help needed to convert values to datetime

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)

help needed to convert values to datetime

Author

Topic

manjug
Starting Member

2 Posts

Posted - 2007-02-22 : 13:10:27

Hello,

I have a nvarchar(254) field in SQL server table which contains date values.
I have removed all junk data from the column using
ISDate(ISSUEDATE) = 1
and when I try to retrieve values using

WHERE CAST(ISSUEDATE AS DATETIME) > '12/31/2006'

It gives me values
100103, 111003, 111103 in addition to other proper date values.

When I try
SELECT CAST(100103 as DateTIME) from VIEW_CERT_ALLTest
SELECT CAST(111003 as DateTIME) from VIEW_CERT_ALLTest
SELECT CAST(111103 as DateTIME) from VIEW_CERT_ALLTest

I get
2174-01-27 00:00:00.000
2203-12-02 00:00:00.000
2204-03-11 00:00:00.000

Can anyone help me out here??
Any help is appreciated.

Thanks
Manju

Kristen
Test

22859 Posts

Posted - 2007-02-22 : 13:15:37

Presumably ISSUEDATE was varchar, not integer?

If so your check should be:


SELECT CAST('100103' as DateTIME)

which will give:

2010-01-03 00:00:00.000

Kristen

sshelper
Posting Yak Master

216 Posts

Posted - 2007-02-22 : 13:19:28

Try using the CONVERT function and not the CAST function because if you try this:

SELECT CAST('111003' as DateTIME)

This will give you '2011-10-03' and not '11/10/2003'.

SQL Server Helper
http://www.sql-server-helper.com

manjug
Starting Member

2 Posts

Posted - 2007-02-22 : 13:39:36

Thanks a lot for the help. It works..

Kristen
Test

22859 Posts

Posted - 2007-02-22 : 13:42:21

Store dates in a column using a datetime datatype - it avoids all this hassle!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources