CODEWITHSUNDEEP
sqlsql-server
user3112213
user3112213

Reputation: 3

convert the number of the calendar day to date in SQL

Having 174 is there a way to convert it to show the complete date? example: 174 should be 06/23/2014, it is the 174th day of the year. my data looks like this 2014174. is there a SQL function for this?

Thank you

Upvotes: 0

Views: 128

Answers (4)

Chanicho
Chanicho

Reputation: 354

Here is a pretty dynamic way to do this. It would work for variable length inputs like 201450 or 2014364 etc.

declare @myDate varchar(10)
declare @dayPart int
declare @yearPart int
declare @dDate datetime
set @myDate = '201459'
set @yearPart = left(@myDate,4)
set @dayPart = right(@myDate, len(@myDate)-4)

select @yearPart, @dayPart


set @dDate = '01/01/' + cast(@yearPart as varchar(4))
select @dDate
select dateadd(dd,@dayPart, @dDate)

Output: 03/01/2014 - The 59th day of 2014.

Upvotes: 0

TMNT2014
TMNT2014

Reputation: 2130

DECLARE @DATEINT  INT
SET @DATEINT = 2014174
select DATEADD(YEAR, @DATEINT/1000-1900,DATEADD(day, @DATEINT%1000 - 1, 0))

Upvotes: 2

Azar
Azar

Reputation: 1867

Select dateadd(dd,174,dateadd(yy,year(getdate(),0)))

Upvotes: 0

Will
Will

Reputation: 104

You can use the dateadd function like below, to accomplish what you are looking for.

declare @inputNumber int
set @inputNumber = 174
select dateadd(day,(@inputNumber-1),'1/1/'+cast (year(getdate())as varchar(10)))

Upvotes: 1

Related Questions