Post reply

date function

  • August 7, 2009 at 6:40 pm

    #208365

    With the statement below, I would like to add the date part of DateAppointment to the time part of TimeStart. I get a runtime overrun with this statement. How should I modify?

    DATEADD(ms, DATEDIFF(ms, 0, b.TimeStart), DATEADD(dd, 0, DATEDIFF(dd, 0, a.DateAppointment)))

    AS dtime

  • August 7, 2009 at 6:46 pm

    #1036345

    What are the datatypes for the columns involved and would you post some sample data, please? There's no way to help on this one without that information.

    For future posts, you can get answers instead of questions if you follow the suggestions in the article at the first link in my signature below.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 7, 2009 at 7:45 pm

    #1036356

    both columns are smalldatetime.

    DateAppointment is: 2009-07-17 00:00:00

    TimeStart: 2009-07-16 12:30:00

  • August 7, 2009 at 8:04 pm

    #1036359

    You are getting an integer overflow because you are trying to count milliseconds since 1900.

    Try this query:

    SELECT 1000 * 3600*24 * 365.25*(2009-1900)

    Use seconds instead.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • August 7, 2009 at 8:06 pm

    #1036361

    Oops, that's still too big...

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • August 7, 2009 at 8:10 pm

    #1036363

    you should probably explain what you are trying to do; there's some logical errors in your code;

    this section:

    DATEDIFF(ms, 0, '2009-07-16 12:30:00') --milliseconds from 01/01/1900 to today--overflow

    is trying to calculate all the milliseconds from the beginning of SQL's default date;

    similarly, the last second section is calculating all the days from the beginning of SQL's default date;

    DATEDIFF(dd, 0, '2009-07-17 00:00:00')

    finally, why are you using milliseconds on a smalldatetime field, which does not have millisecond resolution anyway?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 7, 2009 at 8:10 pm

    #1036364

    Hmm, better go with minutes.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • August 7, 2009 at 8:16 pm

    #1036365

    The following formula will do it... convert to handle columns from a table instead of variables for a set based solution... this is just for demo...

    --I would like to add the date part of DateAppointment to the time part of TimeStart

    DECLARE @DateAppointment SMALLDATETIME,

    @TimeStart SMALLDATETIME

    SELECT @DateAppointment = '2009-07-17 14:30:10',

    @TimeStart = '2009-07-16 12:30:00'

    SELECT @TimeStart - DATEDIFF(dd,0,@TimeStart)

    + DATEADD(dd,DATEDIFF(dd,0,@DateAppointment),0)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 7, 2009 at 8:49 pm

    #1036371

    thanks so much. This works.

    Sam

  • August 7, 2009 at 8:51 pm

    #1036372

    smknox (8/7/2009)

    thanks so much. This works.

    Sam

    You bet and my pleasure. I am confused a bit, though... why do you need to do this? It's not often I see the need to take the date from one day and the time for another.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 18, 2009 at 7:11 pm

    #1041239

    Heh... stiffed again. As soon as they get their answer, they're off until the next time they need help.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 18, 2009 at 8:06 pm

    #1041243

    Jeff Moden (8/18/2009)

    Heh... stiffed again. As soon as they get their answer, they're off until the next time they need help.

    And you expected something different? Isn't there a saying about doing the same thing over and over again :w00t:

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • August 20, 2009 at 7:15 am

    #1042082

    Sorry it was not me but I always try to say 'Thanks'. And all I was doing was searching for the correct way to convert a Varchar string into a date ..................

    Madame Artois

  • August 20, 2009 at 5:33 pm

    #1042521

    Hi Jeff:

    The reason I was adding a date from one column to the time of another column is because: This is a medical application and one surgical procedure might have two or three associated procedures such as a trip to radiology on the day of surgery to do something pertinent to the surgical procedure. When the date of the surgical procedure changes, the dates for these associated procedures needs to move also, with the assumption that they will be done at the same times previously scheduled.

    It turns out I'm really a medical person and just barely able to follow you technical guys.(smile)

    Sam

  • August 20, 2009 at 11:46 pm

    #1042608

    smknox (8/20/2009)

    Hi Jeff:

    The reason I was adding a date from one column to the time of another column is because: This is a medical application and one surgical procedure might have two or three associated procedures such as a trip to radiology on the day of surgery to do something pertinent to the surgical procedure. When the date of the surgical procedure changes, the dates for these associated procedures needs to move also, with the assumption that they will be done at the same times previously scheduled.

    It turns out I'm really a medical person and just barely able to follow you technical guys.(smile)

    Sam

    Thanks Sam... just so you know, you can modify the date very easily just by adding to it or using DATEADD. You don't need the hassle of having a Date column and a Time column. For example, we all know what GETDATE(). What time is it at precisely the same instant tomorrow?

    SELECT GETDATE()+1

    ... or ...

    SELECT DATEADD(dd,1,GETDATE())

    There's no need to keep date and time separate. In fact, it's actually a bad thing. You saw what we just went though on the original problem.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 15 posts - 1 through 15 (of 15 total)

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