# datediff.. timediff??

Discussion in 'General DBA Questions' started by qintian, Jul 11, 2006.

1. ### qintianNew Member

hi all,,

juz wanna ask about is there any TIMEDIFF function ?

currently we have datediff this i know well.. to compare the different of days between the date.

do we have something that can compare the different of times between the time that we enter
2. ### AdriaanNew Member

Check Books Online - SQL uses one data type for date and time. When using DATEDIFF, the datepart parameter specifies on which part of the date to calculate the difference.
3. ### qintianNew Member

DateDiff(interval,date1,date2[,firstdayofweek[,firstweekofyear]])

it only have 1 interval rite?? wat if i wan to compare the hours and the days??? i have to make 2 DateDiff function for my program???

Post some sample data and the result you want

Failing to plan is Planning to fail
5. ### AdriaanNew Member

One interval per call, correct. Give some examples of what you're trying to calculate ...
6. ### khtanNew Member

"it only have 1 interval rite?? wat if i wan to compare the hours and the days??? i have to make 2 DateDiff function for my program???"
Just compare the hours and convert to days + hours

datediff(hour, @date1, @date2)

KH
7. ### qintianNew Member

ok... example here...

Jul 09 2006 3:59PM date1
Jul 11 2006 4:32PM date2

ok.. now i wan to know the diff between the DAY and the MINUTE..
8. ### qintianNew Member

kh i think u got wat i mean.. but how to convet the hours to days + hours??
9. ### AdriaanNew Member

DECLARE @Date1 DATETIME, @Date2 DATETIME<br /><br />SET @Date1 = '2006-07-09 15:59'<br />SET @Date2 = '2006-07-11 16:32'<br /><br />SELECT DATEDIFF(DAY, @Date1, @Date2), DATEDIFF(MINUTE, @Date1, @Date2)<br /><br />If you want to have a result like "2 days and 33 minutes", you need to get only the time part of the date, which you can do with the CONVERT function, and then calculate the difference between the times:<br /><br />SELECT DATEDIFF(MINUTE, CONVERT(VARCHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, @Date1, 10<img src='/community/emoticons/emotion-11.gif' alt='8)' />, CONVERT(VARCHAR(<img src='/community/emoticons/emotion-11.gif' alt='8)' />, @Date2, 10<img src='/community/emoticons/emotion-11.gif' alt='8)' />)<br /><br />If the time part of @Date2 is earlier than that of @Date1, you will see that the DATEDIFF result on the times is negative, so you can followup on that to get the proper results - the number of days should be one less, and the number of minutes should be (24*60=) 1440 minus (plus) the difference.
10. ### khtanNew Member

<pre id="code"><font face="courier" size="2" id="code">declare@date1datetime,<br />@date2datetime<br /><br />select@date1= '2006-07-09 15:59',<br />@date2= '2006-07-11 16:32'<br /><br />selectconvert(varchar(10), datediff(day, @date1, @date2)) + 'D ' +<br />convert(varchar(5), dateadd(minute, datediff(minute, @date1, @date2), 0), 10<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br /><br />-- RESULT : 2D 00:33</font id="code"></pre id="code"><br /><br /><hr noshade size="1"><br /><font size="1">KH</font id="size1">
11. ### bparowNew Member

I have a column in my database called LST_DTE and a column called PUB_CDE.
The data in the PUB_CDE has a list of Publications and the data in the LST_DTE column is dates where the PUB_CDE has been updated. IE: 5/14/07.
I want to create a where clause that will pick the PUB_CDE only if it has been updated in the last 7 days.
IE something like today's date - LST_DTE > 7 DAYS

Can somebody help me with this?
12. ### FrankKalisModerator

quote:Originally posted by bparow
I want to create a where clause that will pick the PUB_CDE only if it has been updated in the last 7 days.
IE something like today's date - LST_DTE > 7 DAYS

Can somebody help me with this?
You can translate this almost 1:1 into T-SQL. Like:

...
WHERE your_column >= DATEADD(DAY, DATEDIFF(DAY,0, GETDATE()), GETDATE())

You might also want to have a look at this:
http://www.sql-server-performance.com/fk_datetime.asp

--
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
13. ### ChappyNew Member

select * from table where PUB_CDE > DATEADD(dd, -7, GETDATE())
14. ### FrankKalisModerator

Oops...
Looking at Chappy's reply, modify my WHERE clause to
DATEADD(DAY, DATEDIFF(DAY,0, GETDATE()) - 7, 0)

--
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
15. ### bparowNew Member

where PUB_CDE < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) -7, 0)

gives me the following error:
Syntax error converting datetime from character string.

Since PUB_CDE is a string value

I want it to display the PUB_CDE's only if the LST_DTE is within today or the previous 7 days.
16. ### FrankKalisModerator

Either you have bad DATE data in your table that cannot be converted to DATETIME or you need to specify some dateformat. Check out SET DATEFORMAT in BOL to see if that resolves your issue? To check if you have bad data you can use the ISDATE() function. If you get somewhere a 0, you have bad data in your table.

--
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de