CODEWITHSUNDEEP
sql-server
MaxPayne
MaxPayne

Reputation: 936

How to add current year's all dates to SQL server?

Is it possible to add all the dates between 1st Jan-31-Dec in MS Sql server using query?

If someone has done it before please guide me to right track.

Thanks

Upvotes: 0

Views: 67

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44326

This should do the trick:

DECLARE @year int = 2015

;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)-1 FROM N,N a,N b,N c,N d,N e,N f)
SELECT top (datediff(d, cast(@year as char(4)), cast(@year + 1 as char(4)))) 
    CAST(DATEADD(d, N, cast(@year as char(4))) as date)
FROM tally

Result:

2015-01-01
2015-01-02
..
..
2015-12-31

Upvotes: 1

Simone
Simone

Reputation: 1924

DECLARE @dt  Date
SET @dt = '2015-01-01'
WHILE @dt < '2016-01-01'
BEGIN
    SELECT @dt
    --INSERT .....
    SET @dt = DATEADD(DAY, 1, @dt)
END

Of course it depends on your table structure

Upvotes: 2

mohan111
mohan111

Reputation: 8865

using loop we can achieve this 

Declare @date table(d datetime)
Declare @d datetime, @d1 datetime, @d2 datetime
Declare  @inc INT

set @d1='20150101'
set @d2='20151231'


Set @inc = DATEDIFF(D, @d1, @d2)

Set @d = @d1


While @d<=@d2
Begin
Insert into @date values (@d)
set @d=@d+1

End
Select d as DateCol from @date

Upvotes: 1

Related Questions