SQL Server Performance Forum – Threads Archive

A date question

Given two timestamps, how would you find the datetime of the start (i.e. midnight) of each Sunday within this interval? I’m not sure yet if I want this as a query or a simple loop in a procedure – it would be interesting to see both actually. Side question – can anyone give a good link to a page on datetimes? For stuff like using system functions, datetime arithmetic and so on.

Check BOL topic "CAST and CONVERT" topic…
http://msdn2.microsoft.com/en-us/library/ms187928.aspx http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx
MohammedU.
Moderator
SQL-Server-Performance.com

make use of MVJ’s F_TABLE_DATE herehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762
KH

See if this helps:<a target="_blank" href=http://www.sql-server-performance.com/fk_datetime.asp>http://www.sql-server-performance.com/fk_datetime.asp</a> That is my own throw at this topic. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Thanks for the links but I’m still having problems with two specific things: 1)Given @startdate & @enddate, I need to find all days/weeks/months which are partially or totally incldued in this period. Since I’m then going to look for record with timestamps in a given day/week/month, I actually need to be able to find the start and end of each chosen day/week/month. 2)Given @startdate & @enddate, I need to find the number of hours between the two. Since a difference of 1 second (don’t need better resolution) is 1/3600 ~ 0.000278, what would be the best datatype for this fractional value? Thankyou very much.

quote:Originally posted by jdx Thanks for the links but I’m still having problems with two specific things: 1)Given @startdate & @enddate, I need to find all days/weeks/months which are partially or totally incldued in this period. Since I’m then going to look for record with timestamps in a given day/week/month, I actually need to be able to find the start and end of each chosen day/week/month. 2)Given @startdate & @enddate, I need to find the number of hours between the two. Since a difference of 1 second (don’t need better resolution) is 1/3600 ~ 0.000278, what would be the best datatype for this fractional value? Thankyou very much.

1) Have you take a look at the F_TABLE_DATE ? 2) no_of_hours = datediff(hour, @startdate, @enddate). What is the resolution that you required ? How many decimal places ?
declare @no_of_hours decimal(10,4)
no_of_hours = datediff(minute, @startdate, @enddate) / 60.0 KH

1)Ah yes I had a closer look. Do you know if I’m allowed to use those functions as-is in a commercial environment? i.e. can I cut and paste the functions? It’s actually not for code but to illustrate to a developer – I’m working as the designer on this; I’m not able to link to code examples but have to include them in my documentation. 2)That looks good. I need good enough resolution that it’s accurate to 1 second. I guess it also needs to support differences of multiple years, 1 million would be a safe upper limit. Oh, another question. If I have an application calling my stored procedures, can the inputs to the sp be datetime types or must the application pass in strings which I convert? Thanks for your help.

"If I have an application calling my stored procedures, can the inputs to the sp be datetime types or must the application pass in strings which I convert?"
You should have the input to the SP in datatime. With string you will have the hustle to convert from string to datetime and also determine the correct format to use MM-DD-YYYYY or DD-MM-YYYY or YYYY-MM-DD etc.
KH

An yyyy-mm-dd string is always interpreted correctly. Other data types usually reflect regional settings in Windows, so they can be unpredictable.

]]>