SQL Server Performance

A date question

Discussion in 'SQL Server 2005 General Developer Questions' started by jdx, Feb 2, 2007.

  1. jdx New Member

    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.
  2. MohammedU New Member

  3. khtan New Member

  4. FrankKalis Moderator

    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> <br />That is my own throw at this topic. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  5. jdx New Member

    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.
  6. khtan New Member

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

    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.
  8. khtan New Member

    "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
  9. Adriaan New Member

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

Share This Page