Sql server Sort date format

select CONVERT(varchar(12),getdate(),1) --// Result 10/18/17

select CONVERT(varchar(12),getdate(),2) --// Result 17.10.18

select CONVERT(varchar(12),getdate(),3) --// Result 18/10/17

select CONVERT(varchar(12),getdate(),4) --// Result 18.10.17

select CONVERT(varchar(12),getdate(),5) --// Result 18-10-17

select CONVERT(varchar(12),getdate(),6) --// Result 18 Oct 17

select CONVERT(varchar(12),getdate(),7) --// Result Oct 18, 17

select CONVERT(varchar(12),getdate(),8) --// Result 11:16:55

select CONVERT(varchar(12),getdate(),9) --// Result Oct 18 2017

select CONVERT(varchar(12),getdate(),10) --// Result 10-18-17

select CONVERT(varchar(12),getdate(),11) --// Result 17/10/18

select CONVERT(varchar(12),getdate(),12) --// Result 171018

select CONVERT(varchar(12),getdate(),13) --// Result 18 Oct 2017            select CONVERT(varchar(12),getdate(),14) --// Result 11:15:40:210

Ajax FilteredTextBoxExtender on multiline TextBox in asp.net allow Enter Key

//

protected void Page_Load(object sender, EventArgs e)

    {

        FilteredTextBoxExtender12.ValidChars = FilteredTextBoxExtender12.ValidChars + "\r\n";

    }

Covert to Currency or datetime format in sql for 2012+ version

Example 1:

DECLARE @mney money = '125000';

SELECT FORMAT ( @mney, 'C') AS MyMoney;

Result:

$125,000.00

Example 2:

DECLARE @mney money = '125000';

SELECT FORMAT ( @mney,'C', 'ta-IN') AS MyMoney;

Result

ரூ 1,25,000.00

Example 3:

DECLARE @mney money = '125000';

SELECT FORMAT ( @mney,'C', 'zh-TW') AS MyMoney;

Result:

NT$125,000.00

Example 4:

set language british

DECLARE @mney money = '125000';

SELECT FORMAT ( @mney, 'C') AS MyMoney;

Result

£125,000.00

Example 5:

DECLARE @num bigint = 5

SELECT FORMAT ( @num, '00.000%') AS MyMoney;

Result:

500.000%

Example set 1:

SELECT Format(1222.4, '##,##0.00')   -- Returns "1,222.40".

SELECT Format(345.9, '###0.00')   -- Returns "345.90".

SELECT Format(15, '0.00%')   -- Returns "1500.00%".

Example set 2

DECLARE @date datetime =getdate()

SELECT  Format(@date , 'h:m:s')   -- Returns "1:10:47".

SELECT Format(@date , 'hh:mm:ss tt')   --Returns "01:10:47 AM".

SELECT Format(@date , 'dddd, MMM d yyyy')   --Returns "Thursday, Dec 22 2011"

Example Set 3:

SELECT  Format(getdate(), 'M/d/yyyy H:mm tt','en-US') --Returns 12/22/2011 1:14 AM

SELECT  Format(getdate(), 'M/d/yyyy H:mm tt zzz','en-US') --12/22/2011 1:15 AM -05:00

Convert Data in currency format using sql server all version

// Query

create function Currency_Format(@ValToConver float,@CommaInt int)

returns varchar(200)

as

begin

  declare @RepsVal varchar(50)

  declare @p1 varchar(40)

  declare @p2 varchar(10)

  set @RepsVal = replace(convert (varchar(20), convert(money, @ValToConver), 3 ) , ',','')

  set @p1 = left(@RepsVal, charindex('.', @RepsVal)-1)

  set @p2 = substring(@RepsVal, charindex('.', @RepsVal), 10)

  set @RepsVal = right(@p1, 3) + @p2

  set @p1 = left(@p1, len(@p1)-3)

  while (@p1<>'')

  begin

    set @RepsVal = right(@p1, @CommaInt) + ',' + @RepsVal

    if (len(@p1) > @CommaInt)

      set @p1 = left(@p1, len(@p1)-@CommaInt)

    else

      set @p1= ''

  end

  return(@RepsVal)

end

/// Result

SQL statement to get column from table type table

//Sql Query

select c.name,t.name from sys.columns as c  JOIN sys.types AS t ON c.user_type_id=t.user_type_id

WHERE c.object_id IN ( SELECT type_table_object_id FROM sys.table_types WHERE name =  'TableType table name') order by c.column_id

Sql server paging

// SQL server

Declare @PageNo int=5,@pageSize int=5

select top (@pageSize) * from(select ROW_NUMBER() OVER(ORDER BY hhguid ASC) as line, * from tblhh where createdby=94)mm

where line > (@PageNo - 1) * @pageSize

Check Uncheck all using Jqury with Parent and Child case

// Jquery function

function CheckUncheck_AllCheckBoxes(chk, CheckParentID) {

    $('[id*=' + CheckParentID + ']').find("input:checkbox").each(function () {

        if (this != chk) {

            this.checked = chk.checked;

        }

    });

    return false;

}

// Html or Asp.net

<asp:CheckBox runat="server" ID="Chk_allFrequency"  onclick="CheckUncheck_AllCheckBoxes(this,'Parent ID static');"

 CssClass="pull-left" Text="Frequency" Checked="true" />