SQL Server Performance

GETDATE() Function

Discussion in 'General DBA Questions' started by Bredsox, Jun 27, 2005.

  1. Bredsox New Member

    Hi All,
    I need to get the GETDATE as 2005-06-25(No time part needed) format for my query.

    For Example,
    Update Orders
    Set ExpirationDate = GETDATE() -- This one should be 2005-06-25 format, no time needed.
    I tried like this: select CAST(GETDATE() AS varchar(12)), but it gives me Jun 25, 2005.
    Your help will be greately appreciated. Thank you all.

    Regards,
    Dan
  2. Bredsox New Member

    quote:Originally posted by Bredsox

    Hi All,
    I need to get the GETDATE as 2005-06-25(No time part needed) format for my query.

    For Example,
    Update Orders
    Set ExpirationDate = GETDATE() -- This one should be 2005-06-25 format, no time needed.
    I tried like this: select CAST(GETDATE() AS varchar(12)), but it gives me Jun 25, 2005.
    Your help will be greately appreciated. Thank you all.

    Regards,
    Dan

    I got it, never mind. It's like ...WHERE ExpirationDate = convert(varchar, getdate(), 112)
  3. dineshasanka Moderator

    That is correct
    but it is always best to do the formatting from the client side rather than doing it from the server.
  4. FrankKalis Moderator

    Here are some more variations on that topic. <br />Personally I prefer the first one as I think it offers maybe the best performance here.<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT DATEADD(d,DATEDIFF(d,0,GETDATE()),0)<br /><br />SELECT CAST(SUBSTRING(CAST(GETDATE() AS BINARY(<img src='/community/emoticons/emotion-11.gif' alt='8)' />),1,4) + 0x00000000 AS DATETIME)<br />SELECT CAST(CAST(SUBSTRING(CAST(GETDATE() AS binary(<img src='/community/emoticons/emotion-11.gif' alt='8)' />),1,4) AS INT) AS DATETIME)<br /><br />SELECT CONVERT(DATETIME,CONVERT(CHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' />,GETDATE(),112))<br />SELECT CONVERT(CHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' />,GETDATE(),112)<br />SELECT CAST(CAST(GETDATE() AS VARCHAR(12)) AS DATETIME)<br />SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) <br /></font id="code"></pre id="code"><br /><br />You might also find this interesting:<a target="_blank" href=http://www.sql-server-performance.com/fk_datetime.asp>http://www.sql-server-performance.com/fk_datetime.asp</a><br /><br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  5. FrankKalis Moderator

    Btw Dinesh, I think this is no presentational issue here. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  6. Bredsox New Member

    That's Correct, Frank. Thanks for all the replies.

    Regards,
    Dan

Share This Page