SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 issue with Date Format
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

joe8079
Posting Yak Master

USA
127 Posts

Posted - 06/17/2013 :  10:09:26  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 06/17/2013 :  10:41:16  Show Profile  Reply with Quote
SELECT CONVERT(datetime,field,109) FROM table


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/17/2013 :  11:26:20  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 06/17/2013 11:33:49
Go to Top of Page

joe8079
Posting Yak Master

USA
127 Posts

Posted - 06/17/2013 :  12:00:08  Show Profile  Reply with Quote
perfect, thank you visakh16 and Michael. Both solutions work great.
Go to Top of Page

JanakiRam
Starting Member

India
22 Posts

Posted - 06/18/2013 :  00:06:23  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/18/2013 :  00:27:44  Show Profile  Reply with Quote
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/18/2013 :  11:48:47  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000