Varchar to date

[robert030975]

For some reason I seem to have issues with date conversion any way

A table on that is populated by a service as a varchar column called import_date! I have built a SSIS package transferring one table on a different server to another however I would like to use the datadiff on the import date and stop me having to truncate the receving table but when I do this

SELECT *
FROM i_bossdayends
WHERE (DATEDIFF(DAY,GETDATE(), Cast (import_date as datetime))) = 0


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

(0 row(s) affected)

would i need to use isdate with a subquery??

 

[bborissov]

Yes, you should check if import_date contains only valid dates.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[drtree]

Yep - Boris has got it. You could put together a query like:

SELECT *
FROM i_bossdayends
WHERE  IsDate(import_date) = 0

to get all the records without a valid date.

HTH,

Doc Tree

 

[robert030975]

I thought so! Was trying to be a bit too clever in trying to do it all in one select. I suppose I could trap the data from the base table into somewhere else then run the date diff.

SELECT into #temp
FROM i_bossdayends
WHERE IsDate(import_date) = 0

DELETE from i_bossdayends
WHERE IsDate(import_date) = 0

SELECT *
FROM i_bossdayends
WHERE (DATEDIFF(DAY,GETDATE(), Cast (import_date as datetime))) = 0


Question is if there an easy way to update the incorrect dates rather than moving them??

eg
in this temp we might have

27/06/2008 02:00:11
28/06/2008 02:00:31

I can't see the difference between these and the others personally.

eg the isdate <> 0 is

01/07/2008 02:00:04

 

[bborissov]

Your current SET DATEFORMAT is MDY,
Change it to:
SET DATEFORMAT DMY
Check this:

SET DATEFORMAT DMY
SELECT ISDATE('27/01/2008')
SET DATEFORMAT MDY
SELECT ISDATE('27/01/2008')

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[robert030975]

Hi Boris

Yes I see the difference 1 and 0 on the import_date but can you update the column to DMY therefore allowing me to use getdate on selection.

 

[bborissov]

SET DATEFORMAT DMY

SELECT  into #temp
FROM i_bossdayends
WHERE  IsDate(import_date) = 0

And I am sure you will get smaller result.
You can't format the column, SET DATEFORMAT is for connection (if I remember well). That is why I always prefer to work with the right types. Why DateTime should be stored in char field is a mystery to me


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.