varchar to smalldatetime 1

[bmpsu]

I need to convert a date stored in a varchar column to a smalldatetime format when querying the database. From sql server 2005.

Here are the specifics:
dob (varchar(30), not null) - to smalldatetime

EX:
dob
Oct 3 1984 12:00AM


Suggestions? Thanks.

 

[gmmastros]

One problem you may have is that a varchar column can contain ANY string, so you'll need to be careful about only converting those values that CAN be converted. For more on this, take a look at this Frequently Asked Question: faq183-6419

Generally, you query would be...

Convert(SmallDateTime, DOB) As DateOfBirth

Eventhough the format is 'wordy' with the month abbreviation, it will still convert properly for you.

Ex:
[tt][blue]Select Convert(SmallDateTime, 'Oct 3 1984 12:00AM')[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[monksnake]

All you have to do is the following:

select convert(smalldatetime, dob)

this puts the varchar data type into a smalldatetime data type

 

[bmpsu]

That was easy enough. Thanks guys. My initialy thoughts were that this would be more of a challenge. I didn't even try this.

george, All values for this column in the database follow the same format. I would have never done it that way, but that is what I am dealing with on this task.

Thanks.

 

[gmmastros]

My words of caution come from experience. Since you are dealing with a date of birth, it's reasonable that not all columns have a valid date of birth. Also, since a small date time value has a lower range of Jan 1, 1900, it's possible that someone in your database was born before that date (unlikely but possible). You should also note that any record where the value is an empty string, the DOB will return as Jan 1, 1900 (which could be problematic for you).

Ex:

Select Convert(SmallDateTime, '')

Returns:
[tt][blue]
------------------------------------------------------
1900-01-01 00:00:00

(1 row(s) affected)[/blue][/tt]

If you want, you can convert this to NULL in your return by using...

Select NullIf(Convert(SmallDateTime, DOB), '19000101') As DateOfBirth

With this syntax, if you don't know the date of birth for a record in your table, it will return as null. Of course you will have to handle the nulls in your front end app, but that's simple enough.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bmpsu]

Thanks for the advice. I looked at the FAQ you referenced to. Would using the NullIf function be the same as the user defined function in the FAQ, then using the case statement if you want nulls for values that can not be converted.

Your syntax would be much easier if they both produce the same results.

I would like to return null for ['' values] or varchars that can not be converted dates.

Thanks.

 

[gmmastros]

Nope. My suggestion would be to use the function from the faqs. Simply copy/paste it to Query Analyzer, change the 'Alter Function' to 'Create Function' and run it.

Then, your query would be something like this...

[tt][blue]
Select Field1,
Field2,
Case When dbo.fnIsSmallDateTime(DOB) = 1
Then Convert(SmallDateTime, DOB)
Else NULL
End As DOB,
Field3
From Table
[/blue][/tt]

The function properly handles empty strings, so you won't need to worry about that.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bmpsu]

Thanks for the help. I used the function and its looking good.