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