Populate database column with dates for a specified year

Question

I'm looking to modify information held within a database depending on the year selected by the user on a form.

Essentially if they select 2016 from a drop down list then the column in the database (labelled date) will be filled with dates from 1st Jan to 31st Dec.

I'm sure its a trivial questions but thanks!

What flavour of SQL are you using? MS SQL Server/mySQL/Oracle/Other? — JohnHC, Sep 19 '16 at 09:23
I believe its a Microsoft SQL server (taken from the type information in the properties window) @JohnHC — Loplac92, Sep 19 '16 at 09:25

score 0 · Answer 1 · edited May 23 '17 at 11:51

0

Ok...

First, get a numbers table. This will help.

Second, select from the numbers table:

SELECT DATEADD(dd, numbercolumn-1, CAST(YearPick +'-01-01' as DATETIME)) as datesinyear
FROM numberstable
WHERE DATEPART(yyyy,DATEADD(dd, numbercolumn-1, CAST(YearPick +'-01-01' as DATETIME))) = cast(YearPick as INT)

edited May 23 '17 at 11:51

Community

1
1

answered Sep 19 '16 at 10:45

JohnHC

10,935
1
24
40

score 0 · Answer 2 · answered Sep 19 '16 at 10:58

DECLARE @year VARCHAR(10) = '2016'

;with months (date) AS ( SELECT cast(@year+'-01-01' as date) UNION ALL SELECT DATEADD(month,1,date) from months where DATEADD(month,1,date)<=CAST(getdate() as date) ) select Datename(month,date) [Month] from months

Populate database column with dates for a specified year

2 Answers2