Date Time conversion - 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 Time conversion

Author

Topic

qfalker
Starting Member

1 Post

Posted - 2011-04-05 : 18:41:19

This might be the most elementary thing in the world for many of you but i need to convert this "datecreated" field which houses values such as this '2010-07-29 12:45:00.000' to both a Month/Year format (first day of month is fine) and a Smalldate (which i believe is YYYY-MM-DD). i havent used SQL since 2001 and got stuck w/ a project w/ my very limited knowledge.

Thanks all!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-05 : 19:01:42

I assume you are trying to convert a column that is one of the datetime formats (datetime, smalldatetime etc.) to a string. http://msdn.microsoft.com/en-us/library/ms187928.aspx lists various conversion formats from datetime to strings. If none of them suit your needs, you will have to build it. For example, to show mm/yyyy, you could do

select left('0'+cast(month(YourColumn) as varchar(2)),2) + '/' + cast(Year(YourColumn)  as varchar(4))

For YYYY-MM-DD, you could do

select left(convert(varchar(32),YourColumn,126),10);

That said, many people on this forum who have a lot of experience and in-depth knowledge of SQL will advise you to do the conversions in your presentation layer, if that indeed is the purpose of doing the conversions.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-04-07 : 11:02:43

The first one can be simplified to

select right(CONVERT(varchar(10),yourcolumn,103),7) from table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources