Date format - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date format

Author

Topic

Trininole
Yak Posting Veteran

83 Posts

Posted - 2011-03-30 : 13:24:33

how can i convert this date format (DOB) of "1988-08-30 00:00:00.000" to
"1988-08-30". I have been trying this format in my select statement:

convert(varchar(10),eeo.dob,112) DOB but all i would get back "19880830" instead of "1988-08-30". Can you someone help me on this?

Roger DeFour

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-30 : 13:28:47

You'll need to do a little bit of work, since CONVERT function doesn't have a style that matches your format.

1. You can use style 102 or 111 and then use REPLACE function so that you have the dashes.
2. Or just use the LEFT function to grab the first 10 characters and not bother with CONVERT at all.
3. Similarly, you can use SUBSTRING function.

By the way, formatting should not be done inside T-SQL. It should be done in your presentation layer (the application) and not in the backend.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Trininole
Yak Posting Veteran

83 Posts

Posted - 2011-03-30 : 14:51:56

so how would i convert a date with the format being "19880330" to "1988-03-30"? I still have yet to figure it out.

Roger DeFour

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-30 : 15:00:57

This page (http://msdn.microsoft.com/en-us/library/ms187928.aspx) lists all the conversion styles that are available in SQL. There is none that matches the exact format. But, 126 would, if you cut off everything after the 10th character. So you could do it like this:

select convert(varchar(10),cast('19880330' as datetime),126);

That varchar(10) which is the length of the output string ensures that you get only the first 10 characters.

Another alternative would be to just "stuff" two dashes into the string:

select stuff(stuff('19880330',7,0,'-'),5,0,'-');

Tara listed some of the other alternatives.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-30 : 15:01:24

STUFF!

Select stuff(stuff(convert(varchar(10),getdate(),112),7,0,'-'),5,0,'-')

Corey

I Has Returned!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-30 : 15:11:16

select convert(varchar(10),getdate(),121) ?

Jim

Everyday I learn something that somebody else already knew

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-30 : 15:32:09

quote:
Originally posted by Trininole

so how would i convert a date with the format being "19880330" to "1988-03-30"? I still have yet to figure it out.

Roger DeFour

I'm confused why you are asking this as original post said your format is like this: "1988-08-30 00:00:00.000". You said that 19880330 is what you get when using style 112 and is not what you want. So why are you asking how to convert from 19880330 to 1988-03-30?

All you need to do is convert from 1988-08-30 00:00:00.000 to 1988-03-30, and that's what my post addresses.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-30 : 15:33:27

I personally don't like the truncation aspect of using 121 or similar styles and just specifying varchar(10). I prefer to make it clear via the LEFT function as that's easier for future people to read the code and immediately understand what it's doing.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources