FORMATDATE
Updated: 30 April 2009
Use FORMATDATE to convert a datetime value to text and specify the display formatting by using special format strings.
Syntax
SELECT [wctString].[wct].[FORMATDATE] (
<@Value, datetime,>
,<@Format, nvarchar(4000),>)
Arguments
@Value
is the value to be formatted. @Value is an expression of type datetime or of a type that can be implicitly converted to datetime.
@Format
A date format as a text string, for example 'm/d/yyyy'. The @Format argument can be of data types that are implicitly convertible to nvarchar or ntext.
Return Types
nvarchar(4000)
Remarks
· FORMATDATE treats date values differently than TEXT; generally there is a difference of 2 days between the results.
· FORMATDATE requires a datetime as input; TEXT takes any expression that can be evaluated as float.
· FORMATEDATE uses format strings found in the .NET framework. TEXT uses format strings found in EXCEL.
· Use d to display the day of the month as a number from 1 through 31.
· Use dd to display the day of the month as a number from 01 through 31. A single-digit day is formatted with a leading zero.
· Use ddd to display the day as an abbreviated name of the day of the week as defined in the current DateTimeFormatInfo.AbbreviatedDayNames property.
· Use dddd to display the full name of the day of the week as defined in the current DateTimeFormatInfo.DayNames property.
· Use f to display the most significant digit of the seconds fraction; use F to suppress the value when it is zero.
· Use ff to display the two most significant digits of the seconds fraction; use FF to suppress the value when it is zero or the rightmost value when it is zero.
· Use fff to display the three most significant digits of the seconds fraction; use FFF to suppress the value when it is zero or the rightmost value when it is zero.
· Use h to display the hour as a number from 1 through 12, that is, the hour as represented by a 12-hour clock that counts the whole hours since midnight or noon.
· Use hh to display the hour as a number from 01 through 12, that is, the hour as represented by a 12-hour clock that counts the whole hours since midnight or noon.
· Use H to display the hour as a number from 0 through 23, that is, the hour as represented by a zero-based 24-hour clock that counts the hours since midnight. A single-digit hour is formatted without a leading zero.
· Use HH to display the hour as a number from 00 through 23, that is, the hour as represented by a zero-based 24-hour clock that counts the hours since midnight. A single-digit hour is formatted with a leading zero.
· Use m to display the minute as a number from 0 through 59. The minute represents whole minutes that have passed since the last hour. A single-digit minute is formatted without a leading zero.
· Use mm to display the minute as a number from 00 through 59. The minute represents whole minutes that have passed since the last hour. A single-digit minute is formatted with a leading zero.
· Use M to display the month as a number from 1 through 12. A single-digit month is formatted without a leading zero.
· Use MM to display the month as a number from 01 through 12. A single-digit month is formatted with a leading zero.
· Use MMM to display the abbreviated name of the month.
· Use MMMM to display the full name of the month.
· Use s to display the seconds as a number from 0 through 59. The result represents whole seconds that have passed since the last minute. A single-digit second is formatted without a leading zero.
· Use ss to display the seconds as a number from 00 through 59. The result represents whole seconds that have passed since the last minute. A single-digit second is formatted with a leading zero.
· Use t to display the first character of the AM/PM designator.
· Use tt to display the AM/PM designator.
· Use y to display the year as a one or two-digit number. If the year has more than two digits, only the two low-order digits appear in the result. If the first digit of a two-digit year begins with a zero (for example, 2009), the number is formatted without a leading zero.
· Use yy to display the year as a two-digit number. If the year has more than two digits, only the two low-order digits appear in the result. If the two-digit year has fewer than two significant digits, the number is padded with leading zeros to achieve two digits.
· Use yyy to display the year with a minimum of three digits. If the year has more than three significant digits, they are included in the result string. If the year has fewer than three digits, the number is padded with leading zeros to achieve three digits.
· Use yyyy to display the year as a four-digit number. If the year has more than four digits, only the four low-order digits appear in the result. If the year has fewer than four digits, the number is padded with leading zeros to achieve four digits.
· Use yyyyy to display the year as a five-digit number. If the year has more than five digits, only the five low-order digits appear in the result. If the year has fewer than five digits, the number is padded with leading zeroes to achieve five digits.
Examples
CREATE TABLE #d (
[recno] [tinyint] NOT NULL,
[date_val] [datetime] NOT NULL,
[date_format] [varchar](30) NOT NULL
)
INSERT INTO #d VALUES( 1, '04/08/2009 08:05:07.036', 'M/d/yy')
INSERT INTO #d VALUES( 2, '04/08/2009 08:05:07.036', 'MM/dd/yy')
INSERT INTO #d VALUES( 3, '04/08/2009 08:05:07.036', 'dd MMM yy')
INSERT INTO #d VALUES( 4, '04/08/2009 08:05:07.036', 'dd MMMM yy')
INSERT INTO #d VALUES( 5, '04/08/2009 08:05:07.036', 'dd MMMM yyyy')
INSERT INTO #d VALUES( 6, '04/08/2009 08:05:07.036', 'h tt')
INSERT INTO #d VALUES( 7, '04/08/2009 08:05:07.036', 'h:mm tt')
INSERT INTO #d VALUES( 8, '04/08/2009 08:05:07.036', 'h:mm:ss tt')
INSERT INTO #d VALUES( 9, '04/08/2009 08:05:07.036', 'dddd, dd MMMM yy h:mm:ss tt')
INSERT INTO #d VALUES( 10, '04/08/2009 08:05:07.036', 'hh:mm:ss.f')
INSERT INTO #d VALUES( 11, '04/08/2009 08:05:07.036', 'hh:mm:ss.F')
INSERT INTO #d VALUES( 12, '04/08/2009 08:05:07.036', 'hh:mm:ss.ff')
INSERT INTO #d VALUES( 13, '04/08/2009 08:05:07.036', 'hh:mm:ss.FF')
select *, wct.FORMATDATE(date_val, date_format)
from #d
This produces the following result
recno date_val date_format
----- ----------------------- ------------------------------ -------------------
1 2009-04-08 08:05:07.037 M/d/yy 4/8/09
2 2009-04-08 08:05:07.037 MM/dd/yy 04/08/09
3 2009-04-08 08:05:07.037 dd MMM yy 08 Apr 09
4 2009-04-08 08:05:07.037 dd MMMM yy 08 April 09
5 2009-04-08 08:05:07.037 dd MMMM yyyy 08 April 2009
6 2009-04-08 08:05:07.037 h tt 8 AM
7 2009-04-08 08:05:07.037 h:mm tt 8:05 AM
8 2009-04-08 08:05:07.037 h:mm:ss tt 8:05:07 AM
9 2009-04-08 08:05:07.037 dddd, dd MMMM yy h:mm:ss tt Wednesday, 08 April 09 8:05:07 AM
10 2009-04-08 08:05:07.037 hh:mm:ss.f 08:05:07.0
11 2009-04-08 08:05:07.037 hh:mm:ss.F 08:05:07
12 2009-04-08 08:05:07.037 hh:mm:ss.ff 08:05:07.03
13 2009-04-08 08:05:07.037 hh:mm:ss.FF 08:05:07.03
(13 row(s) affected)