Equivalent SQL2005 for these Oracle statements

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 2005 Forums

Transact-SQL (2005)

Equivalent SQL2005 for these Oracle statements

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-07-28 : 01:50:55

What is the equivalent to the trunc statement and syntax? Thanks

How does one add a day/hour/minute/second to a date value? Oracle
The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours, minutes or seconds to the date. Look at these examples:

SQL> select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;
SYSDATE SYSDATE+1/24 SYSDATE+1/1440 SYSDATE+1/86400
-------------------- -------------------- -------------------- --------------------
03-Jul-2002 08:32:12 03-Jul-2002 09:32:12 03-Jul-2002 08:33:12 03-Jul-2002 08:32:13
The following format is frequently used with Oracle Replication:

select sysdate NOW, sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual;
NOW NOW_PLUS_30_SECS
-------------------- --------------------
03-JUL-2005 16:47:23 03-JUL-2005 16:47:53
Here are a couple of examples:

Description Date Expression
Now SYSDATE
Tomorow/ next day SYSDATE + 1
Seven days from now SYSDATE + 7
One hour from now SYSDATE + 1/24
Three hours from now SYSDATE + 3/24
An half hour from now SYSDATE + 1/48
10 minutes from now SYSDATE + 10/1440
30 seconds from now SYSDATE + 30/86400
Tomorrow at 12 midnight TRUNC(SYSDATE + 1)
Tomorrow at 8 AM TRUNC(SYSDATE + 1) + 8/24
Next Monday at 12:00 noon NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 12/24
First day of the month at 12 midnight TRUNC(LAST_DAY(SYSDATE ) + 1)
The next Monday, Wednesday or Friday at 9 a.m TRUNC(LEAST(NEXT_DAY(sysdate,MONDAY' ' ),NEXT_DAY(sysdate,WEDNESDAY), NEXT_DAY(sysdate,FRIDAY ))) + (9/24)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-28 : 02:04:29

Are you sure this is normally how it should be done in Oracle ? How would anybody understand what is doing on without using calculator to find out what is 1440, 86400

quote:
SQL> select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;
SYSDATE SYSDATE+1/24 SYSDATE+1/1440 SYSDATE+1/86400


select getdate(), dateadd(hour, 1, getdate()), dateadd(minute, 1, getdate()), dateadd(second, 1, getdate())

quote:

Description Date Expression
Now SYSDATE
Tomorow/ next day SYSDATE + 1
Seven days from now SYSDATE + 7
One hour from now SYSDATE + 1/24
Three hours from now SYSDATE + 3/24
An half hour from now SYSDATE + 1/48
10 minutes from now SYSDATE + 10/1440
30 seconds from now SYSDATE + 30/86400
Tomorrow at 12 midnight TRUNC(SYSDATE + 1)
Tomorrow at 8 AM TRUNC(SYSDATE + 1) + 8/24
Next Monday at 12:00 noon NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 12/24
First day of the month at 12 midnight TRUNC(LAST_DAY(SYSDATE ) + 1)
The next Monday, Wednesday or Friday at 9 a.m TRUNC(LEAST(NEXT_DAY(sysdate,MONDAY' ' ),NEXT_DAY(sysdate,WEDNESDAY), NEXT_DAY(sysdate,FRIDAY ))) + (9/24)

SELECT	[Now]				= GETDATE(),
	[Tomorrow]			= DATEADD(DAY, 1, GETDATE()),
	[Seven days FROM now]		= DATEADD(DAY, 7, GETDATE()),
	[Seven days FROM now 2]		= DATEADD(week, 1, GETDATE()),
	[One hour FROM now]		= DATEADD(hour, 1, GETDATE()),
	[Three hours FROM now]		= DATEADD(hour, 3, GETDATE()),
	[An half hour FROM now]		= DATEADD(minute, 30, GETDATE()),
	[10 minutes FROM now]		= DATEADD(minute, 10, GETDATE()),
	[30 seconds FROM now]		= DATEADD(minute, 10, GETDATE()),
	[Tomorrow at 12 midnight]	= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1),
	[Tomorrow at 8]			= DATEADD(hour, 8, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1))

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-28 : 02:05:55

refer to here for further info on date time in SQL Server
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

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

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-07-28 : 02:05:59

I should say what I am really after is SQL 2005 statements for:

Yesterday, Last Week, Last Month, Today, Current Week, Current Month, Last 7 days. It would also be better if the statements could be the same where d,w,m,y etc are variables and the user enters the time offset as a variable as well to add flexablity.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-28 : 02:08:46

quote:
Originally posted by harlingtonthewizard

I should say what I am really after is SQL 2005 statements for:

Yesterday, Last Week, Last Month, Today, Current Week, Current Month, Last 7 days. It would also be better if the statements could be the same where d,w,m,y etc are variables and the user enters the time offset as a variable as well to add flexablity.

Thanks

A simple CASE WHEN will do the job easily

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources