Function For Fixing Date

[icebo3]

i am going a cummulative sql script where user want records daily. so 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 Monday

how to i do this in sql server?

 

[LogicalmanUS]

icebo3,

Here's the function to find the Monday of the current week (up to and including Saturday) and the number of days lapsed since the Monday.
Copy and paste it in QA and run it from there:

DECLARE @myDATE datetime
DECLARE @iDAYS int
SET @myDATE = GETDATE()
SET @iDAYS = datepart(dw,@myDATE)-2
PRINT 'Lapsed Days since last Monday is ' + CAST(@iDAYS AS varchar(10)) + ' days'
PRINT 'Mondays Date was: ' + CAST((GETDATE()-@iDAYS) AS varchar(100))


Logicalman

 

[icebo3]

this is good ..

for "SET @iDAYS = datepart(dw,@myDATE)-2" does this mean i need to change the "2" when i am in another day ??

i am scheduling this script so that it will auto grabd the monday of that week and starting counting from mon to tues , mon to wed , and etc ... if i am to change the "2" then i think this method can be automated..

 

[LogicalmanUS]

icebo3,

No, the 2 is the SQL WeekDay for Monday.

The WeekDay function in SQL returns 1 for Sunday through 7 for Saturday.

Therefore, the above algorithm takes the current WeekDay number, subtracts 2, and the result is the number of days elpased since the last Monday.

It will fail if it is run on a Sunday, but only inasmuch that it will return a -1, indicating that Monday is the following day.

The only automation required would be in a WHILE statement, for example, if the current day was Thursday, then the algorithm would return 3 (Thursday 5-2 Monday), you may then use this as a counter, or, just use a variable initialized to 2, and loop it, incrementing each loop, until it equals the current WeekDay.

Logicalman

 

[icebo3]

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 Sun

my 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 ....

 

[LogicalmanUS]

icebo3,

You can use the first part:

DECLARE @myDATE datetime
DECLARE @myDATE2 datetime
DECLARE @iDAYS int
SET @myDATE = GETDATE()
SET @iDAYS = datepart(dw,@myDATE)-2

SET @myDATE2 = GETDATE()-@iDAYS --This is Mondays Date

to introduce a variable into your own code:

select a,b from XYZ where complete_time between @myDATE2 and @myDATE


Try that out, I believe you need to encompass the data variables in quotes as they are true date datatypes.

If you want to do away with the variables, then you could try:

select a,b from XYZ where complete_time
between GETDATE()-(datepart(dw, GetDate())-2) and GetDate()

Logicalman

 

[icebo3]

this is the script i am looking for ...thks

select a,b from XYZ where complete_time
between GETDATE()-(datepart(dw, GetDate())-2) and GetDate()

 

[icebo3]

whta happen if it is run on sunday ??

"select cast('2003-09-28'as smalldatetime)-(datepart(dw, '2003-09-28') -2) , GetDate()"

if i schedule this script to run , it will not be able to run on Sunday