best way to convert and validate a date string

by

First of all, since you’re using SQL Server 2005, you should put your code that might fail into BEGIN TRY.....END TRY BEGIN CATCH....END CATCH blocks – try/catch blocks for T-SQL!

Second, for all date manipulation, I would always use ISO-8601 format which will work regardless of what current date format is set in SQL Server.

ISO-8601 format is YYYYMMDD for just dates, or YYYY-MM-DDTHH:MM:SS for date with time – 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 on any particular date format being set!! Send me your code and I’ll try it on a Swiss-German system – I almost guarantee it’ll break if you blindly assume “en-US” and thus “mm/dd/yyyy” – it’s not the same setting everywhere on this planet.

Unfortunately SQL Server is rather weak handling dates – maybe that might be an extension point where using a CLR assembly inside SQL Server would make sense, to tap into the much richer date handling functions in .NET ??

Marc

PS: seems the ISO-8601 format I knew YYYY-MM-DD doesn’t always work in SQL Server – contrary to what Books Online seem to preach. Use YYYYMMDD or YYYY-MM-DDTHH:MM:SS instead.
Thanks, gbn!

More Related Contents:

  1. T-SQL: Opposite to string concatenation – how to split string into multiple records [duplicate]
  2. Check if table exists in SQL Server
  3. Sql Server string to date conversion
  4. Insert multiple rows WITHOUT repeating the “INSERT INTO …” part of the statement?
  5. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  6. Parameter Sniffing (or Spoofing) in SQL Server
  7. How to get Time from DateTime format in SQL?
  8. SQL Server String or binary data would be truncated
  9. Datatype for storing ip address in SQL Server
  10. How to change identity column values programmatically?
  11. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  12. How much size “Null” value takes in SQL Server
  13. SQL Server ORDER BY date and nulls last
  14. How to remove accents and all chars a..z in sql-server?
  15. Find non-ASCII characters in varchar columns using SQL Server
  16. WHERE clause on SQL Server “Text” data type
  17. SQL Server error on update command – “A severe error occurred on the current command”
  18. Aggregate bitwise-OR in a subquery
  19. SQL server schema and default schema
  20. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  21. FOR XML PATH(”): Escaping “special” characters
  22. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  23. Does query plan optimizer works well with joined/filtered table-valued functions?
  24. What are row, page and table locks? And when they are acquired?
  25. @@ERROR and/or TRY – CATCH
  26. Understanding PIVOT function in T-SQL
  27. Using RegEx in SQL Server
  28. How do I use the CONCAT function in SQL Server 2008 R2?
  29. EXEC sp_executesql with multiple parameters
  30. LPAD in SQL Server 2008

Leave a Comment