[RESOLVED] SQL Server dates

[Strider]

Hello,

in my web application, i insert use the getDate() function in sql to insert the dates into the database.
They are stored as '2005/07/29 14:16:19'

now i need to select records between 2 dates from the table.

the format the user will select is DD/MM/YYYY

what is the best way to convert this inside my sql statement so that sql server recognises it as yyyy/mm/dd

or do i need to change it in my code?

cheers

[Mark Gambo]

Hello,

in my web application, i insert use the getDate() function in sql to insert the dates into the database.
They are stored as '2005/07/29 14:16:19'

now i need to select records between 2 dates from the table.

the format the user will select is DD/MM/YYYY

what is the best way to convert this inside my sql statement so that sql server recognises it as yyyy/mm/dd

or do i need to change it in my code?

cheers

Take a look at this Link regarding CAST and CONVERT

[vb_dba]

SQL Server should handle this implicit conversion for you. I use the same thing here, getDate(), and it stores it in the same format. When my users search for records based on date, I don't do any type conversions and in fact, force them to use a MM/DD/YYYY format when they enter their dates.

[Strider]

in my code i have my sql statement as
"SELECT * FROM mytable WHERE DateReceived < Date.Today"

where date.today will be 30/08/2005

but if i use this i get the following error

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

because the 2005 is not a day and is in the place where the day should be

[si_the_geek]

30/08/2005 is not a valid date for SQL Server, it should be 08/30/2005 instead (it always assumes American date format).

If you can, convert the date to this format.

[Strider]

god dharm americans!!!!!!!

i decided to go with stored procedure and pass in a date 30/08/2005 to a datetime variable in the stored procedure and this done the conversion for me to the sql standard of 2005/08/30

[Mark Gambo]

god dharm americans!!!!!!!

But we still Love You