I have used T-SQL for quite a few years now and I have always wondered why it has such bad support for date and times?
OK, I can create a data type called DateTime, but where is the function to return just the date portion?
There are functions to return the Year, Month and Day of a DateTime variable, but not the hour, minute or second!
Now even with DateTime2 we do not really get a lot more functionality. It would seem very common to want to format a date and time in a custom way but none is provided.
Now I can write a whole set of functions in a .NET library and go through the process of getting them installed onto a SQL Server but why is this basic sort of functionality not supported in the product in native code.
I know a lot of people have written a lot of different T-SQL Solutions to formatting dates etc, but it seems to me that it would take a developer only 1 day to implement native functions into T-SQL that provide a lot more functionality than having to write c# code and load the SQL-CLR just to format a date!
Here is the latest date & time functions as listed by the SQL Server 2008 BOL.
SYSDATETIME
Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included.
SYSDATETIMEOFFSET
Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included.
SYSUTCDATETIME
Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time).
CURRENT_TIMESTAMP
Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included.
GETDATE
Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included.
GETUTCDATE
Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time).
DATENAME
Returns a character string that represents the specified datepart of the specified date.
DATEPART
Returns an integer that represents the specified datepart of the specified date.
DAY
Returns an integer that represents the day day part of the specified date.
MONTH
Returns an integer that represents the month part of a specified date.
YEAR
Returns an integer that represents the year part of a specified date.
DATEDIFF
Returns the number of date or time datepart boundaries that are crossed between two specified dates.
DATEADD
Returns a new datetime value by adding an interval to the specified datepart of the specified date.
SWITCHOFFSET
SWITCHOFFSET changes the time zone offset of a DATETIMEOFFSET value and preserves the UTC value.
TODATETIMEOFFSET
TODATETIMEOFFSET transforms a datetime2 value into a datetimeoffset value. The datetime2 value is interpreted in local time for the specified time_zone.
And here is MySQLs
ADDDATE()(v4.1.1)
Add dates
ADDTIME()(v4.1.1)
Add time
CONVERT_TZ()(v4.1.3)
Convert from one timezone to another
CURDATE()
Return the current date
CURRENT_DATE(), CURRENT_DATE
Synonyms for CURDATE()
CURRENT_TIME(), CURRENT_TIME
Synonyms for CURTIME()
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP
Synonyms for NOW()
CURTIME()
Return the current time
DATE_ADD()
Add two dates
DATE_FORMAT()
Format date as specified
DATE_SUB()
Subtract two dates
DATE()(v4.1.1)
Extract the date part of a date or datetime expression
DATEDIFF()(v4.1.1)
Subtract two dates
DAY()(v4.1.1)
Synonym for DAYOFMONTH()
DAYNAME()(v4.1.21)
Return the name of the weekday
DAYOFMONTH()
Return the day of the month (0-31)
DAYOFWEEK()
Return the weekday index of the argument
DAYOFYEAR()
Return the day of the year (1-366)
EXTRACT
Extract part of a date
FROM_DAYS()
Convert a day number to a date
FROM_UNIXTIME()
Format UNIX timestamp as a date
GET_FORMAT()(v4.1.1)
Return a date format string
HOUR()
Extract the hour
LAST_DAY(v4.1.1)
Return the last day of the month for the argument
LOCALTIME(), LOCALTIME
Synonym for NOW()
LOCALTIMESTAMP, LOCALTIMESTAMP()(v4.0.6)
Synonym for NOW()
MAKEDATE()(v4.1.1)
Create a date from the year and day of year
MAKETIME(v4.1.1)
MAKETIME()
MICROSECOND()(v4.1.1)
Return the microseconds from argument
MINUTE()
Return the minute from the argument
MONTH()
Return the month from the date passed
MONTHNAME()(v4.1.21)
Return the name of the month
NOW()
Return the current date and time
PERIOD_ADD()
Add a period to a year-month
PERIOD_DIFF()
Return the number of months between periods
QUARTER()
Return the quarter from a date argument
SEC_TO_TIME()
Converts seconds to 'HH:MM:SS' format
SECOND()
Return the second (0-59)
STR_TO_DATE()(v4.1.1)
Convert a string to a date
SUBDATE()
A synonym for DATE_SUB() when invoked with three arguments
SUBTIME()(v4.1.1)
Subtract times
SYSDATE()
Return the time at which the function executes
TIME_FORMAT()
Format as time
TIME_TO_SEC()
Return the argument converted to seconds
TIME()(v4.1.1)
Extract the time portion of the expression passed
TIMEDIFF()(v4.1.1)
Subtract time
TIMESTAMP()(v4.1.1)
With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
TIMESTAMPADD()(v5.0.0)
Add an interval to a datetime expression
TIMESTAMPDIFF()(v5.0.0)
Subtract an interval from a datetime expression
TO_DAYS()
Return the date argument converted to days
UNIX_TIMESTAMP()
Return a UNIX timestamp
UTC_DATE()(v4.1.1)
Return the current UTC date
UTC_TIME()(v4.1.1)
Return the current UTC time
UTC_TIMESTAMP()(v4.1.1)
Return the current UTC date and time
WEEK()
Return the week number
WEEKDAY()
Return the weekday index
WEEKOFYEAR()(v4.1.1)
Return the calendar week of the date (0-53)
YEAR()
Return the year
YEARWEEK()
Return the year and week
More details on the date & time functions in MySQL
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html