Datetime to date | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Datetime to date

Is there a way to set a datetime column to just date?
Sorry, no. In our systems, in 99% of the cases we have no use for the time part either but we just have to live with it.
The best you can do is to reduce it to smallDateTime. That way you will have precision up to the minute however you will save 4 bytes for each raw.
I found a way of doing this from the coding side of my project so its ok.
Another question however regarding dates –
How can I get dates older than 6 months ? Is there a special function for working with dates?
DateAdd, DateDiff. Take a look at BOL.
Can someone tell me whats wrong with this?
Insert into SOrdersArchive values
Select * from SOrders
where SOrder in
(SELECT SOrder
FROM SOrders where DATEDIFF(MONTH, DelDate, GETDATE()) > 0)
try:<pre id="code"><font face="courier" size="2" id="code">Insert into SOrdersArchive<br />Select * from SOrders <br />where DATEDIFF(MONTH, DelDate, GETDATE()) &gt; 0</font id="code"></pre id="code">Better then original query even if you remove <b>values </b> there <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />
Thanks that worked.
And another question – is there a way to have a stored procedure executed after a specific amount of time – E.g run a stored procedure once a week?
yes, use sql server agent.
To run a SP once a week
run SQL server agent and define a new job to it to run ur SP as u want.
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by eclair21</i><br /><br />Is there a way to set a datetime column to just date?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />You have to wait till the next version. This will implement the DATE datatype. Currently a DATETIME (or SMALLDATETIME for that matter) *always* contain a date and a time portion. However, there are several methods to set the time to midnight 00:00:00 so that it doesn’t hurt in queries anymore. Here are some variation how to do this:<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT DATEADD(d,DATEDIFF(d,0,getdate()),0)<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 />SELECT CAST(SUBSTRING(CAST(GETDATE() AS BINARY(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />),1,4) + 0x00000000 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 />–<br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
]]>