sql server 7.0 – date format | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

sql server 7.0 – date format

I have a Web server running on Windows 2000 server with SQL Server 7.0
My regionnal settings are DD-MM-YYYY and I get this error : [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. However, on another server my regional settings are M/D/YYYY and it works fine. I figured out that my problem was with the Date format so I changed to M/D/YYYY on my other server but i still get the same error message
not sure why you are getting the error, but i have found after many arguements with the DATETIME datatype that using YYYYMMDD seems to work great regardless of settings…there is a great article on this site in the articles section by Frank Kallis about the datetime data type ‘I reject your reality and substitute my own’ – Adam Savage
Just thought- another thing i have had to do in the past is add the following to my query batches to make dd-mm-yyyy work: SET DATEFORMAT dmy ‘I reject your reality and substitute my own’ – Adam Savage
When passing date pass it as yyyymmdd format it will work for all the local settings
Madhivanan Failing to plan is Planning to fail
quote:Originally posted by Madhivanan When passing date pass it as yyyymmdd format it will work for all the local settings
True, but sometimes it is that little bit easier to add the ‘SET DATEFORMAT DMY’ to the SQL than to recode the application…i have TREMENDOUS problems getting our developers to change things like the way they pass dates! ‘I reject your reality and substitute my own’ – Adam Savage
But Setting Date format at SQL Side is not recommended.
You need to reset it to the old format after you finish
Mostly format issues should be done in presentation layer
Madhivanan Failing to plan is Planning to fail
Kindly post the thread in relevant forums, if you have the problem that is affecting in large scale then you must propose the question in ‘Please tell us what you think’ section and then we will add the topic here. (Moving to General DBA section from BRainstorm section) Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>