views:

369

answers:

2

I have a single char(8) variable formatted as ddmmyyyy in a stored procedure (quality and validity of this value is unknown and beyond my control). What is the best most efficient way to move the value into a datetime variable, and throw an error if it is not valid datetime.

DECLARE @Source       char(8)
DECLARE @Destination  datetime

SET @Source='07152009'

--your solution here


SELECT @Destination

here is the best way I could think of:

DECLARE @Source             char(8)
DECLARE @Temp               varchar(10)
DECLARE @Destination        datetime

set @Source='07152009'
SET @Temp=LEFT(@Source,2)+'/'+SUBSTRING(@Source,3,2)+'/'+RIGHT(@Source,4)

IF ISDATE(@Temp)!=1
BEGIN
    RAISERROR('ERROR, invalid date',16,1)
END
SET @Destination=@Temp

SELECT @Source AS Source, @Temp AS  Temp, @Destination AS Destination

EDIT here's what I'm going to go with...

DECLARE @Source             char(8)
DECLARE @Destination        datetime

set @Source='07152009'
BEGIN TRY
    SET @Destination=CONVERT(datetime,RIGHT(@Source,4)        -- YYYY
                                      +LEFT(@Source,2)        -- MM
                                      +SUBSTRING(@Source,3,2) -- DD
                             )
END TRY
BEGIN CATCH
    PRINT 'ERROR!!!' --I'll add a little more logic here and abort processing
END CATCH

SELECT @Source AS Source, @Destination AS Destination
+4  A: 

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!

marc_s
This is ANSI not ISO. ISO would be '1998-02-23T14:23:05'
gbn
...without "T" and time, yyyy-mm-dd defaults to ANSI
gbn
My system is in a controlled environment, there will be no Swiss-German date conversion issues. I do like the try-catch idea. I'll probabily remove the IF ISDATE() and @Temp, and then just catch the conversion error if any when doing SET @Destination=LEFT(@Source,2)+'/'+SUBSTRING(@Source,3,2)+'/'+RIGHT(@Source,4)
KM
your version coded above, does not work on my system, the RAISERROR() is hit for valid dates. @Temp='20091507', and ISDATE('20091507')!=1
KM
oops sorry - mixed up the DAY and MONTH - fixed my block of code - works for me now
marc_s
+1 for the ISO-8601. It's the most sane way to deal with dates and is portable across multiple contexts.
Jacob
+3  A: 

You can guarantee date-month-year order using SET DATEFORMAT. This means ISDATE will parse '15-07-2009' as 15th July 2009

Otherwise, your approach is good enough given the external limitations... but you could reorder into ANSI/ISO too.

After marc_s' answer: "SET DATEFORMAT dmy" works for most European 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
gbn
agreed - for most - but why not use the ISO-8601 format which works for ALL settings of SET DATEFORMAT?? Just play it safe.....
marc_s
ISO-8601 does work for all settings but it's not ISO: it's ANSI you have specified http://stackoverflow.com/questions/1135746/sql-server-convert-string-to-datetime
gbn
MS Books Online seems to disagree: http://msdn.microsoft.com/en-us/library/ms190977%28SQL.90%29.aspx
marc_s
Indeed. But yyyy-mm-dd is not ISO. yyyy-mm-ddThh:mm:ss[.mmm] is ISO, as your link says. Without time, it default to ANSI.
gbn
Interesting - the SQL Books Online and the real implementation in SQL Server don't match..... hmm.... first time I see that!
marc_s
Yep, it's a real gotcha. To help readability and reduce this ambiguity, I'd use SET DATEFORMAT...
gbn
Strangely enough, "YYYYMMDD" will work again even with SET LANGUAGE 'British' set.......
marc_s
Yes: In Tibor's article it says unseparated is language and dateformat neutral. See Tony Rogerson too http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/01/05/date-format-inconsistency-yyyy-mm-dd-or-yyyymmdd-when-2007-04-10-is-4th-october.aspx
gbn