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

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.

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

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

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

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?

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

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)

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?

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)