Convert separated varchar date to datetime 1

[jfcox]

I have a table that has the date separated into 3 columns, month, day and year. All three columns are varchar in the database. I need to recombine those parts into a full date and convert the result into a datetime. I have tried casting and coverting combinations to no avail. One of the problems is that there are blank rows in the database and any attemept to convert those rows gives an error that says the date time is out of range. This is probably simple but it's driving me crazy. Does anybody know an easy way to do the conversion and keep it from crashing on the blank rows?

thanks

 

[bigmelon]

Put your cast or convert inside a case when statement to avoid the crash

 

[bigmelon]

CASE WHEN ... IS NOT NULL THEN (Your Code Here) ELSE NULL

 

[twifosp]

Hrm, are they null or blank? You'll need to give them some type of value. I'm choosing 1 in this example, which might not work very well for your year, but you get the idea.

Either way you could do:

select	cast((day + '/' + month + '/' + year) as datetime)
from
( select 'day' = case when day is null then '1' else day end,
'month' = case when month is null then '1' else month end,
'year' = case when year is null then '1' else year end
from table) a

If your row is blank rather than null, replace is null with =''

 

[SQLBill]

I would suggest using the ISNULL to add a default value.

For example:

SELECT CAST(
       ((CAST(ISNULL(MyYear, '2004')) AS VARCHAR(4)) + '-' +
       (CAST(ISNULL(MyMonth, '01')) AS VARCHAR(2)) + '-' +
       (CAST(ISNULL(MyDay, '01')) AS VARCHAR(2))
       ) AS DATETIME) AS 'MyDate'

It's UGLY. But I think I have all the parenthesis, etc. correct.

-SQLBill

 

[ESquared]

Convert(datetime,yr+'-'+mo+'-'+ dy)

should work fine, returning Null when yr or mo or dy are null! I wonder if the error you are experiencing is because your months and days don't have leading zeroes and you aren't putting a separator in between things. (If they do have leading zeroes you can leave the separator out.)

If you need a default date, you can do it simply this way:

SELECT Convert(datetime,IsNull(MyYear + MyMonth + MyDate,0))

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[jfcox]

Thanks for all your suggestions as I tried them all. It still doesn't work and I get an error 'Error converting data to datetime' I think maybe Esquared's suggestion that I'm missing leading zeros might be valid. To be a little clearer than my first post, I have a date broken into three columns, month (v2), day (v2) and Year (v4). When I try to reassemble these I get a data conversion error. I have tried various combinations as other posters have suggested. One of the problems seems to be that there are both NULL and 'blank' rows in the table. So, I have tried trapping the null with Case or coalesce, ect. and that works on the null side, however, I still get the data conversion error.

Thanks for everyones' help thus far!

 

[ESquared]

The examples I gave work fine with Nulls, I tested them! So I suspect the remaining problem is with empty strings. Can you do a simple update statement to change them to Nulls? To me, Null is a better indicator of "no value" than an empty string in this situation.

UPDATE myTable SET MyYear = Null WHERE MyYear = ''

Then put a constraint on the table to prevent empty strings. Or, change your UI/procedures so empty strings aren't inserted. Or, add a trigger that converts empty strings to Null on insert or update. Or I suppose you could tolerate empty strings.

CASE
   WHEN yr = '' OR mo = '' or dy = '' THEN Null
   ELSE Convert(datetime,yr+'-'+mo+'-'+dy)
END

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[SQLBill]

As ESquared says, change the 'blanks' to NULLS. In SQL Server a blank (one or more spaces) is not equal to a NULL value.

In a concatenation of string values, NULLs and spaces do not act the same. To show by example here are two rows with the exact same information - except that C2 has a NULL in one row and a space in the other. Let's say all values are VARCHAR

C1 C2 C3
01 <space> 2004
01 <NULL> 2004

If we concatenate those (SELECT C1 + C2 + C3) we will get:

01 2004
012004

The first result has a space in it since there is a space for C2 in the stored row. The second result runs together since there ISN'T a space or any other value for C2 in that stored row.

-SQLBill

 

[ESquared]

SQLBill, concatenating Nulls yields Null unless you do SET CONCAT_NULL_YIELDS_NULL OFF. That is why my simplest example works.

[tt]Convert(datetime,yr+'-'+mo+'-'+ dy)[/tt]

Look:

[tt]SELECT '2004' + Null + '01'[/tt]

yields Null, not '200401'.

As for

[tt]SELECT '2004' + '' + '01'[/tt]

it DOES yield '200401'


-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[SQLBill]

AAAAHHHH, I must have been asleep.

-SQLBill