sql server date info help - 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

sql server date info help

Author

Topic

tinamiller1
Yak Posting Veteran

78 Posts

Posted - 2012-02-10 : 17:52:51

I have the following code pulling from a table that is formatted 2009-01-21 00:00:00 and need the output to be 21jan2009

the code I am using is this:

select top 10 [date],
convert(varchar(30), [date],106) as fst_srvc_dt
from mytable

it works sort off and returns 21 jan 2009

I want to have it return with no spaces.

i have tried datepart and datediff and replace functions but they do not work. they actually return wierd stuff like 184367 instead of the date.

tina m miller

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-10 : 17:56:04

Many people on this forum would advise you to do that type of formatting on the client side (front-end) if you have one. Nonetheless, in your scenario, you can remove the spaces using REPLACE:

select top 10 [date],
REPLACE(convert(varchar(30), [date],106),' ','') as fst_srvc_dt
from mytable

tinamiller1
Yak Posting Veteran

78 Posts

Posted - 2012-02-10 : 18:00:37

You are the Queen or King of the world. I would love to change it on the server side but I have read rights only and this is the way the IT dept has this dumb SQL table and actually all the tables on this server set up. Such a pain in the behind. But this works and you are AWESOME!!!!

tina m miller

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-02-13 : 10:57:54

Also make sure to read this post
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-13 : 11:38:35

quote:
Originally posted by tinamiller1

You are the Queen or King of the world. I would love to change it on the server side but I have read rights only and this is the way the IT dept has this dumb SQL table and actually all the tables on this server set up. Such a pain in the behind. But this works and you are AWESOME!!!!

tina m miller

A time will come where you will think: "Thanks It dept for giving me read rights only..."

You really should read what madhi has linked!

No, you're never too old to Yak'n'Roll if you're too young to die.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-13 : 11:41:57

Can you just imagine the havoc?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources