| Author |
Topic |
|
pmccann1
Posting Yak Master
107 Posts |
 Posted - 2008-07-28 : 05:34:58
|
| Is there a way to check at the beginning of a stored procedure the format of the date i.e make sure that it is not american date format and if it is do not run the SP. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-07-28 : 05:40:54
|
quote:
Originally posted by pmccann1
Is there a way to check at the beginning of a stored procedure the format of the date i.e make sure that it is not american date format and if it is do not run the SP.
What do you want to do if it is of different format?You should always use DAETTIME datatype and express dates in YYYYMMDD HH:MM:SS format to work for all date settingsMadhivananFailing to plan is Planning to fail |
 |
|
|
pmccann1
Posting Yak Master
107 Posts |
Posted - 2008-07-28 : 05:47:08
|
| If the date is not in english format i.e dd/mm/yyyy then i want the stored procedure to just stop |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-07-28 : 05:48:24
|
How do you know 01/02/2008 is DD/MM/YYYY or MM/DD/YYYY ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-07-28 : 05:48:57
|
| So how would you know what format 12/11/2008 is?CODO ERGO SUM |
|
|
|
pmccann1
Posting Yak Master
107 Posts |
Posted - 2008-07-28 : 05:59:46
|
| Thats what i am wanting to know is there a way of making sure the date format is correct on the server for instance getdate() s in the right format. we ahve had a problem where it was not and i would just like to put something in place |
|
|
|
VGuyz
Posting Yak Master
121 Posts |
Posted - 2008-07-28 : 06:02:56
|
| Check with isdate() function,orconvert u'r date into american standard.SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY] |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-07-28 : 06:04:09
|
quote:
Originally posted by pmccann1
Thats what i am wanting to know is there a way of making sure the date format is correct on the server for instance getdate() s in the right format. we ahve had a problem where it was not and i would just like to put something in place
What was your problem?Can you post the problamatic code?MadhivananFailing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
pmccann1
Posting Yak Master
107 Posts |
Posted - 2008-07-28 : 06:11:53
|
| basically the code waswhere end_date <= getdate()this was run on the 01/02/2008 but some came through because it read the date as 02/01/2008 |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-07-28 : 06:20:51
|
quote:
Originally posted by pmccann1
basically the code waswhere end_date <= getdate()this was run on the 01/02/2008 but some came through because it read the date as 02/01/2008
What is the datatype of the column?MadhivananFailing to plan is Planning to fail |
|
|
|
pmccann1
Posting Yak Master
107 Posts |
Posted - 2008-07-28 : 06:41:07
|
| datetime but it was the getdate that was wrong because the server was in american |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-07-28 : 06:47:36
|
quote:
Originally posted by pmccann1
datetime but it was the getdate that was wrong because the server was in american
It is of datetime column, you dont need to worryYour query would produce correct resultMadhivananFailing to plan is Planning to fail |
|
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2008-07-28 : 07:27:14
|
| Madhivanan is correct - if the column is a datetime column, the query would work fine. If you do have a problem, this isn't it.Ryan Randall Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-28 : 07:49:09
|
| try specifying explicit format of date usingSET DATEFORMAT dmy |
|
|
|
|
stop procedure if date format wrong