datediff.. timediff??

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

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.

thx adriaan..


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???

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

"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

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..

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

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.

<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">

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?

select * from table where PUB_CDE > DATEADD(dd, -7, GETDATE())

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.