SQL Server Performance

GETDATE()

Discussion in 'General Developer Questions' started by Raulie, Apr 6, 2004.

  1. Raulie New Member

    A Developer is using getdate() to return all rows from SQL Server in a DateTime Column. However not all rows are being returned due to time differences. How do I make it so only the days are returned excluding the times.
  2. Adriaan New Member

    So do you need to apply criteria on the date part, ignoring the time, or do you want to show only the date part without the time? Also, GETDATE() will simply show the system date including the time in the format of the Windows locale for the server.
  3. Raulie New Member

    Thanks I want to query the datetime field ignoring the time, and yes while I'm at it not show it as well.
  4. ChrisFretwell New Member

    For select, you have 2 choices. You can remove the time componant from both sides and compare or you can compare with the time, including the range. If you have a clustered index on your date field then use the second option, otherwise its what you want.
    For display its simple, just convert.

    ie

    select convert(char(10),originaldatevalue, 101) --- or whatever date format you prefer
    from tablea
    where convert(char(102),originaldatevalue, 101) = convert(char(10),getdate(), 101)

    or
    select originaldatevalue, convert(char(10),originaldatevalue, 101) --- or whatever date format you prefer
    from tablea
    where originaldatevalue between convert(datetime,convert(char(10),getdate(), 101)) and dateadd(ss,86399,convert(datetime,convert(char(10),getdate(), 101)) )

    the where translates to originalvalue between 2004-04-06 00:00:00.000 and 2004-04-06 23:59:59.000

  5. Raulie New Member

    You know I should of thought about cutting out those days. To bad SQL Server handles the dates like that. For Developers who aren't aware of this it could cost them. I once read an article on this so I knew it had something to do with those times being inluded.
  6. GermanC4 New Member

    You can also try and use the Select getting all the dates that are LESS than a certain date, <br /><br />for example if you want all the ones that are from the day the system began to work to ... let's say Dec 31 2004<br /><br />you can try something like...<br /><br />SELECT * FROM &lt;TABLE&gt; WHERE SomeDateCol &lt; '1/1/2004'<br /><br /><br />Since it's less than (not less or equal) it will only give you date from 12/1/2003 and back regardless of the time [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />German [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]

Share This Page