convert date - 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

SQL Server 2005 Forums

Transact-SQL (2005)

convert date

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-07-01 : 13:23:06

how can i convert a date in this format

5/9/2010 8:20 (month,day,year) to

20100509 without the date

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-01 : 13:36:37

Go to Books Online Indedx and type in

"Cast"

You will CAST (ANSI) and CONVERT (T-SQL) Functions


DECLARE @d datetime; SET @d = '5/9/2010 8:20'
SELECT CONVERT(varchar(10),@d,112)

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

Add yourself!
http://www.frappr.com/sqlteam

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-07-01 : 14:20:24

it works when i run your script but when i do it in my query i get returned

5/9/2010 8

my query is select a.id,CONVERT(varchar(10),a.datetime,112),a.datetime from may a

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-07-01 : 15:16:38

What datatype is the filed "a.datetime"...Doesn't appear to be datetime. If its varchar, I guess you should be doing string formatting to get your desired output.

Or..try this

select a.id,CONVERT(varchar(10),convert(datetime,a.datetime),112),a.datetime from may a

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-01 : 17:43:22

quote:
Originally posted by esthera

how can i convert a date

Yes, it's probably defined as varchar...while doing what vijay said, should work (I didn't test it), you really should consider changing the datatype of your column...unless of course you'll blow up a lot of programs, in which case I would say don't do anything

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

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources