SQL PHP HTML ASP JavaScript articles and free scripts to download
SQL Date query

Records of the weekdays

Please read the tutorial on last x day's record in part 1 of this tutorial. Here we will develop a query to get records of weekdays of the present week. To get the records we will try by using dayofweek function of MySQL.

This function dayofweek returns values 1 to 7 based on the weekday starting from Sunday as 1, Monday as 2 and …so on for others. So if today is Thursday then dayofweek function will return 5. So we need before three days record ( excluding today ) from today to get the records stating from Monday. So we will deduct 2 from the weekday figure. Here is the query to get the records of all weekdays of a week till today.

SELECT id,date,weekday, dayofweek(CURDATE()) as dayofweek FROM dt_weekday WHERE `date` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL (dayofweek(CURDATE())-2) DAY ) AND CURDATE( )

Above query will collect records starting from Monday and ending with today. For example if today is Tuesday then you will get two records ( of Monday and Tuesday )

For your understanding we have displayed dayofweek value against each record.

Using weekday function

By using weekday function we will get the same result but here weekday will return different number for days.

Monday = 0, Tuesday = 1 and so on……

Here is a simple query

SELECT WEEKDAY( '2014-08-15' )

Output of above query is 4 . (15th Aug 2014 is Friday).

Here is the query using weekday function to get records of the week starting from Monday.

SELECT id, DATE, weekday, WEEKDAY( CURDATE( ) ) AS weekday FROM dt_weekday WHERE `date` BETWEEN DATE_SUB( CURDATE( ) , INTERVAL( WEEKDAY( CURDATE( ) ) ) DAY ) AND CURDATE( )

Previous One week records

By adjusting the interval in our above query we can return the records of previous Week starting from Monday to Saturday.

SELECT id, DATE, weekday, DAYOFWEEK( CURDATE( ) ) AS dayofweek FROM dt_weekday WHERE `date` BETWEEN DATE_SUB( CURDATE( ) , INTERVAL (dayofweek(CURDATE())+5) DAY ) AND DATE_SUB( CURDATE( ) , INTERVAL (dayofweek(CURDATE())) DAY )

Previous Two week records

This query can be further adjusted to return previous two weeks record. ( Check the difference with previous query )

SELECT id, DATE, weekday, DAYOFWEEK( CURDATE( ) ) AS dayofweek FROM dt_weekday WHERE `date` BETWEEN DATE_SUB( CURDATE( ) , INTERVAL( DAYOFWEEK( CURDATE( ) ) +12 ) DAY ) AND DATE_SUB( CURDATE( ) , INTERVAL( DAYOFWEEK( CURDATE( ) ) +7 ) DAY )

Dynamic SQL Dump

Here is the sql dump to create table dt_weekday. This dump is dynamically created by considering previous 15 days and next 15 days starting from today. You will have one record for each day.

You should always take a fresh dump file from here for your testing, if you are not executing on same day.

One column weekday is kept to store name of the weekday ( sun, mon, tue … ) for your understanding.

CREATE TABLE IF NOT EXISTS `dt_weekday` (
`id` varchar(3) NOT NULL,
`date` date NOT NULL,
`weekday` varchar(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `dt_weekday` VALUES ('-15', '2014-09-05','Fri' );
INSERT INTO `dt_weekday` VALUES ('-14', '2014-09-04','Thu' );
INSERT INTO `dt_weekday` VALUES ('-13', '2014-09-03','Wed' );
INSERT INTO `dt_weekday` VALUES ('-12', '2014-09-02','Tue' );
INSERT INTO `dt_weekday` VALUES ('-11', '2014-09-01','Mon' );
INSERT INTO `dt_weekday` VALUES ('-10', '2014-08-31','Sun' );
INSERT INTO `dt_weekday` VALUES ('-9', '2014-08-30','Sat' );
INSERT INTO `dt_weekday` VALUES ('-8', '2014-08-29','Fri' );
INSERT INTO `dt_weekday` VALUES ('-7', '2014-08-28','Thu' );
INSERT INTO `dt_weekday` VALUES ('-6', '2014-08-27','Wed' );
INSERT INTO `dt_weekday` VALUES ('-5', '2014-08-26','Tue' );
INSERT INTO `dt_weekday` VALUES ('-4', '2014-08-25','Mon' );
INSERT INTO `dt_weekday` VALUES ('-3', '2014-08-24','Sun' );
INSERT INTO `dt_weekday` VALUES ('-2', '2014-08-23','Sat' );
INSERT INTO `dt_weekday` VALUES ('-1', '2014-08-22','Fri' );
INSERT INTO `dt_weekday` VALUES ('0', '2014-08-21','Thu' );
INSERT INTO `dt_weekday` VALUES ('1', '2014-08-20','Wed' );
INSERT INTO `dt_weekday` VALUES ('2', '2014-08-19','Tue' );
INSERT INTO `dt_weekday` VALUES ('3', '2014-08-18','Mon' );
INSERT INTO `dt_weekday` VALUES ('4', '2014-08-17','Sun' );
INSERT INTO `dt_weekday` VALUES ('5', '2014-08-16','Sat' );
INSERT INTO `dt_weekday` VALUES ('6', '2014-08-15','Fri' );
INSERT INTO `dt_weekday` VALUES ('7', '2014-08-14','Thu' );
INSERT INTO `dt_weekday` VALUES ('8', '2014-08-13','Wed' );
INSERT INTO `dt_weekday` VALUES ('9', '2014-08-12','Tue' );
INSERT INTO `dt_weekday` VALUES ('10', '2014-08-11','Mon' );
INSERT INTO `dt_weekday` VALUES ('11', '2014-08-10','Sun' );
INSERT INTO `dt_weekday` VALUES ('12', '2014-08-09','Sat' );
INSERT INTO `dt_weekday` VALUES ('13', '2014-08-08','Fri' );
INSERT INTO `dt_weekday` VALUES ('14', '2014-08-07','Thu' );
INSERT INTO `dt_weekday` VALUES ('15', '2014-08-06','Wed' );
INSERT INTO `dt_weekday` VALUES ('16', '2014-08-05','Tue' );


Be the first to post comment on this article :


Google+

Post Comment This is for short comments only. Use the forum for more discussions.
Name
Email( not to be displayed)Privacy Policy
1+2=This is to prevent automatic submission by spammers. Please enter the result of the sum as asked



Join Our Email List
Email:  
For Email Newsletters you can trust

HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2014 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer