SQL Server Performance

datediff.. timediff??

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

  1. qintian New 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. Adriaan New 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. qintian New Member

    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???
  4. Madhivanan Moderator

    Post some sample data and the result you want

    Madhivanan

    Failing to plan is Planning to fail
  5. Adriaan New Member

    One interval per call, correct. Give some examples of what you're trying to calculate ...
  6. khtan New 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. qintian New 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. qintian New Member

    kh i think u got wat i mean.. but how to convet the hours to days + hours??
  9. Adriaan New 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. khtan New 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. bparow New 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. FrankKalis Moderator

    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. Chappy New Member

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

    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. bparow New 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. FrankKalis Moderator

    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

Share This Page