Manipulating And Using DateTime Data

  • Comments posted to this topic are about the content posted at

    Robert W. Marda
    Billing and OSS Specialist - SQL Programmer
    MCL Systems

  • A good article with some interesting observations.

    I would add a point that when using a string to represent the date it is better to get in the habit of using a format which is not dependant on the dateorder setting.

    i.e. use

    SET @Date = '20040930'


    SET @Date = {d '2004-09-30'}

    rather than

    SET @Date = '9/30/2004'

  • One of the habits that I have developed, especially while developing date driven reports is to add this code to every stored procedure that I create:

    --Include the entire End Date (Set time to 11:59)

     Select @EndDate = DateAdd(d, 1,@EndDate)

     Select @Enddate = cast(convert(varchar, @EndDate,101) as datetime) 

     Select @EndDate = DateAdd(s,-1,@EndDate)

    --This is usually done in one statement, I broke it out for readability.


    --Convert Start Date to midnight

     SELECT @Startdate = cast(convert(varchar, @StartDate,101) as datetime) 

    This gives the full date range.


  • I sent this to one of our developers recently:

    --to select all records dated 9th January 2004 in myTable_T use

    select * from myTable_T

    where dateQueued Between '09 Jan 2004' and '10 Jan 2004'

    --which is equivilent to

    select * from myTable_T

    where dateQueued Between '09 Jan 2004 00:00:00' and '10 Jan 2004 00:00:00'

    --note the time stamp is midnight on both days to get JUST the 9th use

    declare @date datetime

    set @date = '09 Jan 2004' --time defaults to midnight 00:00:00

    select * from myTable_T

    where dateQueued Between @date and DateAdd(ss,-1,dateAdd(dd,1,@date))

    --to show what the dateadd code does above

    select @date

    select DateAdd(ss,-1,DateAdd(dd,1,@date))
    "I don't know what I don't know."

  • Good point about sorting when date is stored in character datatype. I used to get various date points (i.e. first day of month, last day, etc...) by stripping the Year(), Month() and then stringing it back together, however I found this EXCELLENT article which generates all of these date points by strictly using built-in T-SQL functions. For example the last day of the month is: select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+1, 0)), and of course you will commonly substitute a variable for the getdate() call here. Anyway read the article, the author has all the date points you will need:

  • Good article, great for those new to T-SQL and also a great reminder for those of us that are slightly more seasoned.  I deal with PostgreSQL alot and forget about how MS handles datetimes whenever I switch back to my MS SQL databases. 

    I'll definately be adding this one to my breifcase.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply