How can I Add 02.45 hour to my Date

StartDate is actually 02:15 as datetime so why I cannot add 02:15hour to
now.
Set @.StartDate = '1899-12-30 02:15:00.000'
Select GetDate() now, GetDate() + @.StartDate as added
-- Result is 2005-02-08 14:11:07.860 -- 2005-02-06 16:26:07.860
Thanks
Murat BUDAKYou should specify to what you want to add it to, SQL's date is broken down
into segments, year, month day etc...
Have a look at the dateadd function
Untested :
select dateadd(hour,2.15,getdate())
You would say, dateadd(hour,@.targetdate,2.15) I don't know if the decimal
might bugger up your solution.
Personally I would convert the amount of time in hours to seconds and then
use dateadd seconds to add the seconds to my current date
"Murat BUDAK" wrote:

> StartDate is actually 02:15 as datetime so why I cannot add 02:15hour to
> now.
> Set @.StartDate = '1899-12-30 02:15:00.000'
> Select GetDate() now, GetDate() + @.StartDate as added
> -- Result is 2005-02-08 14:11:07.860 -- 2005-02-06 16:26:07.860
> Thanks
> Murat BUDAK
>
>|||On Tue, 8 Feb 2005 14:15:10 +0200, Murat BUDAK wrote:

>StartDate is actually 02:15 as datetime so why I cannot add 02:15hour to
>now.
>Set @.StartDate = '1899-12-30 02:15:00.000'
>Select GetDate() now, GetDate() + @.StartDate as added
>-- Result is 2005-02-08 14:11:07.860 -- 2005-02-06 16:26:07.860
>Thanks
>Murat BUDAK
>
Hi Murat,
I don't recommend it, but if you really insist on using the + operator
between two datetime variables to add an amount of time, then you need to
get at least your base date and time right.
Run this in QA
SELECT CAST ('02:15:00.000' AS datetime)
to see why getdate() + '1899-12-30 02:15:00.000' won't result in "now + 2
1/4 hour".
The recommended method to add time to a datetime value is to use DATEADD:
SELECT DATEADD (minute, (60*2) + 15, getdate())
or
SELECT DATEADD (hour, 2, DATEADD (minute, 15, getdate()))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 8 Feb 2005 04:29:02 -0800, Mal .mullerjannie wrote:
(snip)
>Untested :
>select dateadd(hour,2.15,getdate())
Hi Mal,
DATEADD takes an integer as second parameter, so the fractional part
(0.15) will be discarded.
And even if DATEADD would take fractions, this would still be wrong, as
two hours and fifteen minutes equals 2.25 hours, not 2.15 hours.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

How accurate is GetDate() ?

According to MS, GetLocalTime() (in C++) is only accurate to approx a
second,
even though it reports milliseconds, and calling it twice and computing
the
interval can on occasion lead to a negative interval.

Is T-SQL's GetDate() more accurate than that, or at least
non-decreasing?

Thanks,
JimOn 26 Sep 2006 11:28:07 -0700, jim_geissman@.countrywide.com wrote:

Quote:

Originally Posted by

>According to MS, GetLocalTime() (in C++) is only accurate to approx a
>second,
>even though it reports milliseconds, and calling it twice and computing
>the
>interval can on occasion lead to a negative interval.
>
>Is T-SQL's GetDate() more accurate than that, or at least
>non-decreasing?

Hi Jim,

Getdate() (or, to use the ANSI standard name, CURRENT_TIMESTAMP) is
accurate to 1/300th of a second. I've never seen it decrease. Apart from
that, it is as accurate as the system clock of the server - if an admin
sets the system clock three hours back, SQL Server will report from the
past.

--
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis wrote:

Quote:

Originally Posted by

>
Getdate() (or, to use the ANSI standard name, CURRENT_TIMESTAMP) is
accurate to 1/300th of a second. I've never seen it decrease. Apart from
that, it is as accurate as the system clock of the server - if an admin
sets the system clock three hours back, SQL Server will report from the
past.
>

Just to split hairs over this a little: The maximum precision of the
value returned by GETDATE() is 1/300th of a second. That doesn't
necessarily mean it is *accurate* to within 1/300th of a second
compared to some other time measurement such as the system clock.
However, like Hugo, I have never heard of any issue where serial
executions of GETDATE() went negative. It's not difficult to verify
that the time returned by GETDATE() is within a fraction of a second of
the system clock value returned by other means.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||>Is T-SQL's GetDate() more accurate than that, or at least non-decreasing? <<

Yes; read the other answers. The real answer is that the FIPS-127
tests require five or more decimal places of seconds in a timestamp for
Federal use. Maybe when we get to 64 bit hardware and a better
internal representation ..|||Thanks, fellows.

By way of experiment, I wrote a loop that wrote 100,001 GetDate()s to a
table,
with an identity column to record the sequence. That took ~2 sec on
this
server. Then I examined the 100,000 intervals. Their distribution:

Interval (sec) N
0 99855
..13 44
..16 100
..30 1

I conclude that on this particular SQL2000 server, the clock usually
ticks every
160 ms (but not always).

I did this a few times and never saw a negative interval, although
that's not proof
it could never happen.

Thanks again
Jim