Forum Index » Open Mic » Bek or SQL pros  XML
[Post New] 12/19/2008 10:50:31 AM  #1  Subject: Bek or SQL pros [Up]
Flounder
2 Wheel Post Whore
Joined: 05/07/2008
Messages: 19665
Offline
Anyone know how I can formulate this statement:

and kt.ORIGNL_DATA_CREATE_DT >= (sysdate - 1)

to instead be sysdate minus 12 hours?

[Post New] 12/19/2008 10:57:51 AM  #2  Subject: Bek or SQL pros [Up]
Bek
2 Wheel Spartan
Joined: 06/19/2008
Messages: 1831
Location: Bolingbrook
Offline
flounder wrote:Anyone know how I can formulate this statement:

and kt.ORIGNL_DATA_CREATE_DT >= (sysdate - 1)

to instead be sysdate minus 12 hours?


I'm here...

Which variant of SQL are you using again?

In M$ T-SQL, you can use a variation of DATEADD(), which takes not only a numeric parameter, but a DATEPART parameter (i.e. year, month, hour, etc.), so you can tell it to add or subtract X DATEPART from sysdate.
[Post New] 12/19/2008 10:57:55 AM  #3  Subject: Re:Bek or SQL pros [Up]
PaikyPoo
2 Wheel Supreme
Joined: 04/20/2008
Messages: 8065
Location: North Showa
Offline
you should state which db you're working with
[Post New] 12/19/2008 10:59:18 AM  #4  Subject: Re:Bek or SQL pros [Up]
Flounder
2 Wheel Post Whore
Joined: 05/07/2008
Messages: 19665
Offline
Oracle, mah bad. I'm using TOAD.. SQLPLUS
[Post New] 12/19/2008 11:00:07 AM  #5  Subject: Bek or SQL pros [Up]
Flounder
2 Wheel Post Whore
Joined: 05/07/2008
Messages: 19665
Offline
Bek wrote:
flounder wrote:Anyone know how I can formulate this statement:

and kt.ORIGNL_DATA_CREATE_DT >= (sysdate - 1)

to instead be sysdate minus 12 hours?

I'm here...

Which variant of SQL are you using again?

In M$ T-SQL, you can use a variation of DATEADD(), which takes not only a numeric parameter, but a DATEPART parameter (i.e. year, month, hour, etc.), so you can tell it to add or subtract X DATEPART from sysdate.

oooOoo, let me google that, thanks home slice.
[Post New] 12/19/2008 11:02:22 AM  #6  Subject: Bek or SQL pros [Up]
PaikyPoo
2 Wheel Supreme
Joined: 04/20/2008
Messages: 8065
Location: North Showa
Offline
flounder wrote:oooOoo, let me google that, thanks home slice.

don't bother. that won't work for oracle using oracle's sql
[Post New] 12/19/2008 11:06:01 AM  #7  Subject: Re:Bek or SQL pros [Up]
Flounder
2 Wheel Post Whore
Joined: 05/07/2008
Messages: 19665
Offline
ghey..

[Post New] 12/19/2008 11:06:32 AM  #8  Subject: Re:Bek or SQL pros [Up]
PaikyPoo
2 Wheel Supreme
Joined: 04/20/2008
Messages: 8065
Location: North Showa
Offline
google it you lazy bastard.
[Post New] 12/19/2008 11:06:47 AM  #9  Subject: Re:Bek or SQL pros [Up]
Flounder
2 Wheel Post Whore
Joined: 05/07/2008
Messages: 19665
Offline
what do I google though?
[Post New] 12/19/2008 11:07:03 AM  #10  Subject: Bek or SQL pros [Up]
Suh-C
2 Wheel Post Whore
Joined: 05/07/2008
Messages: 16341
Location: NW burbs
Offline
Fish is fat. lulz
[WWW]
[Post New] 12/19/2008 11:09:02 AM  #11  Subject: Re:Bek or SQL pros [Up]
Flounder
2 Wheel Post Whore
Joined: 05/07/2008
Messages: 19665
Offline
Nevermind I found some date functions:

http://www.psoug.org/reference/date_func.html

lez see if this works.
[Post New] 12/19/2008 11:09:52 AM  #12  Subject: Re:Bek or SQL pros [Up]
PaikyPoo
2 Wheel Supreme
Joined: 04/20/2008
Messages: 8065
Location: North Showa
Offline
try this you retard

do a search on specifics. i googled "oracle manipulate sysdate". the link above was 2nd link...

gawd, and you really think you're going to become a dba when you can't even google?
[Post New] 12/19/2008 11:10:31 AM  #13  Subject: Bek or SQL pros [Up]
Bek
2 Wheel Spartan
Joined: 06/19/2008
Messages: 1831
Location: Bolingbrook
Offline
Okay, with Oracle, dates are handled in a far different way than in SQL Server.

To the DATE datatype in Oracle, the base unit is a day. So SYSDATE + 1 means SYSDATE + 1 day.

If you want to do a smaller increment, you have to use decimals or fractions.

Thus, SYSDATE + .5 = SYSDATE + 12 hours.

Or

SYSDATE + 7/24 = SYSDATE + 7 hours

Or

SYSDATE + 7/1440 = SYSDATE + 7 minutes

Be careful, because certain calculations can result in trailing decimals which you may not want, so you may have to nest your calculation within a ROUND() function.
[Post New] 12/19/2008 11:10:33 AM  #14  Subject: Re:Bek or SQL pros [Up]
Flounder
2 Wheel Post Whore
Joined: 05/07/2008
Messages: 19665
Offline
I figured you guys would give me a faster response
[Post New] 12/19/2008 11:10:54 AM  #15  Subject: Re:Bek or SQL pros [Up]
Vice Chaz
2 Wheel Dedicated
Joined: 05/07/2008
Messages: 6983
Location: Albany Park
Offline
PaikyPoo wrote:you can't even google?

Even I can do that. lulz
[Post New] 12/19/2008 11:11:18 AM  #16  Subject: Bek or SQL pros [Up]
Flounder
2 Wheel Post Whore
Joined: 05/07/2008
Messages: 19665
Offline
Bek wrote:Okay, with Oracle, dates are handled in a far different way than in SQL Server.

To the DATE datatype in Oracle, the base unit is a day. So SYSDATE + 1 means SYSDATE + 1 day.

If you want to do a smaller increment, you have to use decimals or fractions.

Thus, SYSDATE + .5 = SYSDATE + 12 hours.

Or

SYSDATE + 7/24 = SYSDATE + 7 hours

Or

SYSDATE + 7/1440 = SYSDATE + 7 minutes

Be careful, because certain calculations can result in trailing decimals which you may not want, so you may have to nest your calculation within a ROUND() function.

Oh shit, let me try that.. thanks!
[Post New] 12/19/2008 11:12:01 AM  #17  Subject: Re:Bek or SQL pros [Up]
Bek
2 Wheel Spartan
Joined: 06/19/2008
Messages: 1831
Location: Bolingbrook
Offline
flounder wrote:I figured you guys would give me a faster response

Geez, I go to all of this damn effort to provide you with a nice write-up, and you two fockers waste my time by going to Google, which is probably what you should have done in the first place!!!

Thanks for nothing!


Dorks...
[Post New] 12/19/2008 11:18:28 AM  #18  Subject: Re:Bek or SQL pros [Up]
PaikyPoo
2 Wheel Supreme
Joined: 04/20/2008
Messages: 8065
Location: North Showa
Offline
Bek wrote:
flounder wrote:I figured you guys would give me a faster response
Geez, I go to all of this damn effort to provide you with a nice write-up, and you two fockers waste my time by going to Google, which is probably what you should have done in the first place!!!

Thanks for nothing!


Dorks...

do NOT lump me in the same category with this dunder wonder!
[Post New] 12/19/2008 11:20:11 AM  #19  Subject: Re:Bek or SQL pros [Up]
Headmuffs
2 Wheel Post Whore
Joined: 05/07/2008
Messages: 15709
Location: Bobby's World
Offline
see, when it comes to n3rd talk, Paiky and Hammerhead get along quite well...
[Post New] 12/19/2008 11:20:43 AM  #20  Subject: Re:Bek or SQL pros [Up]
Flounder
2 Wheel Post Whore
Joined: 05/07/2008
Messages: 19665
Offline
Trust me this ain't nerd talk, it's simple shit. Paiky and bek knows way m0re than me
Forum Index » Open Mic » Bek or SQL pros
Go to:   
Powered by JForum 2.1.8 © JForum Team