Date Problem

[Syed Shahid Hussain]

hei all, I'm now working in Norwegian environment where date formate is like this DD.MM.YYYY so when i write query in SQL 2000 like this SELECT joinDate FROM myTable WHERE (joinDate>= '01.01.2009') AND (joinDate<= '01.01.2010') it gives error And I cant convert date format. Any ideat will be most welcome. Thanks

Syed Shahid Hussain

[J4amieC]

The answer is to always use ISO 8601[^] date format when dealing with databases. So your query becomes:

SELECT joinDate
FROM myTable
WHERE (joinDate>= '2009-01-01') AND (joinDate<= '2010-01-01')

[Syed Shahid Hussain]

Thanks. But the problem is that from application how can i change the date format, should i wirite a function that get dd.mm.yyyy and then return into yyyy-mm-dd Any suggestion from u. Thanks

Syed Shahid Hussain

[David Skelly]

If joinDate is a DateTime type, then this should work:

joinDate >= CONVERT(DATETIME, '01.01.2009', 104)

This is fully documented in the SQL Server documentation, which is all available on line.

[Syed Shahid Hussain]

oh really thanks David its great :rose:

Syed Shahid Hussain

[PIEBALDconsult]

Parse the strings to DateTime and use a parameterized query.

[Syed Shahid Hussain]

thanks :rose:

Syed Shahid Hussain