Showing posts with label Convert Date Format. Show all posts
Showing posts with label Convert Date Format. Show all posts

Saturday 21 May 2016

Convert Date Format To dd/mm/yy in Sql Server


If you Mssql Server is Using Some Other Date Format For Insert Date in Date Column Then Possibly Default Date format is set to US Date Format ( mm/dd/yy ) and if you want to insert date in dd/mm/yy . In order to insert date in this format you need to set Date Format to French / British ( dd/mm/yy )

So first I recommend you to Use any of Jquery Date Picker and pass the selected date / datetime as below to Insert Query as Below -

insert Table_Name (Date_Column)  values (convert(datetime,'18-06-12 10:34:09 PM',103));

Here second parameter 103 is For French Date Format dd/mm/yyyy .If you want to have dd/mm/yy then use 3 in that parameter

Different Types of Date Format Code as As Below - 

Without century (yy) (1)With century (yyyy)StandardInput/Output (3)
-0 or 100 (1,2)Default for datetime and smalldatetimemon dd yyyy hh:miAM (or PM)
1101U.S.1 = mm/dd/yy

101 = mm/dd/yyyy
2102ANSI2 = yy.mm.dd

102 = yyyy.mm.dd
3103British/French3 = dd/mm/yy

103 = dd/mm/yyyy
4104German4 = dd.mm.yy

104 = dd.mm.yyyy
5105Italian5 = dd-mm-yy

105 = dd-mm-yyyy
6106 (1)-6 = dd mon yy

106 = dd mon yyyy
7107 (1)-7 = Mon dd, yy

107 = Mon dd, yyyy
8108-hh:mi:ss
-9 or 109 (1,2)Default + millisecondsmon dd yyyy hh:mi:ss:mmmAM (or PM)
10110USA10 = mm-dd-yy

110 = mm-dd-yyyy
11111JAPAN11 = yy/mm/dd

111 = yyyy/mm/dd
12112ISO12 = yymmdd

112 = yyyymmdd
-13 or 113(1,2)Europe default + millisecondsdd mon yyyy hh:mi:ss:mmm(24h)
14114-hh:mi:ss:mmm(24h)
-20 or 120 (2)ODBC canonicalyyyy-mm-dd hh:mi:ss(24h)
-21 or 121 (2)ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffsetyyyy-mm-dd hh:mi:ss.mmm(24h)
-126 (4)ISO8601yyyy-mm-ddThh:mi:ss.mmm (no spaces)

Note: When the value for milliseconds (mmm) is 0, the millisecond value is not displayed. For example, the value '2012-11-07T18:26:20.000 is displayed as '2012-11-07T18:26:20'.
-127(6, 7)ISO8601 with time zone Z.yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)

Note: When the value for milliseconds (mmm) is 0, the milliseconds value is not displayed. For example, the value '2012-11-07T18:26:20.000 is displayed as '2012-11-07T18:26:20'.
-130 (1,2)Hijri (5)dd mon yyyy hh:mi:ss:mmmAM

In this style, mon represents a multi-token Hijri unicode representation of the full month's name. This value will not render correctly on a default US installation of SSMS.
-131 (2)Hijri (5)dd/mm/yyyy hh:mi:ss:mmmAM