getdate() problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

getdate() problem

Author

Topic

christina_rules
Starting Member

23 Posts

Posted - 2007-07-04 : 02:13:12

i've used this to add the day but my problem is that the result is not really accurate...dateadd(dd, 3, convert(datetime, convert(int, getdate())))..for example, today is 4th of july..after addition, it becomes 8th of july when it's suppose to be 7th..why?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-04 : 02:24:12

why are you converting date data type to integer ?

you can use this :

select dateadd(day, 3, getdate())

or just the date without time

select dateadd(day, datediff(day, 0, getdate()), 3)

KH
[spoiler]Time is always against us[/spoiler]

christina_rules
Starting Member

23 Posts

Posted - 2007-07-04 : 02:33:18

oh ok..i was trying to rid the time..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-04 : 02:37:26

quote:
Originally posted by christina_rules

oh ok..i was trying to rid the time..

this is the fastest method known.

dateadd(day, datediff(day, 0, getdate()), 0)

KH
[spoiler]Time is always against us[/spoiler]

dshelton
Yak Posting Veteran

73 Posts

Posted - 2007-07-04 : 02:39:28

To drop the time just convert to string:

select convert(varchar, dateadd(day, 3, getdate()), 101)

If value needs to be datetime then convert back, this drops the time:

select cast(convert(varchar, dateadd(day, 3, getdate()), 101) as datetime)

dshelton
Yak Posting Veteran

73 Posts

Posted - 2007-07-04 : 02:40:26

nice one khtan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-04 : 02:42:07

refer to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762 for more information on datetime

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-04 : 03:33:02

quote:
Originally posted by khtan

this is the fastest method known.
dateadd(day, datediff(day, 0, getdate()), 0)

This is faster, about 0.5% - 1.0% which is not much.

select cast(current_timestamp - 0.50000004 as int)

It is also more difficult to remember.

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-04 : 20:56:15

quote:
Originally posted by Peso

quote:
Originally posted by khtan

this is the fastest method known.
dateadd(day, datediff(day, 0, getdate()), 0)
This is faster, about 0.5% - 1.0% which is not much.

select cast(current_timestamp - 0.50000004 as int)

It is also more difficult to remember.

Peter Larsson
Helsingborg, Sweden

That returns an integer value, not a datetime. I think you would have to cast it back to a datetime.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-05 : 02:37:01

declare @dt datetime
select @dt = cast(current_timestamp - 0.50000004 as int)

works ok..

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-05 : 04:16:06

It won't work if you need to remove the time component of a data time column. You will need extract convert back to datetime data type.

Will this extra convert() back to datetime offset the 5 - 10% in speed ?

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2007-07-05 : 06:33:26

"To drop the time just convert to string:

select convert(varchar, dateadd(day, 3, getdate()), 101)

If value needs to be datetime then convert back, this drops the time:

select cast(convert(varchar, dateadd(day, 3, getdate()), 101) as datetime)"

That will be very slow

See: http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=38940#120953

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-05 : 09:38:25

quote:
Originally posted by Peso

declare @dt datetime
select @dt = cast(current_timestamp - 0.50000004 as int)

works ok..

Peter Larsson
Helsingborg, Sweden

It also does not work with all datetime values:


select
	a.DT,
	DATE = cast(cast(a.DT - 0.50000004 as int) as datetime)
from
	(
	select DT = getdate() union all
	select '17530101 11:59:59.997'
	) a


Results:
DT                       DATE
------------------------ ------------------------
2007-07-05 09:35:38.680  2007-07-05 00:00:00.000

Server: Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type datetime.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources