Mon-dd-yyyy conversion 1

[Chumley40]

SQL Server 2012

How can I convert a varchar value into a date field if the data comes across in the following formats:
Mar-01-2016 3:20:00 AM
Mar-01-2016
NULL
'' (this means blank)

I have tried numerous cast/convert combinations but keep getting a conversion error. I have tried left 11 characters, then replaced the = with / with no luck. Please help!
TJ

 

[jbenson001]

The first 2 values should go in with no problem
The NULL should go in with no problem as long as the column is nullable
The "blank" will also go in, but SQL server will default it to a datetime value 1900-1-1... (Depending on if it is a SmallDateTime or DateTime)

 

[madonnac]

slice the string up.

[tt]Case[/tt] will recognize date format #107 (mmm dd, yyyy), which is only slightly different to your format

declare @d varchar(40)
set @d = 'Mar-01-2016'

select cast(left(@d,3)+' '+substring(@d,5,2)+', '+substring(@d,8,4) as datetime2)

You should be able to use things like [tt]coalesce[/tt] and [tt]space[/tt] to make sure the string is padded to a minimum length

 

[fredericofonseca]

select case
when infield = 'null' then cast(null as datetime2)
when rtrim(infield) = '' then ... - what are you going to use here???
else convert(datetime2,replace('Mar-01-2016 3:20:00 AM','-',' '), 109) -- will deal with the valid dates.
end

one thing I do hate on SQL Server is the automatic conversion of some values - casting a space as a date should give an error, not default it to a particular value.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Chumley40]

Thank you for all the potential solutions. I now have it returning in almost all circumstances using a variation of the case statement. I think there must be some bad dateformats because for certain dateranges of data I still get an error.

I would like to do something like isdate(field)=1 but the original data is varchar and includes blanks and nulls (as seen in the original post). Do you know of a way to check to see if data can be converted, and if not cast it as cast(null as datetime2)? I am not 100% certain that is my issue, but it makes sense to me. I don't recognize all of the bad data by visualizing it. I did find one value that had the year as 3023, which I excluded to test and it still errored on date conversion.
TJ

 

[fredericofonseca]

As you are on 2012 you can use the try_convert (

https://msdn.microsoft.com/en-us/library/hh230993.aspx)

try following ones and see what you get

select try_convert(datetime2,replace('Mar-01-2016 3:20:00 AM','-',' '), 109)
select try_convert(datetime2,replace('Feb-31-2016 3:20:00 AM','-',' '), 109)
select try_convert(datetime2,replace('null','-',' '), 109)
select try_convert(datetime2,replace(null,'-',' '), 109)
select try_convert(datetime2,replace('','-',' '), 109)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886