Need datatime in this format?

Question

i'm using stored procedure in SQL serveri need to prinf of get current datetime(eg 2012-16-05 03:10:00) in the format of 05/16/2012 00:00:00 please help me thanks in advance.......

Senthilkumar · Answer

Hi Santhosh There are two types of functions which tells you how to convert the date time in desired format. 1) Convert 2) Cast The Convert is standard and most commonly used. Convert(conversiondatatype actualdateformat-code) For example CONVERT(VARCHARGETDATE()108) Please refer this table for format code. Style Code Style Format Example 0 or 100 Default. Equivalent to not specifying a style code. mon dd yyyy hh:mmAM Sep 8 2007 9:00PM 1 USA date. mm/dd/yy 09/08/07 2 ANSI date. yy.mm.dd 07.09.08 3 UK / French date. dd/mm/yy 08/09/07 4 German date. dd.mm.yy 08.09.07 5 Italian date. dd-mm-yy 08-09-07 6 Abbreviated month. dd mmm yy 08 Sep 07 7 Abbreviated month. mmm dd yy Sep 08 07 8 or 108 24 hour time. HH:mm:ss 21:00:00 9 or 109 Default formatting with seconds and milliseconds appended. mon dd yyyy hh:mm:ss:fffAM Sep 8 2007 9:00:00:000PM 10 USA date with hyphen separators. mm-dd-yy 09-08-07 11 Japanese date. yy/mm/dd 07/09/08 12 ISO date. yymmdd 070908 13 or 113 European default with seconds and milliseconds. dd mon yyyy HH:mm:ss:fff 08 Sep 2007 21:00:00:000 14 or 114 24 hour time with milliseconds. HH:mm:ss:fff 21:00:00:000 20 or 120 ODBC canonical date and time. yyyy-mm-dd HH:mm:ss 2007-09-08 21:00:00 21 or 121 ODBC canonical date and time with milliseconds. yyyy-mm-dd HH:mm:ss.fff 2007-09-08 21:00:00.000 101 USA date with century. mm/dd/yyyy 09/08/2007 102 ANSI date with century. yyyy.mm.dd 2007/09/08 103 UK / French date with century. dd/mm/yyyy 08/09/2007 104 German date with century. dd.mm.yyyy 08.09.2007 105 Italian date with century. dd-mm-yyyy 08-09-2007 106 Abbreviated month with century. dd mmm yyyy 08 Sep 2007 107 Abbreviated month with century. mmm dd yyyy Sep 08 2007 110 USA date with hyphen separators and century. mm-dd-yyyy 09-08-2007 111 Japanese date with century. yyyy/mm/dd 2007/09/08 112 ISO date with century. yymmdd 20070908 126 ISO8601 for use in XML. yyy-mm-ddThh:mm:ss 2007-09-08T21:00:00

Kunal Vaishya · Answer

Dear you can set format like this On GetDate() you can set your Own date variable Select Convert(varcharGetdate()101) + ' ' + Convert(varcharGetdate()8) Or Declare @dt DateTime Set @dt =Getdate() Select Convert(varchar@dt101) + ' ' + Convert(varchar@dt8)

Jiteendra Sampathirao · Answer

Hi santhosh Go through this link... http://blog.sqlauthority.com/2008/08/14/sql-server-get-date-time-in-any-format-udf-user-defined-functions/ Hope it resolves your problem...