October 27, 2005 at 3:42 am
What is the best way to ensure consistent handling of dates in SPs?
I got caught out when a client used Crystal Reports (CR) to run one of my SPs and got different results from me when I ran the same SP in QA. After using Profiler, I discovered that CR passed a SET DATEFORMAT MDY command before executing the SP.
In my SP, one line in the where clause was "PaymentDate > '06/04/2005'".
No wonder CR got different results from QA!
So what's the best way to avoid this:
1. Insist all client programs use a proscribed date format.
2. Put a SET DATEFORMAT DMY in all SPs that use dates.
3. Use CONVERT within the SP to translate a date to a set format.
4. Some other way!
Any thoughts much appreciated.
Regards,
Barry
October 27, 2005 at 4:19 am
There is only one format that is unambigous and language independent - yyyymmdd (20051027) - convert displaystyle 112. If you use any other format you're at risk of either parsing errors or (even worse) unexpected results.
The next best is yyyy-mm-dd, while still unambigous (afaik) it is language dependent.
Some good info on the subject:
'The ultimate guide to the datetime datatypes' http://www.karaszi.com/SQLServer/info_datetime.asp
'How do I delimit/format dates for database entry?' http://www.aspfaq.com/show.asp?id=2023
/Kenneth
October 27, 2005 at 7:15 am
Thank you Kenneth, those links were spot on, particularly the first one!
Barry
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply