[ACCEPTED]-best way to convert and validate a date string-tsql
First of all, since you're using SQL Server 22 2005, you should put your code that might fail 21 into BEGIN TRY.....END TRY BEGIN CATCH....END CATCH blocks - try/catch blocks for T-SQL!
Second, for 20 all date manipulation, I would always use ISO-8601 format which 19 will work regardless of what current date 18 format is set in SQL Server.
ISO-8601 format 17 is YYYYMMDD for just dates, or YYYY-MM-DDTHH:MM:SS for date with time 16 - so I'd write your code as:
BEGIN TRY
SET @Source='07152009'
SET @Temp = RIGHT(@Source, 4) + -- YYYY
LEFT(@Source, 2) + -- MM
SUBSTRING(@Source, 3, 2) -- DD
IF ISDATE(@Temp)!=1
BEGIN
RAISERROR('ERROR, invalid date',16,1)
END
SET @Destination = CAST(@Temp AS DATETIME)
END TRY
BEGIN CATCH
-- handle error if something bombs out
END CATCH
Do not rely 15 on any particular date format being set!! Send 14 me your code and I'll try it on a Swiss-German 13 system - I almost guarantee it'll break 12 if you blindly assume "en-US" and 11 thus "mm/dd/yyyy" - it's not the 10 same setting everywhere on this planet.
Unfortunately 9 SQL Server is rather weak handling dates 8 - maybe that might be an extension point 7 where using a CLR assembly inside SQL Server 6 would make sense, to tap into the much richer 5 date handling functions in .NET ??
Marc
PS: seems 4 the ISO-8601 format I knew YYYY-MM-DD doesn't 3 always work in SQL Server - contrary to 2 what Books Online seem to preach. Use YYYYMMDD 1 or YYYY-MM-DDTHH:MM:SS instead.
Thanks, gbn!
You can guarantee date-month-year order 7 using SET DATEFORMAT. This means ISDATE 6 will parse '15-07-2009' as 15th July 2009
Otherwise, your 5 approach is good enough given the external 4 limitations... but you could reorder into 3 ANSI/ISO too.
After marc_s' answer: "SET 2 DATEFORMAT dmy" works for most European 1 settings...
OK:
SET LANGUAGE british
SELECT ISDATE('2009-07-15') --this is ansi says marc_s. It gives "zero"
SELECT ISDATE('2009-07-15T11:22:33') --this really is ANSI and gives true
SET LANGUAGE german
SELECT ISDATE('2009-07-15') --false
SELECT ISDATE('2009-07-15T11:22:33') --true
More Related questions
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.