SQL Server Performance Forum – Threads Archive
GETDATE() Function
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
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 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)
That is correct
but it is always best to do the formatting from the client side rather than doing it from the server.
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 />
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 />
That’s Correct, Frank. Thanks for all the replies. Regards,
Dan
]]>