issue with Date Format - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

issue with Date Format

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 1
The 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 table

SELECT 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 MVP
http://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 end

from
	( -- 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.000
June 30 2013 12:24:11PM 2013-06-30 12:24:11.000
June 30 2013 24:24:11PM NULL
May 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 MVP
http://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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources