Examples of how to Calculate Different SQL Server Dates

[nicc777]

This is just a short note to show you the MySQL equiv. of what this article explained to do in MS SQL.

For all date functions, please refer to the MySQL Online Documentation.

First Day of Month

mysql> SELECT WEEKDAY( DATE_FORMAT( NOW(), '%Y-%m-01' ) ) AS dayofweek;
+-----------+
| dayofweek |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

The return result will be in a range between 0 and 6 ( 0=Monday, 1=Tuesday ... 6=Sunday )

Monday of the Current Week

I could not find a way (yet) for MySQL 3.x, but in MySQL 4 you could try the following:

mysql>  SELECT MAKEDATE( YEAR( NOW() ), ( ( WEEK( NOW(), 3 ) * 7 ) - 8 ) );

To explain this, check the following:

mysql> SELECT ( ( WEEK( '2003-09-18', 3 ) * 7 ) - 8 );
+-----------------------------------------+
| ( ( WEEK( '2003-09-18', 3 ) * 7 ) - 8 ) |
+-----------------------------------------+
|                                     258 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ( ( WEEK( '2003-09-23', 3 ) * 7 ) - 8 );
+-----------------------------------------+
| ( ( WEEK( '2003-09-23', 3 ) * 7 ) - 8 ) |
+-----------------------------------------+
|                                     265 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ( ( WEEK( '2003-09-27', 3 ) * 7 ) - 8 );
+-----------------------------------------+
| ( ( WEEK( '2003-09-27', 3 ) * 7 ) - 8 ) |
+-----------------------------------------+
|                                     265 |
+-----------------------------------------+
1 row in set (0.00 sec)

In each example, the result is the day of the year for the Monday of that week. So, by checking the MySQL documentation, we could use the MAKEDATE(year,dayofyear) function.

Could somebody verify this? I don't have a running 4x installation at this stage

First Day of the Year

This is a variation of the first problem:

mysql> SELECT WEEKDAY( DATE_FORMAT( NOW(), '%Y-01-01' ) ) AS dayofweek;
+-----------+
| dayofweek |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

First Day of the Quarter

I could not yet solve this one - any ideas??

Midnight for the Current Day

I'm not sure I understaood this one. I belief the following is the solution they were after:

mysql> SELECT DATE_FORMAT( NOW(), '%Y-%m-%d' ) AS date;
+------------+
| date       |
+------------+
| 2003-09-16 |
+------------+
1 row in set (0.00 sec)

Well, I'm tired now - will try to add some more later on. If anybody want's to, please add your own solutions.

Cheers

Nico

[nicc777]

Found these two interesting articles on the subject:

Doing Time with MySQL

AND

Changing times in MySQL

Cheers