date function

Question

date function

smknox

Hall of Fame

Points: 3275
More actions
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

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

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

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 1

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)

smknox Hall of Fame Points: 3275 More actions · Answer 2

both columns are smalldatetime.

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

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

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]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

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]

Lowell SSC Guru Points: 323518 More actions · Answer 5

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!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

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]

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 7

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)

smknox Hall of Fame Points: 3275 More actions · Answer 8

smknox

Hall of Fame

Points: 3275

August 7, 2009 at 8:49 pm

#1036371

thanks so much. This works.

Sam

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 9

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)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 10

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)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 11

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

S Hodkinson SSCertifiable Points: 7529 More actions · Answer 12

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

smknox Hall of Fame Points: 3275 More actions · Answer 13

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

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 14

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)