Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
joe8079
Posting Yak Master
127 Posts |
Posted - 2013-06-17 : 10:09:26
|
Hi, I have a field in my table that has a date format of May 28 2013 4:24PM and its in Char(26) datatype. I need to convert this to datetime datatype so that it looks like 2013-05-28 16:24:11.000. I've tried. I have tried all kinds of conversions but everything fails and I get this error: Msg 242, Level 16, State 3, Line 1The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.none of these work, here are a few examples below SELECT field, CONVERT( DATETIME, field,101)d FROM tableSELECT field, CONVERT( DATETIME, field,103)d FROM table |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-17 : 10:41:16
|
[code]SELECT CONVERT(datetime,field,109) FROM table[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2013-06-17 : 11:26:20
|
This works OK for me. You probably need a check for bad dates.select a.*, DT = case when isdate(a.DT_In) = 1 then convert(datetime,a.DT_in) else null endfrom ( -- Test Data Select DT_In = 'May 28 2013 4:24:11PM' union all Select DT_In = 'June 30 2013 12:24:11PM' union all Select DT_In = 'June 30 2013 24:24:11PM' union all -- bad date Select DT_In = 'May 32 2013 4:24:11PM' -- bad date ) a Results:DT_In DT----------------------- -----------------------May 28 2013 4:24:11PM 2013-05-28 16:24:11.000June 30 2013 12:24:11PM 2013-06-30 12:24:11.000June 30 2013 24:24:11PM NULLMay 32 2013 4:24:11PM NULL CODO ERGO SUM |
|
|
joe8079
Posting Yak Master
127 Posts |
Posted - 2013-06-17 : 12:00:08
|
perfect, thank you visakh16 and Michael. Both solutions work great. |
|
|
JanakiRam
Starting Member
22 Posts |
Posted - 2013-06-18 : 00:06:23
|
SELECT CONVERT(DATETIME,CAST('May 28 2013 4:24PM' AS VARCHAR(56)),103)SELECT CONVERT(DATETIME,CAST('May 28 2013 4:24PM' AS VARCHAR(56)),101)Try this once....JanakiRam |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-18 : 00:27:44
|
quote: Originally posted by JanakiRam SELECT CONVERT(DATETIME,CAST('May 28 2013 4:24PM' AS VARCHAR(56)),103)SELECT CONVERT(DATETIME,CAST('May 28 2013 4:24PM' AS VARCHAR(56)),101)Try this once....JanakiRam
whats the purpose of first CAST? the CONVERT itself should be enough with style value of 109------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2013-06-18 : 11:48:47
|
quote: Originally posted by visakh16
quote: Originally posted by JanakiRam SELECT CONVERT(DATETIME,CAST('May 28 2013 4:24PM' AS VARCHAR(56)),103)SELECT CONVERT(DATETIME,CAST('May 28 2013 4:24PM' AS VARCHAR(56)),101)Try this once....JanakiRam
whats the purpose of first CAST? the CONVERT itself should be enough with style value of 109
Especially since it is casting from Char(26) to VARCHAR(56) CODO ERGO SUM |
|
|
|
|
|
|
|