SQL Exercise: Date Queries

1. Display all records of today's date
2. Display all records of yesterday
3. Display all records of tomorrow
4. Display all records of yesterday, today and tomorrow
5. Display all records of last 2 days ( no future dates )
6. Display all records of next 3 days ( no previous dates )
7. Display all records of Previous 10 days to previous 5 days
8. Display all records of next 5 days to next 10 days
9. Display all records of current month .
10. Display all records of current month and current year .
11. Display all records of previous month of current year
12. Display all records of Next month of current year
13. Display all records of previous, current and next month
14. Display all records of current month till today
15. Display all records of current year
16. Display all records of current week
17. Display all records of previous week
18. Display all records of Next week
19. Display all records of first week of the year
20. Display all records of working days ( Monday to Friday ) of current week of the year
21. Display all records of working days till today of current week of the year

• Video Tutorial on Date Query using CURDATE, BETWEEN , YEAR, MONTH, DAYOFWEEK()

More on Date

1. All records of present month
2. All records of last three months
3. All records within the range of last 2 and 4 months

Group by with Date field

1. Display Year and total number of payments done in that year
2. Display year ,total number of bills , sum of amount, average of amount against each year
3. Display month ,total number of bills , sum of amount, average of amount against each month
4. Modify Above query to show Year-Month in place of Year
5. Modify above query to show Year-Month-M1 and Year-Month-M2 format, here sale date from 1^st to 15^th of the month is M1 and from 16^th to rest of the days of the month is M2
6. Display total number of bills, sum of amount, average of amount against each financial year. Financial year starts from 1^st April and ends on 31^st March. It is displayed as 2014-2015
7. Modify the above query to show Financial year starting from 1^st October to 30^th September
8. Financial year starting from 1st July to 30th June

• Video Tutorial on GROUP BY on Date column Query

1. All records of last 24 hours ( including future records )
2. All records of last 24 hours till now.
3. All records from last 12 hours and 30 minutes
4. All records within the range of 13 to 14 hours ( lunch time )
5. All records within the range of 13 to 14 hours of one input day
6. All records between two input time in HOUR , Minute , Second
7. All records between two input time in HOUR , Minute , Second of a particular date
8. Number of records in each hour in all days
9. Number of records in each hour of a particular day
10. Number of records during Lunch hours ( from 13 to 14 hours ) on each day
11. Number of records during Lunch hours ( from 13 to 14 hours ) on a particular day
12. Find out the last record based on time for each day
13. Find out the first record based on time for each day

• Video Tutorial on NOW() with solutions