Showing posts with label Sql Server Date Format Error. Show all posts
Showing posts with label Sql Server Date Format Error. 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