SQL Server Performance Forum – Threads Archive
Converting date and time to date only…
I realize this must be very simple but… What is the simplest and most efficient way of converting a field that has both date and time to the mm/dd/yyyy only part of the date? I want to group data by date and this particular field contains the time. Thanks. Matthew MoranThe IT Career Builder’s Toolkit
http://www.cbtoolkit.com
select convert(varchar ,getdate(),101)
Madhivanan Failing to plan is Planning to fail
Thanks. Exactly what I needed. Matt
See if this helps:<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 />For your issue, here’s the management summary:<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT DATEADD(d,DATEDIFF(d,0,getdate()),0)<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 CAST(CAST(GETDATE() AS VARCHAR(12)) AS DATETIME)<br />SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) 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 /></font id="code"></pre id="code"><br />Especially the first should give your better performance as it relies only on efficient integer operations. Conversion to and from CHAR or VARCHAR is slower. <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 />
Well
I wonder why there is no datatype to store only Date
Madhivanan Failing to plan is Planning to fail
The next version will finally have a DATE data type. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
Frank, is there any link where I can know the new features of SQL Server2005 over 2000?
Madhivanan Failing to plan is Planning to fail
What abouthttp://www.microsoft.com/sql/2005/default.mspx ? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
]]>