| Author |
Topic |
|
Corobori
Posting Yak Master
105 Posts |
 Posted - 2004-03-03 : 14:45:57
|
| I have a view with this statement: CASE WHEN CardDate IS NULL THEN 0 WHEN CardDate < getdate() THEN 1 ELSE 0 END AS iDateLower This works fine on my machine but it seems that on my user machine it doesn't. I am thinking this is due to a different date format. How can I make this "date format" proof ?jean-lucwww.corobori.com |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-03 : 15:45:57
|
| There is only one datetime format in SQL Server. Are you both pointing to the same database server? If not, what versions are each of them (SELECT @@VERSION)?Tara |
 |
|
|
Corobori
Posting Yak Master
105 Posts |
Posted - 2004-03-03 : 15:53:00
|
quote:
Originally posted by tdugganAre you both pointing to the same database server? If not, what versions are each of them (SELECT @@VERSION)?
No, I am developing an application in vb.net using MSDE. My PC has got mm/dd/yy settings and my customer's pc has dd/mm/yyyy. When executing SQL Statement in my code I am formatting the date but I don't know how to do this in my viewjean-lucwww.corobori.com |
|
|
|
ChrisFretwell
Starting Member
43 Posts |
Posted - 2004-03-03 : 15:55:07
|
| When you say doesnt work, what do you mean? Do you mean it returns different results or that it fails?You can also write you case to have just one when if you wantedcase when isnull(cardate,getdate() < getdate() then 1 else 0 end The date format (regional settings) could only have an impact if carddate is not datetime field or if you translate elsewhere it from a string a value. If you translate it from a string, then a user entering 01/02/2004 for Feb 1 would produce different results from someone who enters 02/01/2004 for the same date.Do you include cardate in the view results? If so, what does it display? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-03 : 15:58:40
|
| Please show us the query plus sample data and what the result set is on both machines. You should not be formatting the date as SQL Server stores it in one format only. You can read different formats using CONVERT with a style.Tara |
|
|
|
Corobori
Posting Yak Master
105 Posts |
Posted - 2004-03-03 : 17:44:51
|
quote:
Originally posted by ChrisFretwellWhen you say doesnt work, what do you mean? Do you mean it returns different results or that it fails?
My problem is with the result. On this print screen it says that a card with a date of 12/may/2005 is not valid (Echue=true) which means that considering the statement shown above CardDate was lower than the current date, which is obviously wrong  quote:
Originally posted by tdugganPlease show us the query plus sample data and what the result set is on both machines. You should not be formatting the date as SQL Server stores it in one format only. You can read different formats using CONVERT with a style.
1) I wish I could have access to the sample data on my client's PC but I can't. I know that on my machine it works fine. 2) I am been trying with the convert but I would like to be sure that it will work no matter what the data format is. I don't feel confortable with it.jean-lucwww.corobori.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-03 : 18:02:58
|
| CONVERT will work for different date formats. But you have to know which date format it is and use the appropriate style.Tara |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-03-04 : 14:24:08
|
| I didn't see an answer to whether CardDate is a varchar datatype or a datetime datatype, but based on your description, it sounds like CardDate is a varchar. In that case, explicitly convert it to datetime. If the CardDate varchar of '1/2/2004' means February 1, 2004, then do something likeCASE WHEN CardDate IS NULL THEN 0 WHEN convert(datetime, CardDate, 103) < getdate() THEN 1 ELSE 0 END AS iDateLower --------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
|
|
|
|
CASE and date