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 |
|
icebo3
Starting Member
20 Posts |
 Posted - 2003-09-24 : 23:55:45
|
| i am running report for user using sql script. the script will be schedule to run daily and excuted using batch file. as i don't want to run this batch during peak hour (many many transaction) so i would run it during nonpeak but my user want data from that peak hour ... the last time i did was to run it during peak and use the function "between getdate()-1 and getdate()" at 7pm to get data from yesterday 7pm to today 7pm... later i reschedule it to run at 9pm and use the function "between convert(varchar,getdate(),101)+' 07:00:00 PM' and getdate()" ... what i really want to know is , is this "convert(varchar,getdate(),101)+' 07:00:00 PM'" script good(fast excution time, right way of writing,will not give error) ? and also what other way/function we can use ? |
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2003-09-25 : 01:37:33
|
quote:
also what other way/function we can use
Thanks to Arnold/Jeff ...http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29360select DateAdd(dd,DateDiff(dd,0,getdate()),0) TODAY_DAYselect DateAdd(dd,DateDiff(dd,0,getdate()),-1) YESTERDAY_DAYselect DateAdd(hh,DateDiff(hh,0,getdate()),0) TODAY_HOURselect DateAdd(hh,DateDiff(hh,0,getdate()),-1) YESTERDAY_HOURselect DateAdd(mi,DateDiff(mi,0,getdate()),0) TODAY_MINUTEselect DateAdd(mi,DateDiff(mi,0,getdate()),-1) YESTERDAY_MINUTESekar~~~~Success is not a destination that you ever reach. Success is the quality of your journey. |
 |
|
|
icebo3
Starting Member
20 Posts |
Posted - 2003-09-25 : 03:39:32
|
| ur way is good ... but i still need to run your script at 7pm to capture records from 7pm to 7pm which is still peak hour... i plan to run it at 9 pm but get data from 7pm to 7pm |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-09-25 : 05:59:39
|
SELECT Dateadd(hh, 19, DateAdd(dd,DateDiff(dd,0,getdate()),-1)) AS Yest,Dateadd(hh, 19, DateAdd(dd,DateDiff(dd,0,getdate()),0)) AS Today...Owais
Make it idiot proof and someone will make a better idiot |
|
|
|
icebo3
Starting Member
20 Posts |
Posted - 2003-09-28 : 21:52:45
|
| gotcha ....understand u ...i did it this way "convert(varchar,getdate(),101)+' 07:00:00 PM'" is this way correct ???btw, which one is better (performance, correctness, etc???) |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-09-29 : 06:19:04
|
Over thousands of rows, you'll see a sizeable performance hit with a convert. Don't convert across datatypes unless you have to. It also prevents SQL Server from using the appropriate indexes, if used within a WHERE clause.Owais
Make it idiot proof and someone will make a better idiot |
|
|
|
icebo3
Starting Member
20 Posts |
Posted - 2003-09-30 : 01:58:59
|
| thks for the advise ... i believe i need to do some correction ..... |
|
|
|
|
|
|
|
|
fixing the timestamp