SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 addtime?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kt
Yak Posting Veteran

75 Posts

Posted - 10/09/2012 :  10:22:46  Show Profile  Reply with Quote
I have the return date is 2012/10/03. Curretnly, it added one date to it with this fuction: DateAdd("d", 1, returnDate)> = 2012/10/04.
I don't want to add date, i want it to add time so i can have:
2012/10/03 23:59:59. I used addtime instead but it didn't work and got errror for ADDTIME is undefine

AddTime("23:59:59", returnDate)

can you help ?

thanks

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 10/09/2012 :  10:29:10  Show Profile  Reply with Quote
if you are using SQL Server, use the dateadd() function.

Why do you need the date time as 2012/10/03 23:59:59 ?

if it is for comparison, use
< '2012-10-04


instead of <= '2012/10/03 23:59:59'


KH
Time is always against us

Go to Top of Page

kt
Yak Posting Veteran

75 Posts

Posted - 10/09/2012 :  10:41:27  Show Profile  Reply with Quote
thanks for your respond. Yes i know, it would be easy if i do that, but my customer wants that.

Currently, every time they hit the next page from the search results, it added to one date from the ending date. So if they put ending date is 2012/10/03, they got results from the search. If they hit next page, the ending date is now 2012/10/04 and it keep adding one 2012/10/05, 2012/10/06 of every time they hit the next button.
They now don't want that, they want the ending date stills the same as 2012/10/03 but only adding time for 23:59:59 into it.
is that make senses?
How can i do this?

Thanks
Go to Top of Page

robvolk
Most Valuable Yak

USA
15654 Posts

Posted - 10/09/2012 :  11:10:25  Show Profile  Visit robvolk's Homepage  Reply with Quote
Still use DATEADD:
SELECT DATEADD(ms,86399997,returnDate)
This adds just enough milliseconds to be just before midnight of the next day. DATEADD can add any date or time increment, including minutes, seconds, hours, months, quarters, weeks, and so on.
Go to Top of Page

kt
Yak Posting Veteran

75 Posts

Posted - 10/09/2012 :  11:24:51  Show Profile  Reply with Quote
got error: Variable MS is undefined.
i've tried

SELECT DATEADD("ms",86399997,returnDate)
stills got error
ms is not a valid date/time format.

forgot to tell you, my db is in oracle. i don't think i make any diffrent?

any idea?

Edited by - kt on 10/09/2012 11:32:02
Go to Top of Page

robvolk
Most Valuable Yak

USA
15654 Posts

Posted - 10/09/2012 :  11:54:07  Show Profile  Visit robvolk's Homepage  Reply with Quote
Well, it sort of matters as SQLTeam is a Microsoft SQL Server website. Oracle questions can be directed to http://dbforums.com.

For future reference, if you use SQL Server, you wouldn't add quotes around ms or any other date/time increment in DATEADD/DATEPART/DATEDIFF functions.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000