How to get number of Modays,Tuesdays ,etc between two date

Question

Hi evryone I amtrying to use the algorithm how to calculate the number of Tuesdays between two dates in TSQL to count the number of occurrence of days between two dates.

I just replace some parameters with my date, but I am getting syntax error. I've tried to remove datetime to use date but still getting an error

select 

datediff(day, -7, DATETIME('2015-06-30'))/7-datediff(day, -6,   DATETIME('2015-05-28'))/7 AS MON,

datediff(day, -6, DATETIME('2015-06-30'))/7-datediff(day, -5,DATETIME('2015-05-28'))/7 AS TUE,
datediff(day, -5,DATETIME('2015-06-30'))/7-datediff(day, -4, DATETIME('2015-05-28'))/7 AS WED,
datediff(day, -4, DATETIME('2015-06-30'))/7-datediff(day, -3, DATETIME('2015-05-28'))/7 AS THU,
datediff(day, -3,DATETIME('2015-06-30'))/7-datediff(day, -2,DATETIME('2015-05-28'))/7 AS FRI,
datediff(day, -2, DATETIME('2015-06-30'))/7-datediff(day, -1,DATETIME('2015-05-28'))/7 AS SAT,
datediff(day, -1,DATETIME('2015-06-30'))/7-datediff(day, 0, DATETIME('2015-05-28'))/7 AS SUN

You can't use `datediff(day, count, date)` in `mysql` as per the [documentation](https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_datediff). `mysql` supports `datediff(date, date)` only. — Arulkumar, Jun 16 '15 at 11:36
Please could you confirm if it is indeed MySQL or T-SQL *(Normally Microsoft SQL-Server)* that you're using? Also, providing the actual error message with your full SQL statement often helps a lot. — MatBailie, Jun 16 '15 at 11:59
This is so strange i am getting -9.4286 for all days ,do u have any idea how this is; — Reginwaldt Led, Jun 16 '15 at 12:00

score 0 · Answer 1 · edited Jun 16 '15 at 12:08

You were using wrong syntax for Conversion

I modified your Sql query as Below

select datediff(day, -7, CONVERT(DATETIME,'2015-06-30'))/7-datediff(day, -6,  CONVERT(DATETIME,'2015-05-28'))/7 AS MON,
datediff(day, -6, CONVERT(DATETIME,'2015-06-30'))/7-datediff(day, -5,CONVERT(DATETIME,'2015-05-28'))/7 AS TUE,
datediff(day, -5,CONVERT(DATETIME,'2015-06-30'))/7-datediff(day, -4, CONVERT(DATETIME,'2015-05-28'))/7 AS WED,
datediff(day, -4, CONVERT(DATETIME,'2015-06-30'))/7-datediff(day, -3,CONVERT(DATETIME,'2015-05-28'))/7 AS THU,
datediff(day, -3,CONVERT(DATETIME,'2015-06-30'))/7-datediff(day, -2,CONVERT(DATETIME,'2015-05-28'))/7 AS FRI,
datediff(day, -2, CONVERT(DATETIME,'2015-06-30'))/7-datediff(day, -1,CONVERT(DATETIME,'2015-05-28'))/7 AS SAT,
datediff(day, -1,CONVERT(DATETIME,'2015-06-30'))/7-datediff(day, 0, CONVERT(DATETIME,'2015-05-28'))/7 AS SUN

Thanks

How to get number of Modays,Tuesdays ,etc between two date

1 Answers1