Posted by: R Manimaran | July 4, 2008

Getting Missing Date Values in SQL Server

Getting Missing Dates With Values a Zero

In some table we will insert values for the daily dates. In some cases will omit the values if a date should not have any value. Afterwards while taking the report we want all the dates and values as 0 for the missing month. For here we can use the temp table and get our need.


–Off the Intermediate information message

Set nocount on

–Declare a Table which contains the values for Dates

Declare @Table table(Dayid DateTime,Valuefld int)

–Insert values into the table

insert into @Table values(‘1-Jan-2008’,30)

insert into @Table values(‘3-Jan-2008’,53)

insert into @Table values(‘5-Jan-2008’,70)

insert into @Table values(‘8-Jan-2008’,81)

insert into @Table values(’12-Jan-2008′,22)

insert into @Table values(’16-Jan-2008′,30)

insert into @Table values(’19-Jan-2008′,34)

insert into @Table values(’22-Jan-2008′,64)

insert into @Table values(’27-Jan-2008′,66)

insert into @Table values(’31-Jan-2008′,52)

–Here comes the Query for getting the values

–Declare another table with colum id as dateTime data type

Declare @table_days table(id datetime)

Declare @day_index datetime

select @day_index=‘1-JAN-2008’

Declare @index int

Select @index=1

while @index <365


Insert into @table_days values(@day_index)

SELECT @day_index=DATEADD(day, 1, @day_index)

Select @index=@index+1


–As we are using DateAdd it will automatically count the dates for leap year and months

–Query for the Jan month only

Select CONVERT(VARCHAR(10),,101),isnull(Valuefld,0) From @Table A Right outer join

@table_days B on where <‘1-Feb-2008’


Missing Date with 0 as Value

Missing Date with 0 as Value



  1. hi

    i have a problem.
    i have a table named IOData.Fields are
    name ,id,date ,gatename.
    i have to find absent names,dates excluding weekends for all employees for a month group by name. anyone pls help me


  2. I think your table is not a normalized one. Is there any separate table for Employees.. If yes, then IOData table should not contain name in it. just a reference to id in the employee table.
    After that you can easily get the required result.

  3. For your Question Try like this..
    First Create a Function which will return the WeekDate name i..e Sunday, Monday..


    Create Function [dbo].[DayOfWeek](@dtDate Datetime)
    Returns Varchar(10)
    Declare @rtDayofWeek varchar(10)
    Declare @weekDay INT
    Set @WeekDay=((DatePart(dw,@dtDate)+@@DateFIRST-7)%7)
    Select @rtDayofWeek=Case @weekDay
    when 0 then ‘Saturday’
    WHEN 1 THEN ‘Sunday’
    WHEN 2 THEN ‘Monday’
    WHEN 3 THEN ‘Tuesday’
    WHEN 4 THEN ‘Wednesday’
    WHEN 5 Then ‘Thursday’
    when 6 Then ‘Friday’


    Create a Temp Table with Column
    EntryDate DateTime
    DayName Varchar(12)

    Populate the Table with date values as follows
    Declare @day_index datetime
    Select @day_index=’1-Jan-2008′
    Declare @index int
    select @index=1
    while @index < 365
    insert into dbo.TempDatetbl
    Select @day_index, dbo.DayOfWeek(@day_index)
    SELECT @day_index=DATEADD(day, 1, @day_index)
    Select @index=@index+1

    The above statements will insert the date values and corresponding day value.

    Normalize your IOData table to contain id,Entrydate,gatename

    Create a Table for Employee as id and name as Columns

    Now Query using the all the three tables to get the output(Emp Table,IOData Table and TempDatetbl)

  4. We are building a query to count the number of events per hour, per day. Most days there are hours that do not have any activity and therefore where the query is run the count of activities per hour show up but there are gaps and the query excludes these. We still want to show the hours that do not have activity and display a zero so that zero value can then be charted. The query we using looks like this …

    select datepart(Year, dev_time) as Year,
    datepart(Month, dev_time) as Month,
    datepart(Day, dev_time) as Day,
    datepart(Hour, dev_time) as Hour,
    count(tdm_msg) as Total_Incoming_Vehicles
    from TCKT_ACT
    where tdm_msg = ‘4162’ and dev_time >= DATEADD(day, – 1, GETDATE())
    group by datepart(Year, dev_time) ,
    datepart(Month, dev_time) ,
    datepart(Day, dev_time),
    datepart(Hour, dev_time)
    order by datepart(Year, dev_time) asc,
    datepart(Month, dev_time) asc,
    datepart(Day, dev_time) asc,
    datepart(Hour, dev_time) asc

  5. […] – bookmarked by 4 members originally found by susannakuenzl on 2008-12-30 Getting Missing Date Values in SQL Server – bookmarked […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s


%d bloggers like this: