I was asked how to convert SAS dates to SQL datetimes. Most of the time all it takes is simple Google search to find the solution for requests like this one, well not this time.
I created a function that do the conversion and wanted to share it with you all :-)
This is how you use the function:
select dbo.ConvertSASDate('12DEC2006:15:15:11.000')
Here is the function:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.ConvertSASDate
(
@SASDate varchar(50)
)
RETURNS datetime
AS
BEGIN
-- convert SAS format to european datetime format (dd mon yyyy hh:mm:ss:mmm)
DECLARE @EuropeDate varchar(50)
SET @EuropeDate = substring(@SASDate, 1, 2) -- dd
+ ' ' -- space
+ substring(@SASDate, 3, 3) -- mon
+ ' ' -- space
+ substring(@SASDate, 6, 4) -- yyyy
+ ' ' -- space
+ substring(@SASDate, 11, 2) -- hh
+ ':' -- :
+ substring(@SASDate, 14, 2)-- mm
+ ':' -- :
+ substring(@SASDate, 17, 2)-- ss
+ ':' -- :
+ substring(@SASDate, 20, 3) -- mmm
-- parse european format
return convert(datetime, @EuropeDate, 113)
END
GO