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.
| Author |
Topic |
|
kt
Yak Posting Veteran
88 Posts |
 Posted - 2012-10-09 : 10:22:46
|
| 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 undefineAddTime("23:59:59", returnDate)can you help ?thanks |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-10-09 : 10:29:10
|
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[spoiler]Time is always against us[/spoiler] |
 |
|
|
kt
Yak Posting Veteran
88 Posts |
Posted - 2012-10-09 : 10:41:27
|
| 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 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-10-09 : 11:10:25
|
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. |
|
|
|
kt
Yak Posting Veteran
88 Posts |
Posted - 2012-10-09 : 11:24:51
|
| got error: Variable MS is undefined. i've triedSELECT DATEADD("ms",86399997,returnDate)stills got errorms 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? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-10-09 : 11:54:07
|
| 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. |
|
|
|
|
|
|
|
|
addtime?