August 11, 2009 at 7:49 am
Shark Energy (8/11/2009)
I agree totally with all that, but why so that my servers can not interpret SELECT isdate('2009-06-30') as a valid date?
Cause you use the British English regional settings, for the same reason SELECT isdate('2009-30-06') will return true for you but not me (I'm in the US). As far as the server is concerned you gave it a date in the wrong format, it's expecting day/month and you gave it month/day.
-Luke.
August 11, 2009 at 8:08 am
For the two date formats given the following will work:
-- set to european format.
SET DATEFORMAT dmy
DECLARE @t TABLE
(
CharDate char(10) NOT NULL
)
INSERT INTO @t
SELECT '31/12/2009' UNION ALL
SELECT '2009-06-30'
SELECT ISDATE(REPLACE(Chardate, '-', '')) AS IsDateString
,CASE
WHEN ISDATE(REPLACE(Chardate, '-', '')) = 1
THEN CAST(REPLACE(Chardate, '-', '') AS datetime)
ELSE CAST(NULL AS datetime)
END AS DatetimeDate
FROM @t
If you have other date formats, especially American, you are in trouble.
If both American and European date formats are present you may have other information on where the date was entered, or who entered the date, which may enable you to make a guess.
August 11, 2009 at 9:59 am
Luke L (8/11/2009)
Shark Energy (8/11/2009)
I agree totally with all that, but why so that my servers can not interpret SELECT isdate('2009-06-30') as a valid date?Cause you use the British English regional settings, for the same reason SELECT isdate('2009-30-06') will return true for you but not me (I'm in the US). As far as the server is concerned you gave it a date in the wrong format, it's expecting day/month and you gave it month/day.
-Luke.
So British english is dd/mm/yyyy and yyyy/dd/mm? That kind of mucks things up for me due to the sql standard date format...
Run a getdate() today and it returns '2009-08-11 16:56'
If you run a convert against that it is fine. However if you specifically type '2009-08-11' the engine takes it as 8th of November now because of british english. So if the sql standard is coming in with yyyy-mm-dd then how come it behaves differently with a varchar value just because I am set to British English?
August 11, 2009 at 10:12 am
So British english is dd/mm/yyyy and yyyy/dd/mm?
No, British English is either 'dd/mm/yyyy' or 'dd/mm/yy'.
Also, regardless of how the date is set, 'yyyymmdd' will always convert to a datetime.
So, for your two formats, if you get rid of the hyphens, using REPLACE, the string will always convert.
SELECT CAST(REPLACE([YourDateString], '-', '') AS datetime)
August 11, 2009 at 10:29 am
Shark Energy (8/11/2009)
I agree totally with all that, but why so that my servers can not interpret SELECT isdate('2009-06-30') as a valid date?And surely these should both work if 1 works?
select convert(datetime,'31/12/2009',120) --FAILS
select convert(datetime,'31/12/2009') --WORKS
select convert(datetime,'2009-06-30',120) --WORKS
select convert(datetime,'2009-06-30') --FAILS
The above is true when dateformat is set to dmy. I tested this on one of my development servers.
This, select convert(datetime,'31/12/2009',120), fails because you telling SQL Server to convert the string to a datetime value and that it is in the format yyyy-mm-dd hh:mi:ss(24h). In this case, the time portion is considered to be 00:00:00. Unfortunately, the date is in dd/mm/yyyy format, so it fails.
This, select convert(datetime,'31/12/2009'), works because it is in the dmy format defined above.
Looking at the last two date conversions, it should be obivious why they work or fail based on the explainations above for the first two date conversions.
August 14, 2009 at 7:52 am
Try chucking some dates into this and see if you get the result you are after, you may need to add to your serach patterns for all the different formats of dates you're likely to get, but you get the idea...
declare @sDate varchar(100)
declare @dDate datetime
set @sDate = '01/02/2009'
if PATINDEX('[0-9][0-9][0-9][0-9]-%',@sDate) > 0
set dateformat mdy
else
set dateformat dmy
if isdate(@sDate) = 1
select @dDate = convert(datetime,@sDate)
select @dDate
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply