Converting a string to datetime: MySQL: SELECT STR_TO_DATE ( ', '%d-%m-%Y' ); Oracle: Oracle TO_DATE and TO_TIMESTAMP functions can convert a string in the specified format. Note that the format specifiers are different (see mapping above). SELECT TO_DATE ( ', 'DD-MM-YYYY' ) FROM dual; SQL Server: SQL Server CONVERT function can convert a string to DATETIME, but instead of specifying format specifiers for date/time parts, you have to specify a style for the entire value (see mapping above): SELECT CONVERT ( DATETIME, ', 105 ); PostgreSQL: PostgreSQL provides TO_DATE and TO_TIMESTAMP functions to convert a string in the specified format to DATE or TIMESTAMP. The format specifiers are different from MySQL (see mapping above) but similar to Oracle: SELECT TO_DATE ( ', 'DD-MM-YYYY' ).

•, When invoked with the INTERVAL form of the second argument, is a synonym for. The related function is a synonym for. For information on the INTERVAL unit argument, see the discussion for. Mysql>SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY); ->'2008-02-02' mysql>SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY); ->'2008-02-02' When invoked with the days form of the second argument, MySQL treats it as an integer number of days to be added to expr. Mysql>SELECT ADDDATE('2008-01-02', 31); ->'2008-02-02' • adds expr2 to expr1 and returns the result.

Name & Description. 1, ADDDATE(). 2, ADDTIME(). 3, CONVERT_TZ(). Converts from one timezone to another. 4, CURDATE(). Returns the current date. 5, CURRENT_DATE(), CURRENT_DATE. Synonyms for CURDATE(). 6, CURRENT_TIME(), CURRENT_TIME. Synonyms for CURTIME(). For detailed information about timestamp, read this article How to use Date and Time data as integer value in PHP and MySQL. 4, CURDATE(), Returns the current date as a value in 'YYYY-MM-DD' format (See Practice #4-1) or YYYYMMDD format (See Practice #4-2), depending on whether the function is used in a string.

Expr1 is a time or datetime expression, and expr2 is a time expression. Mysql>SELECT ADDTIME('2007-12-31 23:99', '1 1:1:1.000002'); ->'2008-01-02 01:01' mysql>SELECT ADDTIME('01:99', '02:98'); ->'03:97' • converts a datetime value dt from the time zone given by from_tz to the time zone given by to_tz and returns the resulting value. Time zones are specified as described in. This function returns NULL if the arguments are invalid. If the value falls out of the supported range of the type when converted from from_tz to UTC, no conversion occurs. The range is described in.

Mysql>SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET'); ->'2004-01-01 13:00:00' mysql>SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00'); ->'2004-01-01 22:00:00'. Note To use named time zones such as 'MET' or 'Europe/Moscow', the time zone tables must be properly set up. See, for instructions.

• Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context. Mysql>SELECT CURDATE(); ->'2008-06-13' mysql>SELECT CURDATE() + 0; ->20080613 •, and are synonyms for.

•, and are synonyms for. •, and are synonyms for. • Returns the current time as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone. If the fsp argument is given to specify a fractional seconds precision from 0 to 6, the return value includes a fractional seconds part of that many digits. Mysql>SELECT CURTIME(); ->'23:50:26' mysql>SELECT CURTIME() + 0; ->200 • Extracts the date part of the date or datetime expression expr.

Mysql>SELECT DATE('2003-12-31 01:02:03'); ->'2003-12-31' • returns expr1 − expr2 expressed as a value in days from one date to the other. Expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation. Mysql>SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30'); ->1 mysql>SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31'); ->-31 •, These functions perform date arithmetic.

The date argument specifies the starting date or datetime value. Expr is an expression specifying the interval value to be added or subtracted from the starting date. Expr is a string; it may start with a - for negative intervals.

Unit is a keyword indicating the units in which the expression should be interpreted. The INTERVAL keyword and the unit specifier are not case sensitive.

The following table shows the expected form of the expr argument for each unit value. • if the first argument is a (or ) value, or if the first argument is a and the unit value uses HOURS, MINUTES, or SECONDS. • String otherwise. To ensure that the result is, you can use to convert the first argument to. MySQL permits any punctuation delimiter in the expr format. Those shown in the table are the suggested delimiters.

If the date argument is a value and your calculations involve only YEAR, MONTH, and DAY parts (that is, no time parts), the result is a value. Otherwise, the result is a value. Date arithmetic also can be performed using INTERVAL together with the or operator: date + INTERVAL expr unit date - INTERVAL expr unit INTERVAL expr unit is permitted on either side of the operator if the expression on the other side is a date or datetime value. For the operator, INTERVAL expr unit is permitted only on the right side, because it makes no sense to subtract a date or datetime value from an interval.

Specifier Description%a Abbreviated weekday name ( Sun. Sat)%b Abbreviated month name ( Jan. Dec)%c Month, numeric ( 0.

12)%D Day of the month with English suffix ( 0th, 1st, 2nd, 3rd, )%d Day of the month, numeric ( 00. 31)%e Day of the month, numeric ( 0. 31)%f Microseconds ( 000000. 999999)%H Hour ( 00. 23)%h Hour ( 01. 12)%I Hour ( 01.

12)%i Minutes, numeric ( 00. 59)%j Day of year ( 001. 366)%k Hour ( 0. 23)%l Hour ( 1. 12)%M Month name ( January. December)%m Month, numeric ( 00.

12)%p AM or PM%r Time, 12-hour ( hh:mm:ss followed by AM or PM)%S Seconds ( 00. Tujh Sang Preet Lagai Sajna Serial Song Free Download. 59)%s Seconds ( 00. 59)%T Time, 24-hour ( hh:mm:ss)%U Week ( 00. 53), where Sunday is the first day of the week; mode 0%u Week ( 00. 53), where Monday is the first day of the week; mode 1%V Week ( 01. 53), where Sunday is the first day of the week; mode 2; used with%X%v Week ( 01. 53), where Monday is the first day of the week; mode 3; used with%x%W Weekday name ( Sunday.

Saturday)%w Day of the week ( 0=Sunday. 6=Saturday)%X Year for the week where Sunday is the first day of the week, numeric, four digits; used with%V%x Year for the week, where Monday is the first day of the week, numeric, four digits; used with%v%Y Year, numeric, four digits%y Year, numeric (two digits)%% A literal% character% x x, for any “ x” not listed above Ranges for the month and day specifiers begin with zero due to the fact that MySQL permits the storing of incomplete dates such as '2014-00-00'. The language used for day and month names and abbreviations is controlled by the value of the system variable (). For the%U,%u,%V, and%v specifiers, see the description of the function for information about the mode values.

The mode affects how week numbering occurs. Returns a string with a character set and collation given by and so that it can return month and weekday names containing non-ASCII characters. Mysql>SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W%M%Y'); ->'Sunday October 2009' mysql>SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s'); ->'22:23:00' mysql>SELECT DATE_FORMAT('1900-10-04 22:23:00', ->'%D%y%a%d%m%b%j'); ->'4th 00 Thu 04 10 Oct 277' mysql>SELECT DATE_FORMAT('1997-10-04 22:23:00', ->'%H%k%I%r%T%S%w'); ->'22 22 10 10:23:00 PM 22:23:00 00 6' mysql>SELECT DATE_FORMAT('1999-01-01', '%X%V'); ->'1998 52' mysql>SELECT DATE_FORMAT('2006-06-00', '%d'); ->'00' • See the description for. • is a synonym for. • Returns the name of the weekday for date. The language used for the name is controlled by the value of the system variable ().

Mysql>SELECT DAYNAME('2007-02-03'); ->'Saturday' • Returns the day of the month for date, in the range 1 to 31, or 0 for dates such as '0000-00-00' or '2008-00-00' that have a zero day part. Mysql>SELECT DAYOFMONTH('2007-02-03'); ->3 • Returns the weekday index for date ( 1 = Sunday, 2 = Monday,, 7 = Saturday). These index values correspond to the ODBC standard. Mysql>SELECT DAYOFWEEK('2007-02-03'); ->7 • Returns the day of the year for date, in the range 1 to 366. Mysql>SELECT DAYOFYEAR('2007-02-03'); ->34 • The function uses the same kinds of unit specifiers as or, but extracts parts from the date rather than performing date arithmetic.

Mysql>SELECT EXTRACT(YEAR FROM '2009-07-02'); ->2009 mysql>SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03'); ->200907 mysql>SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 01:02:03'); ->20102 mysql>SELECT EXTRACT(MICROSECOND ->FROM '2003-01-02 10:23'); ->123 • Given a day number N, returns a value. Mysql>SELECT FROM_DAYS(730669); ->'2000-07-03' Use with caution on old dates. It is not intended for use with values that precede the advent of the Gregorian calendar (1582). •, Returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone. Unix_timestamp is an internal timestamp value such as is produced by the function.

If format is given, the result is formatted according to the format string, which is used the same way as listed in the entry for the function. Mysql>SELECT FROM_UNIXTIME(); ->'2015-11-13 10:08:01' mysql>SELECT FROM_UNIXTIME() + 0; ->0801 mysql>SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), ->'%Y%D%M%h:%i:%s%x'); ->'2015 13th November 10:' Note: If you use and to convert between values and Unix timestamp values, the conversion is lossy because the mapping is not one-to-one in both directions. For details, see the description of the function.

• Returns a format string. This function is useful in combination with the and the functions.

The possible values for the first and second arguments result in several possible format strings (for the specifiers used, see the table in the function description). ISO format refers to ISO 9075, not ISO 8601. Function Call Result '%m.%d.%Y' '%Y-%m-%d' '%Y-%m-%d' '%d.%m.%Y' '%Y%m%d' '%Y-%m-%d%H.%i.%s' '%Y-%m-%d%H:%i:%s' '%Y-%m-%d%H:%i:%s' '%Y-%m-%d%H.%i.%s' '%Y%m%d%H%i%s' '%h:%i:%s%p' '%H:%i:%s' '%H:%i:%s' '%H.%i.%s' '%H%i%s' can also be used as the first argument to, in which case the function returns the same values as for.

Mysql>SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR')); ->' mysql>SELECT STR_TO_DATE(',GET_FORMAT(DATE,'USA')); ->'2003-10-31' • Returns the hour for time. The range of the return value is 0 to 23 for time-of-day values. However, the range of values actually is much larger, so HOUR can return values greater than 23. Mysql>SELECT HOUR('10:05:03'); ->10 mysql>SELECT HOUR('272:59:59'); ->272 • Takes a date or datetime value and returns the corresponding value for the last day of the month. Returns NULL if the argument is invalid.

Mysql>SELECT LAST_DAY('2003-02-05'); ->'2003-02-28' mysql>SELECT LAST_DAY('2004-02-05'); ->'2004-02-29' mysql>SELECT LAST_DAY('2004-01-01 01:01:01'); ->'2004-01-31' mysql>SELECT LAST_DAY('2003-03-32'); ->NULL •, and are synonyms for. •, and are synonyms for. • Returns a date, given year and day-of-year values. Dayofyear must be greater than 0 or the result is NULL. Mysql>SELECT MAKEDATE(2011,31), MAKEDATE(2011,32); ->'2011-01-31', '2011-02-01' mysql>SELECT MAKEDATE(2011,365), MAKEDATE(2014,365); ->'2011-12-31', '2014-12-31' mysql>SELECT MAKEDATE(2011,0); ->NULL • Returns a time value calculated from the hour, minute, and second arguments. The second argument can have a fractional part. Mysql>SELECT MAKETIME(12,15,30); ->'12:15:30' • Returns the microseconds from the time or datetime expression expr as a number in the range from 0 to 999999.

Mysql>SELECT MICROSECOND('12:56'); ->123456 mysql>SELECT MICROSECOND('2009-12-31 23:10'); ->10 • Returns the minute for time, in the range 0 to 59. Mysql>SELECT MINUTE('2008-02-03 10:05:03'); ->5 • Returns the month for date, in the range 1 to 12 for January to December, or 0 for dates such as '0000-00-00' or '2008-00-00' that have a zero month part. Mysql>SELECT MONTH('2008-02-03'); ->2 • Returns the full name of the month for date. The language used for the name is controlled by the value of the system variable (). Mysql>SELECT MONTHNAME('2008-02-03'); ->'February' • Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.

If the fsp argument is given to specify a fractional seconds precision from 0 to 6, the return value includes a fractional seconds part of that many digits. Mysql>SELECT NOW(); ->'2007-12-15 23:50:26' mysql>SELECT NOW() + 0; ->5026.000000 returns a constant time that indicates the time at which the statement began to execute. (Within a stored function or trigger, returns the time at which the function or triggering statement began to execute.) This differs from the behavior for, which returns the exact time at which it executes.

Note You cannot use format '%X%V' to convert a year-week string to a date because the combination of a year and week does not uniquely identify a year and month if the week crosses a month boundary. To convert a year-week to a date, you should also specify the weekday: mysql>SELECT STR_TO_DATE('200442 Monday', '%X%V%W'); ->'2004-10-18' •, When invoked with the INTERVAL form of the second argument, is a synonym for. For information on the INTERVAL unit argument, see the discussion for. Mysql>SELECT DATE_SUB('2008-01-02', INTERVAL 31 DAY); ->'2007-12-02' mysql>SELECT SUBDATE('2008-01-02', INTERVAL 31 DAY); ->'2007-12-02' The second form enables the use of an integer value for days. In such cases, it is interpreted as the number of days to be subtracted from the date or datetime expression expr.

Mysql>SELECT SUBDATE('2008-01-02 12:00:00', 31); ->'2007-12-02 12:00:00' • returns expr1 − expr2 expressed as a value in the same format as expr1. Expr1 is a time or datetime expression, and expr2 is a time expression. Mysql>SELECT SUBTIME('2007-12-31 23:99','1 1:1:1.000002'); ->'2007-12-30 22:97' mysql>SELECT SUBTIME('01:99', '02:98'); ->'-00:99' • Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. If the fsp argument is given to specify a fractional seconds precision from 0 to 6, the return value includes a fractional seconds part of that many digits. Returns the time at which it executes. This differs from the behavior for, which returns a constant time that indicates the time at which the statement began to execute.

Note The order of the date or datetime arguments for this function is the opposite of that used with the function when invoked with 2 arguments. • This is used like the function, but the format string may contain format specifiers only for hours, minutes, seconds, and microseconds.

Other specifiers produce a NULL value or 0. If the time value contains an hour part that is greater than 23, the%H and%k hour format specifiers produce a value larger than the usual range of 0.23.

The other hour format specifiers produce the hour value modulo 12. Mysql>SELECT TIME_FORMAT('100:00:00', '%H%k%h%I%l'); ->'100 100 04 04 4' • Returns the time argument, converted to seconds. Mysql>SELECT TIME_TO_SEC('22:23:00'); ->80580 mysql>SELECT TIME_TO_SEC('00:39:38'); ->2378 • Given a date date, returns a day number (the number of days since year 0).

Mysql>SELECT TO_DAYS(950501); ->728779 mysql>SELECT TO_DAYS('2007-10-07'); ->733321 is not intended for use with values that precede the advent of the Gregorian calendar (1582), because it does not take into account the days that were lost when the calendar was changed. For dates before 1582 (and possibly a later year in other locales), results from this function are not reliable. See, for details. Remember that MySQL converts two-digit year values in dates to four-digit form using the rules in. For example, '2008-10-07' and '08-10-07' are seen as identical dates: mysql>SELECT TO_DAYS('2008-10-07'), TO_DAYS('08-10-07'); ->733687, 733687 In MySQL, the zero date is defined as '0000-00-00', even though this date is itself considered invalid.

Mode First day of week Range Week 1 is the first week 0 Sunday 0-53 with a Sunday in this year 1 Monday 0-53 with 4 or more days this year 2 Sunday 1-53 with a Sunday in this year 3 Monday 1-53 with 4 or more days this year 4 Sunday 0-53 with 4 or more days this year 5 Monday 0-53 with a Monday in this year 6 Sunday 1-53 with 4 or more days this year 7 Monday 1-53 with a Monday in this year For mode values with a meaning of “ with 4 or more days this year,” weeks are numbered according to ISO 8601:1988. • If the week containing January 1 has 4 or more days in the new year, it is week 1. • Otherwise, it is the last week of the previous year, and the next week is week 1. Mysql>SELECT WEEK('2008-02-20'); ->7 mysql>SELECT WEEK('2008-02-20',0); ->7 mysql>SELECT WEEK('2008-02-20',1); ->8 mysql>SELECT WEEK('2008-12-31',1); ->53 Note that if a date falls in the last week of the previous year, MySQL returns 0 if you do not use 2, 3, 6, or 7 as the optional mode argument: mysql>SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0); ->2000, 0 One might argue that should return 52 because the given date actually occurs in the 52nd week of 1999. Returns 0 instead so that the return value is “ the week number in the given year.” This makes use of the function reliable when combined with other functions that extract a date part from a date. If you prefer a result evaluated with respect to the year that contains the first day of the week for the given date, use 0, 2, 5, or 7 as the optional mode argument. Mysql>SELECT WEEK('2000-01-01',2); ->52 Alternatively, use the function: mysql>SELECT YEARWEEK('2000-01-01'); ->199952 mysql>SELECT MID(YEARWEEK('2000-01-01'),5,2); ->'52' • Returns the weekday index for date ( 0 = Monday, 1 = Tuesday, 6 = Sunday).

Mysql>SELECT WEEKDAY('2008-02-03 22:23:00'); ->6 mysql>SELECT WEEKDAY('2007-11-06'); ->1 • Returns the calendar week of the date as a number in the range from 1 to 53. Is a compatibility function that is equivalent to. Mysql>SELECT WEEKOFYEAR('2008-02-20'); ->8 • Returns the year for date, in the range 1000 to 9999, or 0 for the “ zero” date. Mysql>SELECT YEAR('1987-01-01'); ->1987 •, Returns year and week for a date. The year in the result may be different from the year in the date argument for the first and the last week of the year. The mode argument works exactly like the mode argument to.

For the single-argument syntax, a mode value of 0 is used. Unlike, the value of does not influence. Mysql>SELECT YEARWEEK('1987-01-01'); ->198652 Note that the week number is different from what the function would return ( 0) for optional arguments 0 or 1, as then returns the week in the context of the given year. If you're looking for generic SQL queries that will allow you to get the days, months, and years between any two given dates, you might consider using these. You just need to substitute date1 and date2 with your date expressions. NOTE: Some of these formulas are complex because they account for all cases where date1 date2.

Additionally, these formulas can be used in very generic queries where aliases and temporary variables are not allowed. Number of days between date1 and date2: TO_DAYS(date2) - TO_DAYS(date1) Number of months between date1 and date2: IF((((YEAR(date2) - 1) * 12 + MONTH(date2)) - ((YEAR(date1) - 1) * 12 + MONTH(date1))) >0, (((YEAR(date2) - 1) * 12 + MONTH(date2)) - ((YEAR(date1) - 1) * 12 + MONTH(date1))) - (MID(date2, 9, 2) 0, (YEAR(date2) - YEAR(date1)) - (MID(date2, 6, 5). Several times i have come to a followng date/time problem: In the table i am storing both date and time information in the datetime column. Querying, I want to receive COUNTed results grouped by date, and not date and time.

I came to the easy solution: SELECT DATE_FORMAT(postdate, '%Y-%m-%d') AS dd, COUNT(id) FROM MyTable GROUP BY dd; I suppose this solution to be quite slow (date formatting). Tf2 Item Hack No Survey No Password No Virus. Later, i 'upgraded' this query to use the string function: SELECT substring(postdate, 1,10) AS dd, COUNT(id) FROM MyTable GROUP BY dd; knowing, that the result is in the fixed format. Works faster. Comparing Dates when using MS Access and MyODBC If you are using MS Access and have created Access queries to substitute for views (which are not yet available in mySQL), you can use the following syntax ro perform date comparisons and avoid the dreaded 'ODBC -- call failed' error: Select * from [Task Effort Summary] Where ((Date() + 0) >CLng([Task Effort Summary].[s_end])) This particular example retuns tasks that are overdue (where todays date is past the scheduled end date). This query was developed for reports on a TUTOS database. Workaround for STR_TO_DATE pre version 4.1.1.

Ugly but it seems to work fine. Assumption: you know the format of the received date (in the below example the format is mm/dd/yy, m/d/yy, mm/dd/yyyy, etc) the statement extracts the year by locating the index of the second '/' and reading from the right of the string to that index. The index of the second is '/' is found by using LOCATE with the index of the first '/'. It extracts the day by locating the indeces of the first and second '/' and reading between them it extracts the month by locating the index of the first '/' and reading from the left of the string to that index.

It then CONCATs the year month and day pieces together separating them with hyphens. Lastly, it lets DATE_FORMAT do its magic on the string. (replace the test string '1/11/03' with your field name, etc) select DATE_FORMAT( CONCAT( RIGHT( '1/11/03', length( '1/11/03') - LOCATE('/', '1/11/03', LOCATE('/', '1/11/03' ) + 1 ) ), '-', LEFT( '1/11/03', LOCATE('/', '1/11/03' ) - 1 ), '-', SUBSTRING( '1/11/03', LOCATE('/', '1/11/03' ) + 1, LOCATE('/', '1/11/03', LOCATE('/', '1/11/03' ) + 1 ) - LOCATE('/', '1/11/03' ) - 1 ) ), '%Y-%m-%d' ). Lets say you have the mysql before 4.1.1 (where timediff() was implementet), and you want to do a timediff. I wanted to make a 'active users' on my page, but I found out that I didnt have the timediff function (to find persons which have been active within 5 minutes). So, I figured this query out: SELECT nick FROM `users` WHERE TO_DAYS( NOW( ) ) - TO_DAYS( last_login ).

Here is an example to convert various user inputs for a date field on an ASP page (VBScript) that will convert common formats (i.e., m/d/yy, mm/dd/yyyy, etc.) to MySQL database format of (yyyy-mm-dd). The function begins by establishing that there is a date in the field.

Then splits the date (converted to string) into three parts by locating '/'. DateArray(0), DateArray(1), DateArray(2) hold the month, day and year, respectively. These are then checked for the amount of digits, if there are not enough digits in month or day then a leading zero is added.

If there are only two digits on the year (ie '04') then a leading '20' is added. Function ConvertInputDate(varDate) If (Len(Trim(varDate)) >0) Then DateArray=Split(CStr(varDate),'/') IF Len(Trim(DateArray(0))). After reading numerous articles and posts regarding converting back and forth between SQL datetime and VBscript datetime, I opted for the simplest solution for my databases. I simply save all datetime values in varchar(20) fields and call on either MySQL or VBscript functions to get datetime values or check/convert datetime values. For example: currentDT = CStr(cn.execute('SELECT NOW()').Fields(0).Value) will fetch current datetime in the SQL server's datetime format and then convert it to a string. [Obviously, cn is set by Set cn = Server.CreateObject('ADODB.Connection') to create the database connection, then the database is opened with a cn.open (parameters).] You can then save this string to an appropriate field such as 'flddate_added' which is formatted as varchar(20). When retrieving the flddate_added value, you can use this VBscript code to check if the value is indeed a datetime value and convert it to the datetime format of the user's computer' if IsDate(flddate_added) then =CDate(flddate_added) ' convert to user's system format for display using user's codepage else =flddate_added ' just display the string end if The above methods allow me to get around all of the issues regarding VBscript's datetime display format differences depending on the system local.

Posted by Filip Wolak: >Several times i have come to a followng date/time problem: >In the table i am storing both date and time information in the datetime >column. Querying, I want to receive COUNTed results grouped by date, >and not date and time.

>SELECT substring(postdate, 1,10). If it's a DATETIME column than substring is not appropriate - it's logically nonsensical of course, and just happens to work in some version of MySQL because the DATETIME happens to be represented by a string in some contexts. Better would be to treat the DATETIME as a DATETIME rather than as a string, which will work in future versions of MYSQL and in other RDMS: SELECT DATE(postdate). Here is another VB/ASP function for converting Dates from standard to MySQL format. Cherise gave a nice example above, but it has extra complexity due to the use of arrays and also may be proned to user input errors. The following example will work based on the Localization settings of the server on which it is run. So it shouldn't care whether the date is dd-mm-yyyy, mm/dd/yy, mm/dd/yyyy, m-d-yy, etc.

Just make sure you pass it a date value that is formatted compliant to the server's localization. If necessary use VB's CDate(strDateValue) before passing strDateValue to the function. You can also easily modify this function to do the same for Time values, except you use Hour, Minute, and Second VB functions, and delimit with a colon (:) instead of a dash (-). Hope this helps! Function funcMySqlDate(dtmChangeDate) 'CONVERTS LOCALIZED DATE FORMAT (for example: m/d/yy) TO MySQL FORMAT (yyyy-mm-dd) Dim strTempYear, strTempMonth, strTempDay strTempYear = Year(dtmChangeDate) strTempMonth = Month(dtmChangeDate) strTempDay = Day(dtmChangeDate) if Len(strTempYear) = 2 then 'Y2K TEST - 1938-2037 - ADJUST AS NECESSARY if strTempYear >= 38 then strTempYear = '19' & strTempYear else strTempYear = '20' & strTempYear end if end if if strTempMonth. >Several times i have come to a followng date/time problem: >In the table i am storing both date and time information in the >datetime column.

Querying, I want to receive COUNTed results >grouped by date, and not date and time. I came to the easy >solution: I needed a query for a more general case to do time based reporting on arbitrary big 'slices' of timestamped data. My table has a column 'timestamp' which is of type 'datetime'. The following makes '120' second big slices select from_unixtime(unix_timestamp(timestamp) - unix_timestamp(timestamp)% 120) as slice. Group by slice.

I wanted to find the start date (Sunday) and the end date (Saturday) for any given week when all I had to go from is an arbitrary date (more precisely, the current date). The value returned by UNIX_TIMESTAMP(NOW()) can be quite unintuitive during the last hour of daylight-saving time in the fall, as it can return a timestamp that's an hour ahead of the current time. (The docs indicate that this may be 'fixed' from 4.1.3, but I have not tested.) This is because CST-related information is lost during the conversion by NOW() from the current time to a string. When presented a date string like '2004-10-31 01:52:37' which names a time that happened twice (once during daylight-saving time, and again an hour later in standard time), it doesn't know which you intend it to be interpreted as. The docs indicate that from 4.1.3, it uses the timezone in effect at the time of the SELECT, which implies that FROM_UNIXTIME('2004-10-31 01:52:37') returns a different value depending on whether you are currently under daylight-saving time or not. With 4.1.2 and before, it seems to always use standard time, and hence the one-hour 'error' (which is not really an error, but damn unintuitive that UNIX_TIMESTAMP(NOW()) does not return the UNIX_TIMESTAMP for now. Note that UNIX_TIMESTAMP() without args does return the proper unix timestamp for the current time.

If you have a table1, and (fields date which is varchar(100) you can also convert it as date type look the following example mysql>select str_to_date(date,'%d/%m/%Y') as Mydate from table1 order by Mydate DESC; +------------+ Mydate +------------+ 2004-12-16 2004-12-15 2004-12-02 2004-12-02 2004-11-01 2004-10-29 2004-10-12 2004-10-07 2004-09-12 2004-08-19 2004-08-13 2004-08-09 2004-08-04 2004-07-30 2004-07-26 2004-07-20 2004-07-16 2004-07-14 +------------+ 18 rows in set (0.00 sec) mysql. There doesn't appear to be an official way of selecting * from a table where eg 'date is january 2005'. So far i've found 8 different ways!! Where date like '2005-01-%' 2. Where DATE_FORMAT(date,'%Y-%m')='2005-01' 3. Where EXTRACT(YEAR_MONTH FROM date)='200501' 4.

Where YEAR(date)='2005' and MONTH(date)='1' 5. Where substring(date,1,7)='2005-01' 6. Where date between '2005-01-01' and '2005-01-31' 7. Where date >= '2005-01-01' and date. GENERATE missing days on a table with date gaps ===================================== If you want to bring visits per day to your site and you have a table wich is storing the hits, in a way similar to this.

+--------------+--------------------------+ date IP +--------------+--------------------------+ 2004-8-3 123.123.124.155 2004-8-3 123.123.124.145 2004-8-5 123.123.124.145 +--------------+--------------------------+ You may want to draw a chart and retrieve all the hits per day. The problem is that DAYS WITHOUT HITS WON'T APPEAR. And you won't be able to display the info of '0 hits'. One solution to this which is easy to code and clean, is to create and have in your database, a table named 'calendar' with all the days from today till some years from now (let's say, till 2034). The table should look something like this: +----------+ date +----------+ 2004-1-1 2004-1-2 2004-1-3 2004-1-4 2004-1-5 . etc. +---------+ Here is a piece of code which will make such table: Then all you have to do is perform a LEFT JOIN from this table and you've got every day from the period of time you specify. Even those with 0 hits SELECT calendar.date, count(*) FROM calendar LEFT JOIN visits ON calendar.date=visits.date GROUP BY calendar.date.

Age from date of birth compared whith in this function you can know the age of a person(it works for my). Preg 12 is a date in the format show bellow i dont know if it is fast. If you have a recent version you can asign curtime to a variable for get more performance else use php,c++ or another to save it as: YYYY-MM-DD example: 1997-03-31 left((curtime()-preg12),(CHAR_LENGTH(curtime()-preg12)-4)) another way is: (TO_DAYS('a - date') - TO_DAYS('birth'))/365 you can replece the curdate for a before date changing curdate to this 20000619 NOT THIS: 2000-06-19 if you have beter way send it to my tanks bye.

The description of FROM_DAYS(N) - 'Given a daynumber N, returns a DATE value' - uses the term 'daynumber' without explaining it. The description of TO_DAYS(date) - 'Given a date date, returns a daynumber (the number of days since year 0)' - lower down the page at least tries to explain the term, but unsuccessfully. There are two problems here. Firstly, there was no year 0 in the Gregorian calendar. Secondly, a number of days has to be counted from a day, not a year. Do they mean the beginning of the (non-existent) year, or the end of the (non-existent) year? Do non-existent years even have beginnings and ends?

Someone should amend these descriptions. On transactional consistency.Concerning the functions which use the real current time, such as NOW(), the manual says 'Functions that return the current date or time each are evaluated only once per query at the start of query execution.'

Note though that this does not apply across entire transactions, as you may expect. Thus a transaction like: START TRANSACTION; INSERT INTO EVENTS VALUES (NOW(), 'A'); INSERT INTO EVENTS VALUES (NOW(), 'B'); COMMIT; will result in potentially two different times being recorded for the two records. If you need the type to be dynamically taken from a table (that is where you have 'year', 'day', 'month' etc as a column in the table), here is the best way I could work out to do it. Expand as necessary: SELECT set_date, unit_period, unit_multiplier, CASE WHEN unit_period = 'month' THEN DATE_SUB(set_date, INTERVAL unit_multiplier MONTH) WHEN unit_period = 'week' THEN DATE_SUB(set_date, INTERVAL (unit_multiplier * 7) DAY) WHEN unit_period = 'year' THEN DATE_SUB(set_date, INTERVAL unit_multiplier YEAR) ELSE DATE_SUB(set_date, INTERVAL unit_multiplier DAY) END FROM dates_table. Returns all rows from actual month to given @months. I was using mysql v4 and the date was in a varchar data type, in order to change the data type in mysql v5 i use the following code: update ssd_escondida.tactual_sag4 set ssd_escondida.tactual_sag4.Fecha=str_to_date(ssd_escondida.tactual_sag4.Fecha2,'%e/%m/%Y'); where: ssd_escondida: database tactual_sag4:is a table Fecha: is a date type Fecha2:is a varchar which contains a date, but is from to (with a zero at the begining) why i used%e instead of%d??? The answer is very simple, there is a problem with de help about str_to_date:%d: represents the days, but from 0 to 31 and.%e: represents the days, but from 00 to 31.

That's the reason why we cannot use: str_to_date(',%d/%m/%Y), we must use str_to_date(','%e/%m/%Y') Another way in order to change a string like: to a date is to use: str_to_date(','0%d/%m/%Y'). In MySQL 4.0, and possibly others, UNIX_TIMESTAMP() doesn't work with dates before 1970. This query does the same, and works with any date from from Fri, 13 Dec 1901 20:45:54 to Tue, 19 Jan 2038 03:14:07. 'date' is the name of the DATETIME column you need a timestamp of. SELECT (((TO_DAYS(date) * 86400) + TIME_TO_SEC(date)) - (TO_DAYS('1970-01-01') * 86400)) AS timestamp If you're using PHP, note that date() accounts for DST and thus may appear to return incorrect results; also, don't forget to escape the quotes around 1970-01-01.

Keyphrases: Birthday reminder, select dates between This might be useful. If you have a database containing 'name' and 'birthday' (as columns) then the following query will list the birthdays in the next 15 days. (16 to be more precise:-)) What I found unique about this problem is that the YEAR (of birth) will always be different and hence one cannot simply use a query like: SELECT * FROM `friends` WHERE `birthday` >= CURDATE() AND `birthday` = DAYOFMONTH(CURDATE()) AND DAYOFMONTH(`birthday`). The birthday-reminder doesn't work the way it should be. I found the bug and fixed it.

This is a working example: SELECT user_birthdate,user_name,user_id, EXTRACT(MONTH FROM `user_birthdate` ) month, EXTRACT(DAY FROM `user_birthdate` ) day FROM '.$db_prefix.' Users WHERE ( EXTRACT(MONTH FROM `user_birthdate` ) = EXTRACT(MONTH FROM CURDATE()) AND DAYOFMONTH(`user_birthdate`) >DAYOFMONTH(CURDATE()) AND DAYOFMONTH(`user_birthdate`) EXTRACT(MONTH FROM CURDATE()) AND EXTRACT(MONTH FROM `user_birthdate`) = EXTRACT(MONTH FROM ADDDATE(CURDATE(), INTERVAL 15 DAY)) AND DAYOFMONTH(`user_birthdate`). It took me a bit of time to find how to select data based on time periods (such as for quarterly or yearly reports). You can use group by month(DateTypeColumn). Example- to find periodic totals: SELECT [Year Quarter Month Day](date) as Period,shipcountry,shipstate,shipcity,sum(products),sum(shipping),sum(tax) FROM products NATURAL JOIN shipping NATURAL JOIN tax GROUP BY Period,shipcountry,shipstate,shipcity more here- I suppose you could alter the start of quarterly periods by doing some arithmetic on the (date), but you might have to do some conversions.

Time arithmetic using CURTIME() is quite willing to type everything into integers rather than adding and subtracting seconds. For example, where log_time is a TIME column; SELECT log_time AS Time FROM call_log WHERE log_time >= (CURTIME( ) - 60 ); will fetch all results from the last 60 seconds. However, SELECT log_time AS Time FROM call_log WHERE log_time >= (CURTIME( ) - 900 ); will fetch all results from the last 9 minutes. 900 is interpreted, not as 900 seconds (15 minutes), but as 9:00. An hour is 10000 (1:00:00), not 3600 (36:00). If you want to add seconds, use something like the following (for the last hour); SELECT log_time AS Time FROM call_log WHERE log_time >= (CURTIME( ) - SEC_TO_TIME(3600) ). In reply to 'David Berry on September 17 2004 9:08pm' Problem: To find week start and end date with user specified start of the week day and user specified date for which the week is to be found.

David's solution does not work with user specified week start and end. It only works with normal week which is 1 and 7 as start and end correspondingly. As I needed different starting day for week than Sunday or Monday for timesheet calculations, I had to come up with working solution. Date_sub(t.date, interval if(dayofweek(t.date)-$weekStartingDay >= 0, dayofweek(t.date)-$weekStartingDay, dayofweek(t.date)-$weekStartingDay+7) day) week_start. Date_sub(t.date, interval if(dayofweek(t.date)-$weekStartingDay >= 0, dayofweek(t.date)-$weekStartingDay, dayofweek(t.date)-$weekStartingDay+7) - 6 day) week_end. This solution works fine for me, at least at the moment till I find some bug in it:). Just another example on how to figure out how many days are until some birthdate (in order to do a range query, or get the 'next' birthday): SELECT name, birthday, IF(DAYOFYEAR(birthday) >= DAYOFYEAR(NOW()), DAYOFYEAR(birthday) - DAYOFYEAR(NOW()), DAYOFYEAR(birthday) - DAYOFYEAR(NOW()) + DAYOFYEAR(CONCAT(YEAR(NOW()),'-12-31'))) AS distance FROM birthdates; The + DAYOFYEAR(CONCAT(YEAR(NOW()),'-12-31')) (which is 366 or 365, depending on whether we're in a leap year or not) takes care of the New Year's Eve wrap around.

You could add WHERE distance. Here's what I used to get a summary of some value by day of the week: select date_format(date, '%W') AS `Day of the week`, sum(cost) from daily_cost group by `Day of the week` order by date_format(date, '%w') Output: +-----------------+-----------+ Day of the week sum(cost) +-----------------+-----------+ Sunday 271.53 Monday 310.95 Tuesday 323.6 Wednesday 312.45 Thursday 301.76 Friday 294.76 Saturday 255.83 +-----------------+-----------+ To order results starting with Monday, change the 'order by' expression to order by (date_format(date, '%w') - 7)% 7. I made a Stored Function which can covert an ISO 8601 (2006-07-05T13:30:00+02:00) date to a UNIX TIMESTAMP of the corresponding UTC or GMT datetime, so you can compare timestamps from different timezones with eachother. Hope this can help someone. CREATE FUNCTION ISO8601TOUNIXTIMESTAMP (iso varchar(25)) RETURNS INTEGER(15) DETERMINISTIC BEGIN DECLARE CONVTIME INTEGER(11); SET CONVTIME = (SUBSTRING(iso,21,2) * 60) + SUBSTRING(iso,24,2); IF SUBSTRING(iso,20,1) = '+' THEN SET CONVTIME = 0 - CONVTIME; END IF; RETURN UNIX_TIMESTAMP(DATE_ADD(STR_TO_DATE(CONCAT(SUBSTRING(iso,1,10),' ',SUBSTRING(iso,12,8)),'%Y-%m-%d%H:%i:%s'), INTERVAL CONVTIME MINUTE)); END.

To get the first day of the current month: SELECT ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),0)*100)+1) as FirstDayOfTheMonth; This will give you the first day of the month. Mysql>SELECT ((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),0)*100)+1) as FirstDayOfTheMonth; +--------------------+ FirstDayOfTheMonth +--------------------+ 20060701 +--------------------+ 1 row in set To get the last day of the current month: SELECT (SUBDATE(ADDDATE(CURDATE(),INTERVAL 1 MONTH),INTERVAL DAYOFMONTH(CURDATE())DAY)) AS LastDayOfTheMonth; This will give you the first day of the month.

Mysql>SELECT (SUBDATE(ADDDATE(CURDATE(),INTERVAL 1 MONTH),INTERVAL DAYOFMONTH(CURDATE())DAY)) AS LastDayOfTheMonth; +-------------------+ LastDayOfTheMonth +-------------------+ 2006-07-31 +-------------------+ 1 row in set Hope this helps! I'm not sure why Horst Schirmeier did that very complex birthdate equation. Seems to me you could just do: SET @DOYNOW = DAYOFYEAR(CURDATE()); SELECT (DAYOFYEAR(birthdate) - @DOYNOW) AS birthdays, birthdate, @DOYNOW, CURDATE() FROM users WHERE birthdate IS NOT NULL; then if birthdays == 0, it's that persons birthday, otherwise you know if the birthday is in the future by how many days, or if you missed it and how many beers you owe them.

(although the missed/negative days seems to be off) +-----------+------------+---------+------------+ birthdays birthdate @DOYNOW CURDATE() +-----------+------------+---------+------------+ 83 1969-10-26 216 2006-08-04 3 1981-08-07 216 2006-08-04 -1 1972-08-02 216 2006-08-04 0 1946-08-04 216 2006-08-04 -151 1976-03-05 216 2006-08-04 +-----------+------------+---------+------------+ Shouldn't that -1 be -2? Am I missing something obvious?

If I do 'SELECT DATEDIFF('2006-08-01', CURDATE());' I get -2 as I expect. SERIAL DATES ------------ to convert dates stored as a double (Dateserial as used by microsoft etc i.e. The whole number is the number of days since either or, the fraction being the proportion of 1 day) into a dd/mm/yyyy hh:mm format: note - MySQL requires the date taken from, and even then the addition of number of days is still out by 1 because MySQL like Excel and other programs incorrectly assumes that the year 1900 was a leap year, when it wasn't for some reason. The SQL to get the correct date is:- ADDDATE(ADDDATE('1899-12-31 00:00',), INTERVAL -1 DAY) The fraction of the time can be multiplied by the number of seconds in a day (84,600) and then added to the date as a number of seconds to get the time as well, so for a datetime the SQL is:- ADDDATE(ADDDATE(ADDDATE('1899-12-31 00:00',), INTERVAL -1 DAY), INTERVAL (MOD(,1) * 86400) SECOND) Sorry is this is a bit obvious, it just took me a while to find all this out. Hope it helps. I created this function to calculate 'working day' difference of two dates.

If you have table with list of holidays you may uncomment part in this function to exclude days of holidays also. If you want to select the time difference between two datetime columns and the fields may contain datetimes that are on different days, you can use the following if statement: SELECT IF(TIME_TO_SEC(last_date)>=TIME_TO_SEC(first_date), TIME_TO_SEC(last_date)-TIME_TO_SEC(first_date), 86400+(TIME_TO_SEC(last_date)-TIME_TO_SEC(first_date))) FROM table; This will return the time between the last_date and the first date, taking into account the values where first_date and last_date are on different days.

As mentioned above STR_TO_DATE() is available as of MySQL 4.1.1. This function can be useful if you are grouping rows by Week of Year and then want to produce a table with 'Week Commencing' as the points on your X-axis. So what do you do if you're codeshop is using pre-4.1? Here's what I did.

I have a table of events happening on a datetime. I wanted an event count by week with the date of the start of the week, assuming the week starts Monday. I have exploited the group by function to extract the minimum datetime value which in my case is guaranteed to be at least once daily. This will not work if your data is not being injected daily! Select count(*) as 'count', date_format(min(added_on), '%Y-%M-%d') as 'week commencing', date_format(added_on, '%Y%u') as 'week' from system where added_on >= '2007-05-16' group by week order by 3 desc; +-------+-----------------+--------+ count week commencing week +-------+-----------------+--------+ 88 2007-June-04 200723 276 2007-May-28 200722 275 2007-May-21 200721 160 2007-May-16 200720 +-------+-----------------+--------+ Hope thats useful for someone!

Imran Chaudhry. Due to a bug in mysql versions prior to 5.0.36, there is a problem when performing multiple SEC_TO_TIME conversions and there are intermediate null values. It will turn the results after the first null into null values. To display the date of the monday preceding a given day, Bryan Donovan suggested the following: >SELECT DISTINCT(STR_TO_DATE(CONCAT(YEARWEEK(starttime),'1'),'%x%v%w')) >FROM test_events; In fact, you need to be consistent in the type of weeks you use. The above would tell you that the Monday July 16 2007 is part of the week starting. Monday July 9 2007! This comes from week definition ambiguities (see WEEK() above).

ANNIVERSARIES ARE TRICKY! ------------------------- For versions of MySQL previous to 4.1 it is quite difficult to determine if a date field's anniversary date falls within a specified date range. Day-of-year calculations fail because of leap years and the possibility that the date range you have specified spans a year boundary.

Here is what I have come up with. Hopefully it can save someone the headache and Google Fever I had to go through to come up with it.

** SOME SELECT STATEMENT.** WHERE ((month(Date) BETWEEN month('[MyStartDate]') AND month('[MyEndDate]') AND month('[MyStartDate]') = dayofmonth('[MyStartDate]') AND month(Date)=month('[MyStartDate]') OR dayofmonth(Date) month('[MyStartDate]') AND month(date) month('[MyEndDate]') AND (dayofmonth(Date) >= dayofmonth('[MyStartDate]') AND month(Date)=month('[MyStartDate]') OR dayofmonth(Date) month('[MyStartDate]') OR month(Date). I was looking for a solution where I could return the number of days, hours, Minutes and seconds between two entries in a table.

DATE_DIFF is not running on my mysql server as my provider uses mysql version 4.0.25 Solution was to use to days and std time functions to calculate the difference in one call. The fields stored in the table(report_table) are time(00:00:00), date(0000-00-00) and record(enum) which tells the app the type of log stored.

EG start or end of a report. SELECT (TO_DAYS( `end`.`date` ) - TO_DAYS( `start`.`date` )) - ( second( `end`.`time` ) + (minute( `end`.`time` )*60) + (hour( `end`.`time` )*3600). Birthday reminder The next birthday (including today!) is when the person is 1 year older than he/she was yesterday. Finding a date before a given number of days Often, for certain applications, we need to subtract some days from a given date to find another date. For example, in a library, we need to go 21 days behind from the current date and list the books that were taken before that date.

This would be the overdue list. Here is a simple SQL statement. This type of use is practically essential to most apps. SELECT SUBDATE( '2007-12-12', INTERVAL 3 DAY ); The line above will give the answer 2007-12-9.

To explain it further, the function SUBDATE returns a date after subtracting a specified duration. In our example, the duration is 3 days. To indicate that we are dealing with DAYS, we use the term INTERVAL.

So the function above can be explained as “what is the date, three days before today?” Now if you want to find the date 20 days before TODAY or the current system date, this is what you should do: SELECT SUBDATE( CURRENT_DATE, INTERVAL 20 DAY ) In the statement above, we are using one of the MySQL constants that hold the current date on the server. We count 20 days back and get the answer as a result. Happy coding. Khalid (itsols). IF you need to run a monthly report from the 1st of each month with the previous month date, you could use the case statement below. For MySQL 4.1 you can use this query to select the month-difference between two dates, which also takes into account the amount of days of the starting month and ending month. The first part is the period_diff function to get a total amount of months between two dates.

Then 1 month is subtracted (because of the next two parts). The second part is to calculate the part of the starting-month (results in 1 month for dates starting on the first of the month, like ) 3. The third part is to calculate the part of the ending-month (results in 0 for dates starting on the first of the month, like ) PERIOD_DIFF(DATE_FORMAT(`end_date`,'%Y%m'),DATE_FORMAT(`start_date`,'%Y%m')) -1 + (TO_DAYS(LAST_DAY(`start_date`)) + 1 - TO_DAYS(DATE_FORMAT(`start_date`,'%Y-%m-%d')))/(TO_DAYS(LAST_DAY(`start_date`)) + 1 - to_days(DATE_FORMAT(`start_date`,'%Y-%m-01'))) + (DAY(`end_date`)-1)/(TO_DAYS(LAST_DAY(`end_date`)) + 1 - TO_DAYS(DATE_FORMAT(`end_date`,'%Y-%m-01'))). I work for a publishing company and recently had a request to show the modification date/time for an article as either minutes ago, hours ago, yesterday, or date of last modification depending on how recently the article was last modified.

Here's a copy of the case statement I used for this: CASE WHEN modified_date between date_sub(now(), INTERVAL 60 minute) and now() THEN concat(minute(TIMEDIFF(now(), modified_date)), ' minutes ago') WHEN datediff(now(), modified_date) = 1 THEN 'Yesterday' WHEN modified_date between date_sub(now(), INTERVAL 24 hour) and now() THEN concat(hour(TIMEDIFF(NOW(), modified_date)), ' hours ago') ELSE date_format(modified_date, '%a,%m/%d/%y') END Note that the 'yesterday' check occurs before the 'hours' check. This is intentional so that hours will only be shown if the modification occurred during the current day. Hope this is useful for someone else! Hi, here is a MySQL(5) function to validate a date, in format YYYYMMDD (example:'select isDate(20080229)'). This function include 'leap year' validation. I try to simulate 'isdate()' function of Sql Server.

I've always find it useful to be able to find out the number of records created on a monthly basis. This SELECT statement does the job by formatting a date field using DATE_FORMAT() and group it to see data by the year and month. SELECT DATE_FORMAT(creationDate, '%Y-%m') AS month, COUNT(*) AS total FROM myTable GROUP BY month ORDER BY total DESC LIMIT 5; +---------+-------+ month total +---------+-------+ 2006-07 485 2006-08 179 2008-10 96 2008-06 89 2008-01 84 +---------+-------+ 5 rows in set (0.00 sec) The ORDER BY and LIMIT is optional, to further filter the select statement. Regards, Alex.

To find future date in X business days, took me a while but here it is without using store procedures, instead I compute the number of weekends between the initial date and date + X days, adjust for the weekend days. Its implemented as a derived table to easy joins with real tables. To expand a bit on the note by Jeffrey Friedl on October 31 2004 9:05am: The manual states that FROM_UNIXTIME(UNIX_TIMESTAMP(.)) doesn't map back to the same formatted date. What also should be stated is that the reverse is not true either. UNIX_TIMESTAMP(ts) where ts is a TIMESTAMP column returns the unix timestamp stored in it without conversion to/from a formatted date string. That avoids trouble with timezones and DST overlaps etc. However, when the column ts is set through FROM_UNIXTIME(n), it (apparently) formats the unix timestamp as a string which is then parsed back to a timestamp again.

The formatting and the parsing is done with the same time zone, so the timezone offsets generally cancel themselves out. The exception is if the timezone uses DST and the timestamp is in the overlapping hour in the fall when going from summer time to normal time. If the active time zone on the connection is Central European Time, which uses DST, then setting (Sun 30 Oct 2005 2:00:00 CEST) through INSERT INTO foo SET ts = FROM_UNIXTIME() actually sets the ts column to (Sun 30 Oct 2005 2:00:00 CET), i.e one hour later. The only way around that problem is apparently to ensure that the time zone used on the connection is one which doesn't use DST. UTC is a reasonable choice, which can be set on the connection through 'SET time_zone = '+00:00'.

I implemented a well known algorithm from an English standards organisation (I've forgotten exactly who). For the calculate easter sunday i used following function: DELIMITER $$ USE `pczeitdb`$$ DROP FUNCTION IF EXISTS `fneastern`$$ CREATE DEFINER=`wkroot`@`%` FUNCTION `fneastern`(iYear INT) RETURNS DATE DETERMINISTIC BEGIN SET @iD=0,@iE=0,@iQ=0,@iMonth=0,@iDay=0; SET @iD = 255 - 11 * (iYear% 19); SET @iD = IF (@iD >50,(@iD-21)% 30 + 21,@iD); SET @iD = @iD - IF(@iD >48, 1,0); SET @iE = (iYear + FLOOR(iYear/4) + @iD + 1)% 7; SET @iQ = @iD + 7 - @iE; IF @iQ. Much appreciation to Steven Copley's post concerning weekdays [since that is what I was looking for] but I think he used the dayofweek() function where he meant to use the weekday() function in the LEAST() clause. This will give week days (everything minus weekends) between 2 dates: SELECT (floor( datediff( '2009-01-11', '2009-01-01' ) /7 ) *5) + CASE dayofweek('2009-01-01') WHEN 1 THEN mod(datediff( '2009-01-11', '2009-01-01' ), 7) - 2 WHEN 7 THEN mod(datediff( '2009-01-11', '2009-01-01' ), 7) - 1 ELSE LEAST(7-weekday( '2009-01-01' ), mod(datediff( '2009-01-11', '2009-01-01' ), 7)) END. I personally got stuck, trying to feed VBScript dates back into MySQL; as the ODBC Driver converts the MySQL dates to VBScript's version automatically. This is a one-liner solution for everyone using VBScript (ASP), that needs to feed a VB date into MySQL. Input: REQmod in any date format that VBScript understands (1/1/1970 12:00:01 AM) REQmod = year(REQmod) & '-' & right('0' & month(REQmod),2) & '-' & right('0' & day(REQmod),2) & ' ' & right('0' & Hour(REQmod),2) & ':' & right('0' & Minute(REQmod),2) & ':' & right('0' & Second(REQmod),2) Output: REQmod will be strictly in MySQL Format (1970-01-01 00:00:01).

Two things to keep in mind when using the TIMESTAMPDIFF function: 1. The function returns a truncated integer of the specified unit. It does NOT use the unit to determine the precision of the difference calculation. For example, you might expect SELECT TIMESTAMPDIFF(MONTH,'2010-03-31','2010-04-30'); to return 1, as the difference between '2010-03' and '2010-04'. However, because the difference (including the days) is not quite a full month, it returns 0, not 1. In version 5.0, the unit you specify determines whether the calculation includes the time elements.

When using the YEAR or MONTH units, the function uses the date elements (year, month and day) in the comparison, but ignores the time elements. For example, SELECT TIMESTAMPDIFF(MONTH,'2010-03-01 00:00:00','2010-04-01 00:00:00'); and SELECT TIMESTAMPDIFF(MONTH,'2010-03-01 23:59:59','2010-04-01 00:00:00'); both return 1, while SELECT TIMESTAMPDIFF(MONTH,'2010-03-02 00:00:00','2010-04-01 00:00:00'); returns 0. However, if you use DAY as the unit, all date and time elements are included in the calculation. Thus, SELECT TIMESTAMPDIFF(DAY,'2010-03-29 00:00:00','2010-03-30 00:00:00'); returns 1, while SELECT TIMESTAMPDIFF(DAY,'2010-03-29 00:00:01','2010-03-30 00:00:00'); returns 0. In version 5.1, the function includes the time elements in all calculations. (My testing was done in versions 5.0.75 and 5.1.30) This function works fine for calculations on the time part of a timestamp; but for working with the date part, I much prefer using the YEAR, PERIOD_DIFF, or TO_DAYS functions. If you have a VARCHAR column called, for example, `Date` that contains date data, but is not formatted as a MySQL DATE column, you can convert it using MySQL's STR_TO_DATE function.

For instance, Excel stores can store dates like this 1/31/2010. MySQL uses this, 2010-01-31. Create a column of type DATE called `MySQLDate` Step 2.

UPDATE `table` SET MySQLDate = (SELECT STR_TO_DATE(`Date`,'%c/%e/%Y')); Also here is another tip. There is a LAST_DAY() function but no FIRST_DAY() function.

This has the same effect of FIRST_DAY: it subtracts a month, find last day, and then add 1 day. UPDATE `table` SET start_date = DATE_ADD(LAST_DAY(DATE_SUB(start_date, INTERVAL 1 MONTH)), INTERVAL 1 DAY). CREATE FUNCTION `workdayadd`(a date, days int) RETURNS date DETERMINISTIC COMMENT 'add workdays to date a' BEGIN DECLARE freedays int; DECLARE daysadd, totaldays, workday, di INT; declare dateinit date; set dateinit=a; SET workday=0; SET di=DAYOFWEEK(dateinit); set totaldays = 0; WHILE workday1 and di0 do SELECT count(*) INTO freedays FROM holliday WHERE d_day BETWEEN dateinit AND date_add(dateinit,interval daysadd-1 day) AND WEEKDAY(d_day)0 then SET workday=0; set dateinit=date_add(dateinit,interval daysadd day); SET di=DAYOFWEEK(dateinit); WHILE workday1 and di. Further to Santi Bari's excellent suggestion to 'plug the missing gaps', remember that if you have additional clauses in your retrieval, you may have to include a little extra in your syntax. For example, following on from Santi's example, suppose you had a field in the visits table for capturing the browser used. To retrieve the data where the browser was Firefox, you would add a WHERE clause, like: WHERE visits.browser = 'Firefox' However, this will not work as expected.

To plug the holes in the dates, the right syntax would be: WHERE (visits.browser = 'Firefox' OR visits.browser IS NULL) and the gaps in the dates will be filled again! Using function from Martin Minka posted here on January 25 2007 5:23pm in this example we calculate effective working minutes elapsed between creation and completion of ServiceDesk request. I hope it may be useful. SELECT `created_at`, `finished_at`, CASE mysql.workdaydiff( `finished_at`, `created_at`) WHEN 1 THEN TIMESTAMPDIFF( MINUTE, `created_at`, `finished_at` ) ELSE ( mysql.workdaydiff(`finished_at`, `created_at`) -2 ) *60 *9 + TIMESTAMPDIFF( MINUTE, `created_at`, DATE_ADD( DATE( `created_at` ), INTERVAL 19 HOUR ) ) + TIMESTAMPDIFF( MINUTE, DATE_ADD( DATE( `finished_at` ), INTERVAL 10 HOUR ), `finished_at` ) END AS working_time_used_to_finish_request FROM `request` WHERE `request_status_id` IN ( 4, 5 ).

When you need to get the timestamp of a date in a certain timezone or GMT time, use this: select UNIX_TIMESTAMP( CONVERT_TZ( '2012-05-31 23:59:59', '-03:00', '+00:00') ) + TIMESTAMPDIFF(second,utc_timestamp(), now()) as time; This will return the UTC timestamp for the datetime in Argentina at 23:59:59, GMT-3. A generic way of getting the UTC time of a certain LOCAL DATE TIME is: select UNIX_TIMESTAMP( CONVERT_TZ( '2012-05-31 23:59:59', substring( replace( concat('+', SEC_TO_TIME( TIMESTAMPDIFF( second,utc_timestamp(), now() ) ) ),'+-','-'),1,6), '+00:00') ) + TIMESTAMPDIFF(second,utc_timestamp(), now()) as time. Just aggregating some of what I found on this page in a single query.

Calculating age in years from date of birth accurately using datediff() doesn't work due to leap years. If you calcualte a person's age on a date near their birthday, you're likely to get an incorrect result. The older the subject is, the more likely you will get an incorrect result due to the number of leap years that has occurred in a person's life.

For example, a person who is 100 has lived through about 25 leap years. Calculating age is a simple problem. It boils down to subtracting year of birth from the current year (or whatever year you're calculating age at) and then subtractiing 1 if the subject's birth date is after the the date of the calculation in the calendar year of the date of calculation. For example, if the subject was born on June 15th, 1954 and you're calculating their age on July 1st, 1984, then their birthday has already occurred in the calendar year of the date of calculation and the correct answer is 1984 - 1954 = 30. That calculation works if the subject's birth date is on or before the date of calculation.

However, if that person were born on August 1st, 1954, then their birthday occurs later in the calendar year than the date of calculation. In that case, the person is still only 29 on July 1st which means that the correct caclulation is 1984 - 1954 - 1 = 29. To calculate age in years accurately, you need a calculation which simply subtracts the year of the date of birth from the year of the date of calculation and then adds '- 1' to the calculation if the person's birthday occurs after the date of calculation in the calender year of the date of calculation. I was able to get a 100% correct answer using a nested if-then-else function. However, the simplest approach I've come across is actually in this reference manual here: It does the same thing as my more complicated if-then-else approach but it's a lot simpler. Stay away from the datediff() approaches for calculating age.

This approach is 100% accurate. It will even give you the correct age, in years, for someone born on February 29th. If you have a queue and you want to find out the average time in which an item gets processed, you can use this snippet (that's what we use for a dashboard in our project). If you have a table of queued items with `created_at` and `updated_at` datetime fields that represent when an item was added and when it was updated (processed) accordingly. So, [code] SELECT SEC_TO_TIME(AVG(TIME_TO_SEC(TIMEDIFF(`updated_at`, `created_at`)))) AS average_item_time FROM `mail_queue_item` [/code] shows something like '00:02:30' which means average idle time is 2.5 minutes per item. In our database there are partial dates. In spite of ALLOW_INVALID_DATES function DATEDIFF yields NULL on them.

Therefore, I wrote this function: CREATE FUNCTION ddiff(lait DATE, earlie DATE) RETURNS INTEGER(7) COMMENT 'less pickie dait-differens' DETERMINISTIC RETURN ROUND(PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM lait), EXTRACT(YEAR_MONTH FROM earlie)) * 30.44) + DAY(lait) - DAY(earlie) It is at most 2 days off the real difference, and the greatly biassed value for DAY(d) = 0 is of little importance in our case. If it mattered, we would make it 15. Function like Date_add considering only business's day (Monday-Friday) delimiter $ drop function if exists BusinessDaysDateAdd $ create function BusinessDaysDateAdd (.FromDate datetime.DaysToAdd int ) RETURNS datetime BEGIN.SET @Result = (FromDate + interval floor(DaysToAdd / 5) week) +....interval (mod(DaysToAdd,5) +.....(CASE mod(DAYOFWEEK(FromDate) + mod(DaysToAdd,5),7).....WHEN 0 THEN 2.....WHEN 1 THEN 2.....ELSE 0 END)) day.RETURN @Result; END; $ delimiter. A note for those of us who are stressed and missing some minor but annoying issues in using DATE_ADD or DATE_SUB, please note the following. SELECT DATE_SUB('2017-10-29', INTERVAL 10 DAY) ->2017-10-19 Assume you wish to specify a range of 10 days from a table MyTable with a column MyDate going back 10 days from MAX(MyDate) then consider the following; SELECT MAX(MyDate) FROM MyTable INTO @maxdt SET @initdt = DATE_SUB(@maxdt, INTERVAL 10 DAY); SELECT * FROM MyTable WHERE MyDate >= @initdt; ->returns a selection of 11 days To return 10 actual days in the range, reduce 10 to 9 as such.

SET @initdt = DATE_SUB(@maxdt, INTERVAL 9 DAY); ->returns 10 days in the range selection of sequential dates. Think zero-based sets. I hope this saves others 30-60 minutes unnecessary debugging.