Tony Rogerson's ramblings on SQL Server

Dec 31 2007 11:59PM

Dec 31 2007 11:59PM

print cast(  '2007-12-31 23:59:30'  as datetime )

print cast(  '2007-12-31 23:59:30'  as smalldatetime )

Dec 31 2007 11:59PM

Jan  1 2008 12:00AM

If you use DATEPART( second on the casted date you will see that on casting to smalldatetime you will get 0.

smalldatetime is often used when you are storing only the date portion or you are not interesting in seconds, this is to save space more than anything else.

When you query columns that are definied as smalldatetime for instance....

select * from trades where trade_date between '20070101' and '2007-12-31T23:59:59'

You need to make sure you don't specify the seconds otherwise SQL Server will round your input so the above query will actually become...

select * from trades where trade_date between '20070101' and '2008-01-01T00:00:00'

It's probably safer to use >= and < for instance...

select * from trades where trade_date >= '20070101' trade_date < '2008-01-01'

Another aspect - make sure you specify date literals without the hypens! If somebody changes your default langauge to British then '2007-10-01' will actually become 1st January 2007 and not 10th October 2007.

create table calendar (

    calendar_id             int not null identity constraint pk_calendar primary key clustered,

    period_start            datetime    not null,

    period_end              as cast( left( convert( varchar(60),

                 dateadd( day, -1, case period_type

                                        when 'WY' then dateadd( week, 1, period_start )

                                        when 'WI' then dateadd( week, 1, period_start )

                                        when 'M' then dateadd( month, 1, period_start )

                                        when 'Q' then dateadd( month, 3, period_start )

                                        when 'H' then dateadd( month, 6, period_start )

                                        when 'Y' then dateadd( year, 1, period_start )

                                   end ), 121 ), 10 ) + 'T23:59:59.997' as datetime ),  

    period_name             varchar(50) not null,

    period_type             varchar(2)  not null    check( period_type in ( 'WY', 'WI', 'M', 'Q', 'H', 'Y' ) ),

        constraint uk_calendar_period_start_type unique( period_start, period_type ),

        constraint uk_calendar_period_name unique( period_name, period_type )

)

go

/***

    period_type

        WY - Week in Year according to SQL Server; WI - Week in Year according to ISO

        M  - Month

        Q  - Quarter

        H  - Half

        Y  - Year

***/

--  Populate the calendar (month based stuff first; that is periods are aligned with start of month)

declare @start_date datetime

declare @date datetime

set @start_date = '20060101'

set @date = @start_date

set nocount on

while @date <= dateadd( year, 1, current_timestamp )

begin

    insert calendar( period_start, period_name, period_type )

        values( @date, left( datename( month, @date ), 3 ) + '-' + datename( year, @date ), 'M' )

    if datepart( month, @date ) in ( 1, 4, 7, 10 )

        insert calendar( period_start, period_name, period_type )

            values( @date, 'Q' + case datepart( month, @date )

                                    when 1 then '1'

                                    when 4 then '2'

                                    when 7 then '3'

                                    when 10 then '4'

                                 end + '-' + datename( year, @date ), 'Q' )

    if datepart( month, @date ) in ( 1, 7 )

        insert calendar( period_start, period_name, period_type )

            values( @date, 'H' + case datepart( month, @date )

                                    when 1 then '1'

                                    when 7 then '2'

                                 end + '-' + datename( year, @date ), 'H' )

    if datepart( month, @date ) = 1

        insert calendar( period_start, period_name, period_type )

            values( @date, 'Y' + datename( year, @date ), 'Y' )

    set @date = dateadd( month, 1, @date )

end

go