Post reply

Convert Time Stamp in Query

  • December 11, 2011 at 6:32 pm

    #247506

    I'm trying to query a databse that stores records with date/time stamp format - I need to return results for the entire day.......

    Example Order Date is stored as follows:

    2011-11-30 17:53:00.000

    If I use...........select * from Orders where orderdate = '2011-11-30' or if I try a like statement as follows:

    select * from Orders where orderdate like '%2011-11-30%'

    I don't get my desired results - I must need to use CAST, Convert or Trim (guessing)???

    Thanks in advance - I didn't see anything in BOL that would give me immediate assistance......lots of verbage about the different formats and converting the data - I don't want to convert - just query........

  • December 11, 2011 at 6:48 pm

    #1419840

    I think I found my answer on this website (should have searched here before posting...........

    I tried this............

    select * from orders where cast(orderdate as date) = '2011-11-30'

    It seems to work - still checking my results........

    Anybody agree?

  • December 12, 2011 at 12:55 am

    #1419876

    Please provide the DDL for your table so we can give you proper advise. Follow the link to Jeff Moden's article on posting etiquette in my footer text if you don't know what this means.

    Guessing that the timestamp value is stored in a column with a type derived from datetime, I would suggest to avoid using the type-casts as these will make SQL server not use any indices that may be available on the table, ergo your queries will become slow fast when the table grows.

    Instead use >= and < to specify a date range. This enables SQL server to use any indices if available. Like this:

    select *

    from dbo.Orders

    where orderdate >= {d '2011-12-11'}

    and orderdate < {d '2011-12-12'}


    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]

    If you don't have time to do it right, when will you have time to do it over?

  • December 12, 2011 at 2:05 am

    #1419894

    R.P.Rozema provided the solution.

    I just want to point to Some common date routines by Lynn Pettis

    and

    my rules of thumb for date/datetime related stuff http://www.sqlservercentral.com/Forums/FindPost1219915.aspx

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • December 12, 2011 at 4:09 am

    #1419935

    Thanks everyone for response.

    For the record both of these queries worked and returned exact same results:

    select * from Orders where cast(orderdate as date) = '2011-11-30'

    select *

    from dbo.Orders

    where orderdate >= {d '2011-11-30'}

    and orderdate < {d '2011-12-01'}

    RP - thank you for your response including tips on providing actual data, queries attempted, expected results, etc. I will try to provide actual data next time.

    Regards,

    BT

  • December 12, 2011 at 5:53 am

    #1419987

    Bron Tamulis (12/12/2011)

    Thanks everyone for response.

    For the record both of these queries worked and returned exact same results:

    select * from Orders where cast(orderdate as date) = '2011-11-30'

    select *

    from dbo.Orders

    where orderdate >= {d '2011-11-30'}

    and orderdate < {d '2011-12-01'}

    RP - thank you for your response including tips on providing actual data, queries attempted, expected results, etc. I will try to provide actual data next time.

    Regards,

    BT

    Actually, they DON'T provide the same results or at least not in the same amount of time... the query that uses CAST will be significantly slower and use significantly more resources because it will never be able to use an INDEX SEEK.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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