VarChar to DateTime - 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 2000 Forums

SQL Server Development (2000)

VarChar to DateTime

Author

Topic

echo5whiskey
Starting Member

3 Posts

Posted - 2008-05-13 : 16:14:37

Hello everyone, I hope I picked the right forum. I am working on a query where I need to do some date comparisons, basically return records whose date is between X date and Y date. The problem is that the dates are stored in a varchar field in the format of MMM DD YYYY. Is there a way to convert this to a datetime on the fly so I can do my comparisons? This is SQL 2000. TIA.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-13 : 16:17:06

You can use the CONVERT or CAST functions. But I believe your format should have no issues converting implicitly.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

echo5whiskey
Starting Member

3 Posts

Posted - 2008-05-13 : 17:13:54

Thanks. Can you tell me why this query:

SET DATEFormat DMY
SELECT TOP 10 *
FROM tblCustomFieldValues
WHERE CustomFieldDefinitionKeyID = 11
AND CAST(CustomFieldValue AS DateTime) >= '2008-03-01'
AND CAST(CustomFieldValue AS DateTime) <= '2008-03-30'

Returns this error:

Server: Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

???
Seems I can only use one CAST statement for comparison or something? If I run it alone without the second AND, it is fine. I tried a BETWEEN with the same results.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-13 : 17:25:20

Well you've specified in your SET DATEFORMAT statement that you'll be supplying dates as DMY. 30 is not a valid month.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-05-13 : 18:40:44

Try changing the query to this:

SELECT TOP 10 *
FROM tblCustomFieldValues
WHERE CustomFieldDefinitionKeyID = 11
AND CAST(CustomFieldValue AS DateTime) >= '20080301'
AND CAST(CustomFieldValue AS DateTime) <= '20080330'

CODO ERGO SUM

echo5whiskey
Starting Member

3 Posts

Posted - 2008-05-13 : 20:34:25

quote:
Originally posted by tkizer

Well you've specified in your SET DATEFORMAT statement that you'll be supplying dates as DMY. 30 is not a valid month.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Ummm...yeah. I couldn't see the forest for all the trees I guess. I changed it to YMD, and poof - it works. Thanks! :D

LTack
Posting Yak Master

193 Posts

Posted - 2008-05-13 : 22:41:34

Gotta love the head-desk ritual that follows an "Oh dur, I can't believe I missed that!" realization.

My desk has recently acquired a few dents from said ritual.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources