having trouble with converting from datetime to varchar 2

[BobRodes]

I'm trying to convert a date format to a string, so I can strip off the characters I want. I'm having problems. I have these three statements:

select top 1 effdate from temp0
select top 1 convert(datetime, effdate, 112) from temp0
select top 1 cast(convert(datetime, effdate, 112) as varchar(50)) from temp0

with these results:

Jan  1 1991 12:00AM
1991-01-01 00:00:00.000
Jan  1 1991 12:00AM

effdate is varchar(50). Why doesn't the cast (I've tried using convert as well with the same result) cast the result of the inner convert to text? Also, what's the right way to do this? I want the result 19910101 as text, and was planning to cast the second format as text and use substring and replace to get it.

 

[markros]

You need to use double convert, e.g.
select top 1 convert(varchar(10), convert(datetime, effDate,112),112) as [Date Converted]

Although using 112 in the first conversion seems incorrect to me.

PluralSight Learning Library

 

[gmmastros]

effdate is varchar(50)

I want the result 19910101 as text

Soo... you have a string and you want to return a string, right? I suppose there is something about the format for the dates that you don't like.


select top 1 effdate from temp0
[blue]Jan 1 1991 12:00AM[/blue]

So, effdate is a string containing that text.


select top 1 convert(datetime, effdate, 112) from temp0
[blue]1991-01-01 00:00:00.000[/blue]

Datetime is actually stored with 4 bytes, actually a pair of integers. Dates cannot actually be displayed. What you are seeing here is SQL Server Management Studio's default date to string conversion display format.


select top 1 cast(convert(datetime, effdate, 112) as varchar(50)) from temp0
[blue]Jan 1 1991 12:00AM[/blue]

In this case, you are converting to a DateTime and then casting to string. What you are seeing now is SQL Server's (the database engine, not the management studio's) default format for converting a datetime to a string.

To get your desired results, you should convert to datetime, and then convert to string but with a style argument, like this:

select top 1 Convert(VarChar(8), convert(datetime, effdate), 112) from temp0

You need to be careful though. When people store dates as strings in the database, it almost always happens that they end up getting strings that are not able to be converted to a date.

Ex:

TableData
---------
Jan 1 1991 12:00AM
Dec 25 2001 05:25PM
Yesterday

If this is the case, you may want to protect yourself by doing an IsDate check. Like this:

select top 1 
       Convert(VarChar(8), Case When isdate(effdate) = 1
                                Then convert(datetime, effdate)
                                Else NULL
                                END, 112) 
from   temp0

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BobRodes]

Thanks markros. I did try using the double convert, but I missed putting in the 112 format in the outer parens as you have it. I see now that that caused it to format the text to the default date format. I also see as you mention that it's not necessary to put the 112 on the inside conversion.

So, it looks like the inside conversion takes the text back to a datetime, which allows the outer conversion to convert to text using format 112.

The light dawns...thanks again!

 

[BobRodes]

Thanks for all the additional information, too, george. I agree with your caveat, and fortunately we're getting away from this format. Has to do with downloading government stuff, of course.

 

[gmmastros]

It is relatively rare for the style argument to be used during a convert to DateTime, but there are cases when you want to use it.

For example, if you are dealing with British and/or US dates, you can use the style argument to force the interpretation of the date.

For example, what month is this?:

2/3/2011

Some would say February, and some would say March. But, what would SQL Server say?

The truth is, it depends on the default language of the login you are using. More on that here:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/setting-a-standard-dateformat-for-sql-se

You can force the interpretation during the convert to DateTime process by using the style argument of the convert function, like this:

Select Convert(DateTime, '2/3/2011', [!]101[/!])
Select Convert(DateTime, '2/3/2011', [!]103[/!])

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom