Date syntax - 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 syntax

Author

Topic

Trininole
Yak Posting Veteran

83 Posts

Posted - 2012-10-25 : 15:36:26

What is the syntax for converting the date format of the date enlisted as "2012-287" to "yyyy-mm-dd"?

Roger DeFour

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-25 : 15:47:26

What date is represented by "2012-287"? Is it the 287th day of 2012 or 28th day of July, or something else?

Trininole
Yak Posting Veteran

83 Posts

Posted - 2012-10-25 : 15:55:43

Yes it is the 287th day of 2012. How would I convert to the format, "yyyy-mm-dd"?

Roger DeFour

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 16:05:42

use like


SELECT DATEADD(dd,STUFF(datefield,1,CHARINDEX('-',datefield),'')*1-1,DATEADD(yy,LEFT(datefield,CHARINDEX('-',datefield+'-')-1)*1-1900,0)) FROM Table

provided the format is consistent

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Trininole
Yak Posting Veteran

83 Posts

Posted - 2012-10-25 : 16:11:57

Thank you

Roger DeFour

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-11-02 : 05:34:23

Another method

declare @d varchar(10)
set @d='2012-287'

select dateadd(year,dates/1000-1900,dates%1000-1) from
(
select replace(@d,'-','') as dates
) as t

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources