I have to store climate (temperature) data for a few cities. This would mean that each city will have 365 values (ex: The value for Jan 1, is the average temperature for that city for all Jan 1 for 50 years (1955-2004).

Hence I am not at all concerned with year. How do I store the date in a date column? Do I store Jan 1 as Jan 1 2009 ( a non-leap year)? Or is there any better way out?

  • can you describe some use cases of this data
    – miracle173
    May 25 '12 at 11:37
  • @miracle173 This would be for a tourism related module for my webiste. A User would click on the map, and find some points, A graph will be shown, showing max & min temperature throughout the year. User can also select by month & week, as well as zoom into the graph to show data for any arbitrary subset of the year May 25 '12 at 13:25

One idea:

Use "day of year", but offset

  • March 1st is 1
  • Feb 28th is 365
  • Feb 29th is 366

a different approach

you can have a separate table to store all the dates, just like a Date dimension table and then in your main table have a foreign key pointing to it

CREATE TABLE YourDatesTable (
 DateID int NOT NULL IDENTITY(1, 1),
 [Date] datetime NOT NULL,
 [Year] int NOT NULL, 
 [Month] int NOT NULL,
 [Day] int NOT NULL

Set the Dates Start and End

DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = '01/01/1955'
SET @EndDate = '12/31/2004' 

Generate the Date Records

DECLARE @LoopDate datetime
SET @LoopDate = @StartDate

WHILE @LoopDate <= @EndDate

 SET @LoopDate = DateAdd(d, 1, @LoopDate)

View the Dates and notice that leap years have feb 29 and non leap years have only till feb 28

a non leap year

SELECT * FROM YourDatesTable 
where Month = 2
and Year = 2003

a leap year

SELECT * FROM YourDatesTable 
where Month = 2
and Year = 2004

you can use the DateID as a foreign key in your main table

This way you have day, month and year seperate and you also have the Date in dateformat for any date related functions

for reference the script to generate a date dimension i used this site http://www.sqlbook.com/Data-Warehousing/Date-Dimension-SQL-script-18.aspx

  • Why use DateID and not the ([Month],[Day]) combination? May 25 '12 at 10:22
  • you can do that, but how will you differentiate between leap and non leap year.
    – AmmarR
    May 25 '12 at 10:27
  • If you want to store year, fine. The OP states however that he wants to keep Month and Day, independent of Year. May 25 '12 at 10:29
  • Firstly, I do not have 29Feb as a date. Secondly, I don't have a year in my data; I feel that this might be over-engineering, and it still doesn't tell me what I should store for the year May 25 '12 at 13:23
  • why do you want to store the year if its useless, and if you insist on having the year just store he current year.., this way i agree with @ypercube just take in month and day from the table and or have a table just month,week and day. its standard for any year
    – AmmarR
    May 25 '12 at 16:50

In similar cases I store month and day (well, typically year and month) separately, which later can be manipulated easily. Leap day in such series can be tricky since the average will be computed from much less data than ordinary days.

  • the problem i foresee with this approach, is that I can use any date functions in my query, for example, get me all values before March-25 and so on May 25 '12 at 7:18
  • @DevdattaTengshe - yes, your ordinary date operators won't work, but you can easily do SELECT something FROM weather_data WHERE month * 100 + day < 325 -- for 25 March
    – dezso
    May 25 '12 at 7:22

Given the information you provided, I think you have already determined the best way.

...store Jan 1 as Jan 1 2009 ( a non-leap year)

This method allows you to use date types as dates when doing comparisons or date arithmetic without having to convert them. For most scenarios it would be the simplest solution.

The solution is also flexible in that it could later be enhanced to store new averages every year (rather than replacing the old averages). The year would then be the year in which the average was calculated. Of course that would introduce complexity, but the complexity would be deferred until it is necessary.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.