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 : 03:11:39
|
| i am going a cummulative sql script where user want records daily. they want the records cummulative from Monday to Sunday example, Monday to Tuesday, Mon to Wed and Mon to Thrus until Sunday ...in oracle, we have the function called "next day (col , 'Monday') -1 " so that we can fix the Monday and start counting from that Mondayhow to i do this in sql server? |
|
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2003-09-24 : 04:50:52
|
| Check BOL for topic "SET DATEFIRST"Sekar~~~~Success is not a destination that you ever reach. Success is the quality of your journey. |
 |
|
|
icebo3
Starting Member
20 Posts |
Posted - 2003-09-24 : 21:39:47
|
| how do i apply this to my sql script as i am scheduling to run this sql script every day and i need to write sql script that can capture data from Monday to the day that i run the report, if the schedule report run on wed, then Mon to Wed, if on Sun then Mon to Sunmy current script example:select a,b from XYZ where complete_time between '09/22/2003' and (select getdate()) ... currently i am harcoding the '09/22/2003' as i need to update the sql script every week .... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-09-24 : 21:57:13
|
| SELECT a, b FROM XYZ WHERE complete_time BETWEEN DateAdd(day, 2-DatePart(dw, getdate()), getdate()) AND getdate()Actually this might be more accurate:SELECT a, b FROM XYZ WHERE complete_time BETWEEN DateAdd(day, 1, DateAdd(wk, DateDiff(wk, 0, getdate()), 0)) AND getdate() |
|
|
|
icebo3
Starting Member
20 Posts |
Posted - 2003-09-24 : 23:28:54
|
| yeah ... this is what i am looking for ...actually i can use "select dateadd(wk,DateDiff(wk, 0, getdate()),0)" is enough as this query return the Monday date at anytime, right ???i want to understand more about this query, first us get the datadiff from today date to 0, and then dateadd that value to '0' for the week (why by dateadd can return a value ???)...is it "select dateadd(wk,DateDiff(wk, 0, getdate()),0)" return me every "Monday" as my system setting is Monday and not Sunday... |
|
|
|
icebo3
Starting Member
20 Posts |
Posted - 2003-09-25 : 03:31:35
|
| also "select dateadd(wk,DateDiff(wk, 0, '2003-09-28'),0)" ... will this have problem if it is run on Sunday ??? example it is run on Sunday 28 Sep ... i would want my data from Monday 22 Sep to Sun 28 Sep ... |
|
|
|
|
|
|
|
|
function for fixing date