Page 1 of 3 123 LastLast
Results 1 to 15 of 38
  1. #1
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136

    Unanswered: SQL Server 2000 date time

    Is there a better way of doing this?

    Code:
    DECLARE @PriorDayDate as varchar(10)
    DECLARE @CurrentDayDate as varchar(10)
    DECLARE @StartHourTime as varchar(10)
    DECLARE @EndHourTime as varchar(10)
    DECLARE @PriorDayDateANDStartHourTime as varchar(20)
    DECLARE @PriorDayDateANDEndHourTime as varchar(20)
    DECLARE @CurrentDayDateANDStartHourTime as varchar(20)
    DECLARE @CurrentDayDateANDEndHourTime as varchar(20)
    
    SELECT @PriorDayDate = convert(varchar,(GETDATE()-1),126)
    SELECT @CurrentDayDate = convert(varchar,(GETDATE()),126)
    SELECT @StartHourTime = convert(varchar,(dateadd(hh, -1, getdate())),108)
    SELECT @EndHourTime = convert(varchar,getdate(),108)
    SELECT @StartHourTime = left(@StartHourTime, 2) + ':00:0000'
    SELECT @EndHourTime = left(@EndHourTime, 2) + ':00:0000'
    SELECT @PriorDayDateANDStartHourTime = @PriorDayDate + ' ' +  @StartHourTime
    SELECT @PriorDayDateANDEndHourTime = @PriorDayDate + ' ' +  @EndHourTime
    SELECT @CurrentDayDateANDStartHourTime = @CurrentDayDate + ' ' +  @StartHourTime
    SELECT @CurrentDayDateANDEndHourTime = @CurrentDayDate + ' ' +  @EndHourTime
    
    --select @PriorDayDate
    --select @CurrentDayDate
    --select @StartHourTime
    --select @EndHourTime
    --select @PriorDayDateANDStartHourTime
    --select @PriorDayDateANDEndHourTime
    select cast(@PriorDayDateANDStartHourTime as datetime) as 'PD Start Time'
    select cast(@PriorDayDateANDEndHourTime as datetime) as 'PD End Time'
    select cast(@CurrentDayDateANDStartHourTime as datetime) as 'CD Start Time'
    select cast(@CurrentDayDateANDEndHourTime as datetime) as 'CD End Time'

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, there sure is

    do it without resorting to varchars

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    yeah but how do I get the start and end times to be xx:00:00.000?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    glad you asked

    it is most easily done when you use date arithmetic

    DATEDIFF counts the number of interval boundaries crossed between two datetimes

    DATEDIFF between now and five minutes ago, measured in days, is 0

    but if right now is 3 minutes past midnight, then DATEDIFF between now and five minutes ago, measured in days, is 1

    so, how to strip off the time...

    find the DATEDIFF between right now and 1900-01-01 00:00:00 (let's call this the "base date at midnight"), measured in days

    it'll be some large number of days -- remember, this is the number of interval boundaries crossed between right now and that midnight date

    so, take that number of intervals, add it back to that base date at midnight, and, vwalah! it gives you the date as of today but at midnight

    Code:
    SELECT DATEADD(d,DATEDIFF(d,'1900-01-01',GETDATE()),'1900-01-01')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    AWESOME!! Thanks!

    This is what happens when you start off with a previously used piece of code (for similar purposes but the varchar was necessary) and try to build on it. Sometimes it is better to throw it out and start fresh.

    Code:
    SELECT DATEADD(hh, (convert(int,(left(convert(varchar,getdate(),108), 2))-1)),(DATEADD(d,DATEDIFF(d,'1900-01-01',GETDATE()-1),'1900-01-01'))) as 'PD STart Time'
    SELECT DATEADD(hh, convert(int,(left(convert(varchar,getdate(),108), 2))),(DATEADD(d,DATEDIFF(d,'1900-01-01',GETDATE()-1),'1900-01-01'))) as 'PD End Time'
    SELECT DATEADD(hh, (convert(int,(left(convert(varchar,getdate(),108), 2))-1)),(DATEADD(d,DATEDIFF(d,'1900-01-01',GETDATE()),'1900-01-01'))) as 'CD Start Time'
    SELECT DATEADD(hh, convert(int,(left(convert(varchar,getdate(),108), 2))),(DATEADD(d,DATEDIFF(d,'1900-01-01',GETDATE()),'1900-01-01'))) as 'CD End Time'

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Or, more concisely:
    Code:
    SELECT DATEADD(d,DATEDIFF(d,0,GETDATE()),0)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    But, I'm curious

    What are you trying to accomplish?

    The blind dude should have asked if this is a presentation issue

    Much like presenting you queen to the center of the board
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    As a matter of fact, rewrite your code like this:
    Code:
    select	dateadd(day, -1, dateadd(hour, datediff(hour, 0, getdate())-1, 0)) as 'PD STart Time'
    select	dateadd(day, -1, dateadd(hour, datediff(hour, 0, getdate()), 0)) as 'PD End Time'
    select	dateadd(hour, datediff(hour, 0, getdate())-1, 0) as 'CD Start Time'
    select	dateadd(hour, datediff(hour, 0, getdate()), 0) as 'CD End Time'
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    Or, more concisely:
    Code:
    SELECT DATEADD(d,DATEDIFF(d,0,GETDATE()),0)
    yeah, sure...


    ... but the first time someone sees the approach, using "0" as the base date is too much to grasp

    i like using an actual value like '1900-01-01 00:00:00' because it lets the novice focus on what is actually of most importance in understanding the approach at first, which is to count the number of interval boundaries

    and not get hung up on 'WTF? "0" is a valid date???'

    once they're comfortable with the concept of the intervals, then you toss the "0" date into it, and switch to concentrating on the intervals themselves

    DATEADD(d,DATEDIFF(d,0,GETDATE()),0) = midnight today

    DATEADD(m,DATEDIFF(m,0,GETDATE()),0) = midnight on the first of this month

    DAY(DATEADD(m,DATEDIFF(m,0,GETDATE())-1,0)) = the last day of last month

    and so on
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I still say that it is VERY poor programming practice to ever use an integer in place of a date. While SQL Server has included code to support this particular coding error for many revisions, it should not and someday may not continue to include extra code to support this syntax error.

    While function overloading may make this a non-issue, it is still an error and I don't think we ought to encourage sloppy code.

    -PatP

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so, you like my use of '1900-01-01 00:00:00' here?

    oh, sweet, i am redeemed!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Pat,

    In that code, 0 does not represent a date. Programatically, it represents a constant.

    You want to pass a string to the function, which expects a date value. Is that a coding error? Poor programming practice?
    What is the difference between asking the function to interpret a date, and asking it to interpret an integer?
    Should we always declare a datetime variable so we have a valid datetime datetype to pass to the function?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    In that code, 0 does not represent a date. Programatically, it represents a constant.
    What kind of constant?
    Quote Originally Posted by blindman
    You want to pass a string to the function, which expects a date value. Is that a coding error? Poor programming practice?
    SQL Server represents date constants as strings. No, using a date constant is appropriate for a date argument.
    Quote Originally Posted by blindman
    What is the difference between asking the function to interpret a date, and asking it to interpret an integer?
    Should we always declare a datetime variable so we have a valid datetime datetype to pass to the function?
    I see a huge difference between dates and numbers. What is the square root of today, and how do you compute it?

    There is no need to create a datetime variable if you use a datetime constant.

    I still believe that it is wrong for example code to use incorrect data types, even when the database engine has code to specifically compensate for that kind of error. It really bothers me that SQL Server even produces slightly faster code when it encounters this error... At the very least they could have made it a tiergrube so it worked but performance was awful.

    -PatP

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    i am redeemed!
    You've been deemed so many times, I'm not sure if you can be redeemed!

    -PatP

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    At the very least they could have made it a tiergrube so it worked but performance was awful.
    nice word, appeals to my german background

    by the way, (german) tier = (english) animal

    what you meant is spelled teergrube
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •