DateTime Conversion!!!
-
Hi, How to cast the following value into datetime. select CAST('17/01/2006 14:52:48' as DATETIME) --> gives out-of-range datetime value.
You are nearly there try this
select Convert(DATETIME, '17/01/2006 14:52:48')There are options that you can use as well; as described below. example convertimg German date for today without timeselect Convert(DATETIME, CAST(Day(getdate())as char(2)) + '.' + CAST(Month(getdate()) as char(2)) + '.' + CAST(Year(getdate())as char(4)) , 104)Without century (yy) (1) With century (yyyy) Standard Input/Output (3) 0 or 100 (1, 2) Default mon dd yyyy hh:miAM (or PM) 1 101 U.S. mm/dd/yyyy 2 102 ANSI yy.mm.dd 3 103 British/French dd/mm/yy 4 104 German dd.mm.yy 5 105 Italian dd-mm-yy 6 106 (1) - dd mon yy 7 107 (1) - Mon dd, yy 8 108 - hh:mm:ss - 9 or 109 (1, 2) Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM) 10 110 USA mm-dd-yy 11 111 JAPAN yy/mm/dd 12 112 ISO yymmdd 13 or 113 (1, 2) Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h) 14 114 - hh:mi:ss:mmm(24h) 20 or 120 (2) ODBC canonical yyyy-mm-dd hh:mi:ss(24h) 21 or 121 (2) ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h) 126 (4) ISO8601 yyyy-mm-ddThh:mm:ss.mmm (no spaces) 127(6) ISO8601 with time zone Z. yyyy-mm-ddThh:mm:ss.mmmZ (no spaces) 130 (1, 2) Hijri (5) dd mon yyyy hh:mi:ss:mmmAM 131 (2) Hijri (5) dd/mm/yy hh:mi:ss:mmmAM -- modified at 7:05 Tuesday 8th August, 2006
Look where you want to go not where you don't want to crash. Bikers Bible
-
Hi, How to cast the following value into datetime. select CAST('17/01/2006 14:52:48' as DATETIME) --> gives out-of-range datetime value.
For consistent date-time conversion behaviour, always use the ISO format: yyyyMMdd hh:mm:ss. All other formats are subject to the user's current locale setting. Best practice when passing dates from an application into a query is to use parameterised queries with parameter objects. For example, in ADO.NET with SQL Server, use @name placeholders to mark the parameters, and SqlParameter objects to set the values of the parameters.
Stability. What an interesting concept. -- Chris Maunder