first and last day of the current week ?

[bobby123]

Hi Forum

in xbasic you can use month_start(date()) to find the first day of the month and month_end(date()) to find the last day, but is there a comand to find the first and last day of the current week ?

Thanks

Robert

[Davidk]

Re: first and last day of the current week ?

This is just rough so will need some extras like taking care of Sat and Sun the way you want to, but... should do it. I'm not sure if there is built-in function to do this.

dim weekBegAdj as n
dim weekEndAdj as n
dim currDate as d = ctod(e._currentRowDataNew.YOUR_GRID_DATE)
dim currDay as c = cdow(currDate)

SELECT
    CASE currDay = "Monday"
        weekBegAdj = 0
        weekEndAdj = 4
    CASE currDay = "Tuesday"
        weekBegAdj = -1
        weekEndAdj = 3
    CASE currDay = "Wednesday"
        weekBegAdj = -2
        weekEndAdj = 2
	CASE currDay = "Thursday"
        weekBegAdj = -3
        weekEndAdj = 1
    CASE currDay = "Friday"
        weekBegAdj = -4
        weekEndAdj = 0
end SELECT

currMon = add_bus_days(currDate,weekBegAdj)
currFri = add_bus_days(currDate,weekEndAdj)

[ids-dave]

Re: first and last day of the current week ?

Another option is if you have data involved and are using a view, you could do it from SQL

SELECT field_x, field_y, field_z, DATEADD(week, DATEDIFF(week, 0, GETDATE()), - 1) AS week_starts_on
FROM dbo.mytablename

or.... substitute GETDATE with a date field if you need to know the week starting date based on the data record rather than the current calendar

[Stan Mathews]

Re: first and last day of the current week ?

I usually miss something but for a Sunday through Saturday week.

dt = {06/15/2012}
? dt-dow(dt)+1
= {06/10/2012}
? dt+(7-dow(dt))
= {06/16/2012}

dt = {06/10/2012}
? dt-dow(dt)+1
= {06/10/2012}
? dt+(7-dow(dt))
= {06/16/2012}

dt = {06/16/2012}
? dt-dow(dt)+1
= {06/10/2012}
? dt+(7-dow(dt))
= {06/16/2012}

[bobby123]

Re: first and last day of the current week ?

Hi Stan, David

Thanks for the reply's, I will most likely go the sql route on this occation, I have done this calc before using something along the lines of:

adddate(curdate(), 1-DAYOFWEEK(curdate()) DAY) WeekStart
adddate(curdate(), 7-DAYOFWEEK(curdate()) DAY) WeekEnd

But, had some trouble with the alpha query builder, just need to find the correct syntax, while mucking around I just wonder if there was an xbasic week start/end date function but after looking a bit I don't thing there is one.

But thankyou both again, I will both a try

Thanks
Robert

[ids-dave]

Re: first and last day of the current week ?

Robert, when building your SQL statements within the Alpha component, I have found it easier to 1st create the statement from SQL (view or query) and then copy & paste it into the Alpha SQL builder dialog. That's my approach anyway when creating expressions especially if it involves the SQL CAST function.

One other note, you may run into the "Cannot Parse" error whenever you go back into the component to make changes. If that happens, you can copy the SQL statement from the initial (read only) view before clicking the "Edit SQL in Query Builder" button, and then paste it after opening the editor.

[agillbb]

Re: first and last day of the current week ?

You can even skip the A5 sql and just use the DB view instead.

[RichCPT]

Re: first and last day of the current week ?

One other note, you may run into the "Cannot Parse" error whenever you go back into the component to make changes. If that happens, you can copy the SQL statement from the initial (read only) view before clicking the "Edit SQL in Query Builder" button, and then paste it after opening the editor.

Yep, that error drives me crazy. I have given up on going back and forth between Enterprise Manager and A5 on that. I will have to remember David's tip if I decide to try that again.

[RichCPT]

Re: first and last day of the current week ?

I just have to mention this, as this is something I have to deal with in an application I'm converting to Alpha: There are "calendar weeks" and there are "work weeks" or work shifts. In the system I'm converting each employee may have their own "Work Week" and they file reports based on their "Work Week". Each individual may start their Work Week on a different calendar day then other people. And to make things really interesting not everyone uses 7 days in a week! http://en.wikipedia.org/wiki/Shift_work

-RichCPT

[ids-dave]

Re: first and last day of the current week ?

Rich, that is a real oddity. I have found scheduling systems to be very challenging (sometimes fun and sometimes frustrating). Since we're comparing.... my labor management system allows for 2, 3, or 4 weeks to be set with varying rotations. Example, an employee can have a 3 week rotation where they work MTWTF on week 1, SMTWT on week 2 and then TWTFS on week 3. Then the rotation starts all over again. On top of that, there are screens / user functions to adjust individual days and allow employees to trade work days

Hope I never run into the varying calendar weeks you described !

[Davidk]

Re: first and last day of the current week ?

Rich,

I was helping to build a system like that... every employee had different parameters. We built a table to hold (let say) 52 rows for each emp, containing length of week (5 days), start day, end day and whatever else was needed. Looked up each emp work week record as needed.

[bobby123]

Re: first and last day of the current week ?

Hi David

Thanks for the heads up, I have noticed the "Cannot Parse" error.

On some of the other comments, Working with dates and times is a requirement in most dbase systems and can be a real pain, there maybe an opportunity here for a feature pack for alpha which can take the labour out of time based calculations. I for one would buy that.

Thanks for all the help

Robert