dateadd function

Question

Post reply

dateadd function

newbieuser

SSCrazy Eights

Points: 9423
More actions
November 3, 2009 at 3:01 pm

#213181

Hi friends,
I'm trying to convert this piece of code to Oracle and kind of stuck with it.. This is a piece from SQL server procedure but accessing Oracle table.
create procedure..
DECLARE @SYSDATETRUNC DATETIME
SET @SYSDATETRUN = cast(convert(nvarchar(20), getdate(), 101) as datetime)
select
@CREATED= DateAdd(D, CDAYS, @SysDateTrunc),
....
from oracletable
If I need to code this in oracle procedure, can I just use
create procedure..
select
sysdate + cdays
....
from oracletable
Thank you

Viewing 12 posts - 1 through 11 (of 11 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I'm not sure what you are asking. Can you explain better what you are trying to accomplish.

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 2

Sorry, just trying to understand how this code works..

This is set in the declare section of the procedure..

@SYSDATETRUN = cast(convert(nvarchar(20), getdate(), 101) as datetime)

Is the above statement the same as 'sysdate' in Oracle?

In the body of the procedure, there is a select statement from a table in Oracle database that is using this variable.

select

@CREATED= DateAdd(D, CDAYS, @SysDateTrunc) from ..

I need to conver this procedure into Oracle and I researched that dateadd function adds the number of days to the mentioned date. So in the above select statement will this be the value of created - 'sysdate + cdays'

Thanks

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4552 More actions · Answer 3

create procedure..
select
sysdate + cdays
....
from oracletable
Thank you

sysdate cannot be fetched from 'oracletable' :w00t:

You can say select sysdate from dual or select sysdate + 1 from dual

sysdate + 1 will give you todays date & time +1 day ie tomorrow the same time.

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 4

Thanks, Is this function in sql server

cast(convert(nvarchar(20), getdate(), 101) as datetime)

equivalent to trunc(sysdate) in Oracle?

Thanks again

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

newbieuser (11/4/2009)
cast(convert(nvarchar(20), getdate(), 101) as datetime)

Instead of the above, I'd use the following in MS SQL Server:

select dateadd(dd, datediff(dd, 0, getdate()), 0)

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 6

Actually we have the cast function in sql server now, I'm converting this into Oracle so I need to know the equivalent for it.. Thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

newbieuser (11/4/2009)
Actually we have the cast function in sql server now, I'm converting this into Oracle so I need to know the equivalent for it.. Thanks

Understand, but I thought you should see another way to accomplish the task (truncating the time from a datetime value) in MS SQL Server.

As for Oracle, I have no experience with it. I have worked with MS SQL Server for over 12 years and have some experience with Borland InterBase (about 12 years ago during a side by side comparision with MS SQL Server 6.5).

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4552 More actions · Answer 8

newbieuser (11/4/2009)
Thanks, Is this function in sql server
cast(convert(nvarchar(20), getdate(), 101) as datetime)
equivalent to trunc(sysdate) in Oracle?
Thanks again

Dear,

Let me try to answer you 🙂

Thanks, Is this function in sql server

Which function are you talking about. If you are asking about sysdate then yes it is Oracle.

Now the answer to your primary query

Following is your SQL Server statements:

select cast(convert(nvarchar(20), getdate(), 101) as datetime)

Its Oracle equivalent is:

select trunc(sysdate) from dual

More details:

http://www.techonthenet.com/oracle/functions/trunc_date.php

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

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

Mazharuddin Ehsan (11/3/2009)
create procedure..
select
sysdate + cdays
....
from oracletable
Thank you
sysdate cannot be fetched from 'oracletable' :w00t:

Sure it can... SysDate can be "fetched" from any table. It will return the current date and time for every row that's in the table just like GETDATE() does in SQL Server. IIRC, CURRENT_TIMESTAMP can be used in both SQL Server and Oracle on ANY table.

--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)

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4552 More actions · Answer 10

Jeff Moden (11/4/2009)
Mazharuddin Ehsan (11/3/2009)
create procedure..
select
sysdate + cdays
....
from oracletable
Thank you
sysdate cannot be fetched from 'oracletable' :w00t:
Sure it can... SysDate can be "fetched" from any table. It will return the current date and time for every row that's in the table just like GETDATE() does in SQL Server. IIRC, CURRENT_TIMESTAMP can be used in both SQL Server and Oracle on ANY table.

Point taken Jeff. Thanks for the info. However, the fact remains that

In SQL Server we can say

select getdate()

while in Oracle, it will be

select sysdate from dual

So the answer to the OP is as follows

Oracle:

create procedure..

select

trunc(sysdate) + cdays

....

from oracletable

Although the column sysdate is not part of the table it can be used in a select statement. The dual seems to be working hidden in this case.

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

abatej SSChasing Mays Points: 626 More actions · Answer 11

Dual is just a single row pseudo table in Oracle. The reason it is there is because Selects have to have a from clause to be parsed correctly. Sysdate is a function that returns the current date and time and can be part of any select statement from any table.