String to DATETIME with TimeZone

Question

I have an SSIS package which pulls in a flat file from a survey system. One of the fields in the flat file is called start_date. It comes in format "04/29/2015 10:09:41 AM PDT"

Executing...

SELECT Start_Date = CAST(Start_Date AS DATETIME)

Results in...

Conversion failed when converting date and/or time from character string.

PDT is causing the issue as when I remove it manually the query is fine. My question is a bit of scalability question. Sure I can hard code it and remove the PDT. Is there a simple way to remove this without being specific to string replacement or left(x) or hard coding a PDT removal? I am not seeing it. Any help is greatly appreciated.

Answer 1

Time zone will always follow the last space, whatever it would be. So here is a generic way you can chop it off. You can set up a lookup for time zones, if you like, at that point.

DECLARE @dtstr NVARCHAR(50) = '04/29/2015 10:09:41 AM PDT'
DECLARE @tzpos INT = CHARINDEX(' ',REVERSE(@dtstr)) --find last space
DECLARE @dt DATETIME2 = LEFT(@dtstr,LEN(@dtstr)-@tzpos) --truncate at last space

SELECT @dt