Post reply

Date - without the time

  • April 8, 2011 at 2:49 am

    #236372

    I have just created a Dates look up table. It contains a DateID (int, primary key) and a smalldatetime column that contains dates for the next 10 years. I'm going to be doing endless date comparisons and will often need Select statements like:

    Select DateID FROM tblDates WHERE theDate = @SomeDate

    So, I want the smalldatetime column to just contain dates - with no time element.

    I messed around for a while looking this up - and all I've managed to do so far is to get the dates in there looking like this 12/01/2011 00:00:00

    Is there a way to get rid of the 00:00:00 ?

    Edit: just thought of something. If I created a varchar(10) field I could copy Left(thedate,10) into it.

    Which is faster / more sensible...

    Select DateID FROM tblDates WHERE theDate = @SomeDate (where theDate is a smalldatetime column and @SomeDate is of datatype smalldatetime)

    Or

    Select DateID FROM tblDates WHERE theDateStr = @SomeDateStr (where theDateStr is a varchar column and @SomeDate is of datatype varchar)

  • April 8, 2011 at 6:24 am

    #1309091

    Does the date you will be comparing (e.g. @SomeDate in your example) contain a time element?

    If not, then you won't have a problem, as both the lookup table and date will have a time of 00:00:00.

    Storing the date as a varchar will take up more storage than the smalldatetime, so will ultimately be slower than smalldatetime. Whether it's slow enough to matter is up to you to test.

    If you store the date as a varchar, then you will have to convert the comparison date to a varchar before comparing, which will also add some overhead.

    If you do go down the varchar route, then I recommend you store the date reversed and without the / delimeter e.g. 20110112 instead of 12/01/2011. If you store it as 12/01/2011 it will be more difficult to sort anything in date order, and would be extremely difficult to do any date range comparison e.g. date between 28/01/2011 and 03/02/2011.

  • April 8, 2011 at 6:33 am

    #1309098

    you can also strip the time portion off of the parameter so you can compare dates to dates:

    Select DateID FROM tblDates WHERE theDate = select DATEADD(dd, DATEDIFF(dd,0,@SomeDate), 0)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 8, 2011 at 7:10 am

    #1309129

    Ian Scarlett (4/8/2011)

    Does the date you will be comparing (e.g. @SomeDate in your example) contain a time element?

    If not, then you won't have a problem, as both the lookup table and date will have a time of 00:00:00.

    Storing the date as a varchar will take up more storage than the smalldatetime, so will ultimately be slower than smalldatetime. Whether it's slow enough to matter is up to you to test.

    If you store the date as a varchar, then you will have to convert the comparison date to a varchar before comparing, which will also add some overhead.

    If you do go down the varchar route, then I recommend you store the date reversed and without the / delimeter e.g. 20110112 instead of 12/01/2011. If you store it as 12/01/2011 it will be more difficult to sort anything in date order, and would be extremely difficult to do any date range comparison e.g. date between 28/01/2011 and 03/02/2011.

    I'd make a slightly different recommendation. If you decide you really need it as varchar, then make that a calculated column. You're are storing a date, so use one of the date data types to store it - this will help because eventually, somehow, that varchar column will end up with an invalid date that the date data type would have prevented.

    If the dates you are comparing are a date data type, you'd really be better off using the same. Converting one to the other will prevent the usage of indexes between the tables. If the other dates have a time element, check out the "common date routines" link in my signature for ways to strip the time from them.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • April 8, 2011 at 7:48 am

    #1309171

    WayneS (4/8/2011)

    If you decide you really need it as varchar, then make that a calculated column.

    Ignore my suggestion, Wayne's is far more sensible.

  • April 9, 2011 at 2:05 am

    #1309524

    Thanks to all for your replies.

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

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