Days Difference Between 2 Dates

  • Dwain Camps

    SSC Guru

    Points: 86893

    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

  • kapil_kk

    SSC-Insane

    Points: 21316

    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/

  • Dwain Camps

    SSC Guru

    Points: 86893

    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

  • kapil_kk

    SSC-Insane

    Points: 21316

    Thanks

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

  • Davin21

    SSC Eights!

    Points: 850

    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?

  • Dwain Camps

    SSC Guru

    Points: 86893

    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

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    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.

  • kapil_kk

    SSC-Insane

    Points: 21316

    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/

  • archie flockhart

    SSCrazy

    Points: 2339

    "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

  • Dwain Camps

    SSC Guru

    Points: 86893

    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

  • (Bob Brown)

    SSCrazy

    Points: 2705

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

  • TomThomson

    SSC Guru

    Points: 104773

    Nice question. Made me think.

    Tom

  • Dwain Camps

    SSC Guru

    Points: 86893

    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

  • TomThomson

    SSC Guru

    Points: 104773

    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 31 total)

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