Date question

[djj55]

Hello, SQL2008R2
I could not think of a good way to search for this on the web.

I have a date in the format of ddmmyyyy imported from a CSV file.

Our default is mdy. I would prefer not to change the format (SET DATEFORMAT) but if that is the best way to handle things, okay.

I want to be able to compare the date column (VARCHAR(20)) with "ddmmyyyy" to a DATE data type column.

Should I just use something like

SET DATEFORMAT dmy
SELECT M.* 
FROM Mytable M 
INNER JOIN Mytable2 M2 ON M.IncomingDate = M2.DateColumn;
SET DATEFORMAT mdy
GO

What about inserting it into a table which has a DATE column?

Thank you,

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[fredericofonseca]

My advise would be to convert to a date field on load. Failing that create another field (date type) on the table after the load and convert field accordingly and including validation to see if it is a valid date (isdate + substrings + convert(date,...,112) or a format you feel familiar with, but that you explicitly specify on the convert statement.

And only use the date field on the joins


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[djj55]

Thank you, I used SSIS Import Wizard from SSMS to load it but was under a time constraint and was not thinking clearly. I thought about adding a new DATE column solution when I got home.

Thanks again,


djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!