asked
Converting the given day (1-365) in a year to yyyymmdd
I have day columns such as beginday & endday that use 1-365. I need to be able to convert a number (say 45) to a yyyymmdd result. Anybody know how?
The site works by voting. For all answers below that are helpful, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, indicate that by clicking on the check mark next to that one answer.
Try something like Dateadd(d, beginday-1, year(getdate())+'0101')
Presume you want the current year? @Emooiw00 is close, but the calculation is slightly out try: select Dateadd(d, beginday-1, dateadd(yy, datediff(yy, 0,getdate()),0)) What's this doing? start at the inner `datediff(yy, 0,getdate())` that's getting the number of years since the date 0 (1 Jan 1900), and then adding that value as years to the date 0 , to give you the start of this year 1 Jan 2013, and finally adding the number of days (45 in your example) to that date. Then to get your date into the yyyymmdd format, use `convert()` and the style 112
try this one: > DECLARE @NUMBER INT, > @DT DATE = GETUTCDATE(), > @STARTDATE DATE = '2013-1-1' > SET @NUMBER = 1 --(Any number between 1 to 365) > SELECT CONVERT(VARCHAR(8),DATEADD(DD,@NUMBER-1,@STARTDATE),112)
19 People are following this question.
