When working with dates in SQL Server, sometimes you might find yourself reaching for the DATEPART() function, only to realise that what you really need is the DATENAME() function. Then there may be other situations where DATEPART() is actually preferable to DATENAME().
So what’s the difference between the DATEPART() and DATENAME() functions?
Let’s find out.
In SQL Server, the T-SQL DATENAME() function returns a character string that represents the specified datepart of the specified date. For example, you can pass in 2021-01-07 and have SQL Server return only the month portion (January).
The return type for DATENAME() is nvarchar.
Examples below.
In SQL Server, the T-SQL DATEPART() function returns an integer that represents the specified datepart of the specified date. For example, you can pass in 2021-01-07 and have SQL Server return only the year portion (2021).
You can also extract the time portion. For example, you can pass in 2018-06-02 08:24:14.3112042 and have SQL Server return only the hour portion (8).
Examples below.
Continue readingSometimes when working with SQL Server (or any other DBMS for that matter), you need to return the short name for a month. By this I mean the 3 letter abbreviation of a month. For example, you need “Dec” instead of “December”.
Here are four ways you can extract the shortened month name from a date in SQL Server.
This article provides a solution to a problem you may occasionally encounter while using the DATEDIFF() function in SQL Server.
If you encounter the following error:
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
It’s because the return value is too large. The DATEDIFF() function returns its result as an int data type. The reason you got this message is that the return value is too big for the int data type. Fortunately there’s an easy way to fix this.
In MySQL, the DATE_FORMAT() function allows you to format the date and time.
Here’s an example:
SELECT DATE_FORMAT('2018-12-01', '%W, %d %M %Y');
Result:
Saturday, 01 December 2018
In this example, %W is for the weekday name, %d is for the day of the month, %M is for Month, and %Y is for Year. There are many more format specifiers available that enable you to specify a precise format for dates, as well as the time component.
MySQL has a number of functions that allow you to format the date and time. These include functions such as DATE_FORMAT() and TIME_FORMAT().
When using these functions, you provide arguments that tell it what format you’d like the date or time to be in. These arguments are called format specifiers, and they begin with a percentage sign (%) followed by a character (usually a letter, but not always).
The following table lists the format specifiers that can be used when formatting the date and time in MySQL.
In SQL Server, you can use the T-SQL FORMAT() function to format the date and/or time. Simply provide two arguments; the date/time and the format to use.
The format is supplied as a format string. A format string defines how the output should be formatted.
The FORMAT() function also accepts an optional “culture” argument, which allows you to specify a language/locale that the results should adhere to.