Post reply

GETDATE() remove Time

  • June 23, 2009 at 4:25 am

    #384825

    Hi All,

    Here is my Query,

    SET @AnoynomousPlayers=(SELECT isnull(count(*), 0)

    FROM tblApplicationPlayer

    WHERE (bitAnonymousPlayer = 1) AND (dtApplicationTakenDt = GETDATE() - 1) )

    Here the dtApplicationTakenDt is in Datetime and a also GETDATE() is also returning datetime,

    What i need is to convert both the date time to just Date format.And want to remove off the time.

    So that i can compare only with the date and not the time.

  • June 23, 2009 at 5:08 am

    #1013832

    perhaps do a 'between' statement

    e.g

    this would bring back everything that had the specified column between

    23-06-2009 00:00:00 and 24-06-2009 00:00:00

    dtApplicationTakenDt between CONVERT(varchar(50),getdate(),106)

    and CONVERT(varchar(50),dateadd(dd,1,getdate()),106)

  • June 23, 2009 at 5:25 am

    #1013839

    Converting to varchar's one of the the slowest ways to trim the time from a date. Also watch out for the edge cases. Between's inclusive on both sides. If a query needs all rows from yesterday, it shouldn't return rows from today at midnight.

    This should work.

    WHERE dtApplicationTakenDt >= dateadd(dd, datediff(dd,0, GETDATE())-1,0) AND < dateadd(dd, datediff(dd,0, GETDATE()),0)

    See http://sqlinthewild.co.za/index.php/2007/11/05/datetime-manipulation/ and http://sqlinthewild.co.za/index.php/2008/09/04/comparing-date-truncations/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 23, 2009 at 5:25 am

    #1013840

    add this : select rtrim(cast(GETDATE() as varchar(12)))

    Abhay Chaudhary
    Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

  • June 23, 2009 at 6:58 pm

    #1014371

    Gail's method has the distinct advantage of being able to seek using an index. The other methods would have to scan the table or the whole index.

    There was a slight typo in her example; this is the correct statement:

    [font="Courier New"]WHERE dtApplicationTakenDt >= dateadd(dd, datediff(dd,0, GETDATE())-1,0) AND dtApplicationTakenDt < dateadd(dd, datediff(dd,0, GETDATE()),0)

    [/font]

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • June 23, 2009 at 7:12 pm

    #1014377

    hi_abhay78 (6/23/2009)

    add this : select rtrim(cast(GETDATE() as varchar(12)))

    Abhay,

    1 Using CONVERT with a style parameter when converting date-time to string is more robust and explicit.

    2 Your statement does not work correctly when the active language is Russian.

    So if one must use a string conversion, something like:

    [font="Courier New"]select convert(nchar(10), current_timestamp, 120)[/font]

    ...would be better.

    I can only assume you posted too quickly, since the impressive job title and letters after your name in your signature would suggest that you know better.

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

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

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