CODEWITHSUNDEEP
mysqlsql-server
Bobby
Bobby

Reputation: 320

Getting particular day using MySql

I need to get Bth working day in a month when the value of B is entered. For example, If b=12 in the month of January,2013 the resultant value should be in the date format as '17-01-2013' as the result is calculated after excluding Saturdays, Sundays & holidays in the month.

I have tried it in SQLserver with the following code & its working fine, but Im finding it difficult to execute it in MySql as some functions are not available as in Sqlserver.

    Declare 
@fromDate Date,
@Daydiff int

Set @fromDate ='01 jan 2013'

Set @Daydiff=datediff(day, @fromdate, dateadd(month, 1, @fromdate))
Select * from 
(
Select 
    dateadd(day,DayNo,@fromDate) as Date,
    dateName(weekday,(dateadd(day,DayNo,@fromDate))) As WeekDate,
    Datename(month,(dateadd(day,DayNo,@fromDate))) as MonthName,
    Row_number() Over (partition by (DatePart(month,(dateadd(day,DayNo,@fromDate)))) 
    order by (dateadd(day,DayNo,@fromDate))) as Business_day
from 
    (Select top (@Daydiff) row_number() over(order by (select 1))-1 as DayNo 
     from sys.syscolumns a cross join sys.syscolumns b)Dates
Where
    dateName(weekday,(dateadd(day,DayNo,@fromDate))) Not In ('Saturday','Sunday') and
    dateadd(day,DayNo,@fromDate) Not In (Select hdate from Holidays)
)A
Where Business_day=1

Note

Holidays is the static holidays table which contains list of holidays of 2013

I need a similar instance in Mysql. Kindly help me with this.

Upvotes: 4

Views: 307

Answers (2)

Harish Singh
Harish Singh

Reputation: 11

how to get the same result when only month and year are passed as parameters. Coz when i checked the code... its working when the date is 1st of the respective month, Like if I enter parameter as '2013-01-01' the result is absolute, but if the date is given as '2013-01-15' the procedure is counting the 1st day 15th and calculating the nth day starting from there.

Upvotes: 1

valex
valex

Reputation: 24144

SQLFiddle demo

If you need first day set OFFSET 0 in the end. If the second OFFSET 1, if 15-th set OFFSET 14

select d
FROM
(
SELECT @row := @row + 1 as row,
 DATE_ADD('2013-01-01', INTERVAL @row-1 DAY) d
from
(SELECT @row := 0) r,
(
select 1 n
union all 
select 2 n
union all 
select 3 n
union all 
select 4 n
union all 
select 5 n
union all 
select 6 n
) t1,
(
select 1 n
union all 
select 2 n
union all 
select 3 n
union all 
select 4 n
union all 
select 5 n
union all 
select 6 n
) t2
) num_seq

where 
d<DATE_ADD('2013-01-01', INTERVAL 1 MONTH)
and d not in (select hdate from Holidays )
and DAYNAME(d) not in ('Saturday','Sunday')
order by d
LIMIT 1 OFFSET 20

Version without OFFSET and LIMIT. See the latest where r=1 it is the 1-st day. If you need 15-th day change to where r=15

SQLFiddle demo

select d
from
(
select d,@r := @r + 1 as r
FROM
(SELECT @r := 0) r1,
(
SELECT @row := @row + 1 as row,
 DATE_ADD('2013-01-01', INTERVAL @row-1 DAY) d
from
(SELECT @row := 0) r,
(
select 1 n
union all 
select 2 n
union all 
select 3 n
union all 
select 4 n
union all 
select 5 n
union all 
select 6 n
) t1,
(
select 1 n
union all 
select 2 n
union all 
select 3 n
union all 
select 4 n
union all 
select 5 n
union all 
select 6 n
) t2
) num_seq

where 
d<DATE_ADD('2013-01-01', INTERVAL 1 MONTH)
and d not in (select hdate from Holidays )
and DAYNAME(d) not in ('Saturday','Sunday')
order by d
) rTable
where r=1

Upvotes: 2

Related Questions