Days Difference Between 2 Dates

  • Comments posted to this topic are about the item Days Difference Between 2 Dates


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • DECLARE @Date1 DATETIME = '2012-08-28 11:53:00'

    select cast(@Date1 AS int)

    It gives the result 41147. Can anyone tell me how this conversion is done.

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • kapil190588 (10/2/2012)


    DECLARE @Date1 DATETIME = '2012-08-28 11:53:00'

    select cast(@Date1 AS int)

    It gives the result 41147. Can anyone tell me how this conversion is done.

    There are 41147 days between 1900-01-01 and 2012-08-28.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • This was removed by the editor as SPAM

  • Thanks

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Interesting stuff!

    Is there any specific reason when we do the following

    DECLARE @Date1 DATETIME SET @Date1= '2012-08-28 11:53:00'

    select CAST(cast(@Date1 AS float) AS DATETIME)

    That we lose 3 ms in the process of reconverting back into a date?

  • Davin21 (10/2/2012)


    Interesting stuff!

    Is there any specific reason when we do the following

    DECLARE @Date1 DATETIME SET @Date1= '2012-08-28 11:53:00'

    select CAST(cast(@Date1 AS float) AS DATETIME)

    That we lose 3 ms in the process of reconverting back into a date?

    The reason is probably that the granularity of DATETIME is 3ms.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • very interesting one, 🙂 thank you for the question.

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • I have a table CityMaster, LocationMaster and Location table from another database. In LocationMaster CityID (int) is stored while in Citymaster (CityID, CityName, StateID) fields are stored, in Location CityName is stored. I want to insert the CityID corresponding to Citymaster table as in Location Cityname is stored. I write a query

    "

    update L

    set L.CityID = C.CityID

    from CityMaster C

    join

    WCDentalSQL_TOR..Location TOR ON tor.Fcity COLLATE Latin1_General_CI_AI = C.CityName COLLATE Latin1_General_CI_AI

    join

    LocationMaster L on l.code COLLATE Latin1_General_CI_AI = tor.Flocation COLLATE Latin1_General_CI_AI

    where tor.Flocation='TOR'

    "

    while running this query no rows gets updated. Can anyone tell me why?

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • "Interesting" behaviour:

    DECLARE @date datetime

    SET @date='2012-10-01 13:21:00'

    SELECT CAST(@date AS int)

    result: 41182

    SELECT CAST(CAST(@date AS float) AS int)

    result: 41181

  • kapil190588 (10/2/2012)


    I have a table CityMaster, LocationMaster and Location table from another database. In LocationMaster CityID (int) is stored while in Citymaster (CityID, CityName, StateID) fields are stored, in Location CityName is stored. I want to insert the CityID corresponding to Citymaster table as in Location Cityname is stored. I write a query

    "

    update L

    set L.CityID = C.CityID

    from CityMaster C

    join

    WCDentalSQL_TOR..Location TOR ON tor.Fcity COLLATE Latin1_General_CI_AI = C.CityName COLLATE Latin1_General_CI_AI

    join

    LocationMaster L on l.code COLLATE Latin1_General_CI_AI = tor.Flocation COLLATE Latin1_General_CI_AI

    where tor.Flocation='TOR'

    "

    while running this query no rows gets updated. Can anyone tell me why?

    This question would be more appropriately posted to a relevant forum.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Thanks for the question. Like to see datetime questions, they are helpful.

  • Nice question. Made me think.

    Tom

  • L' Eomot Inversé (10/2/2012)


    Nice question. Made me think.

    Thanks for giving it a shot Tom.

    Looks like you changed your avatar and signature.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (10/2/2012)


    Looks like you changed your avatar and signature.

    Yes, I change the avatar every few months; and switch languages (and messages) in the signature message quite often too. Avoids being too much the same all the time.

    Tom

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply