By: MSSQLTips |Updated: 2023-11-13 |Comments (63) | Related: 1 | 2 | 3 | 4 | 5 | 6 | > Dates
Problem
There are many instances when dates and times don't show up at your doorstepin the format you'd like it to be, nor does the output of a query fit the needsof the people viewing it. One option is to format the data in the application itself.Another option is to use the built-in functions SQL Server provides to format thedate string for you.
Solution
SQL Server provides a number of options you can use for formatting a date/time string in SQL queries and stored procedures either from an input file (Excel, CSV, etc.) or a date column(datetime, datetime2, smalldatetime, etc.) from a table.One of the first considerations is the actual date/time value needed if you arein the USA, need a French value or the ISO standard. The most commonis the current date/time using getdate(). This provides the current dateand time according to the server providing the date and time. If a universal date/time(UTC) is needed, then getutcdate() should be used. To change the format of thedate, you convert the requested date to a string and specify the format number correspondingto the format needed.
How to get SQL Date Format in SQL Server
- Use the SELECT statement with CONVERT function and date format optionfor the date values needed
- To get YYYY-MM-DD use this T-SQL syntax SELECT CONVERT(varchar, getdate(), 23)
- To get MM/DD/YY use this T-SQL syntax SELECT CONVERT(varchar, getdate(), 1)
- Check out the chart to get a list of all format options
The following table is a list of SQL date formats and an example of the output. The dateparameter usedfor all of these examples is "2022-12-30 00:38:54.840".
DATE ONLY FORMATS | |||
Format # | Query | Format | Sample |
---|---|---|---|
1 | select convert(varchar, getdate(), 1) | mm/dd/yy | 12/30/22 |
2 | select convert(varchar, getdate(), 2) | yy.mm.dd | 22.12.30 |
3 | select convert(varchar, getdate(), 3) | dd/mm/yy | 30/12/22 |
4 | select convert(varchar, getdate(), 4) | dd.mm.yy | 30.12.22 |
5 | select convert(varchar, getdate(), 5) | dd-mm-yy | 30-12-22 |
6 | select convert(varchar, getdate(), 6) | dd-Mon-yy | 30 Dec 22 |
7 | select convert(varchar, getdate(), 7) | Mon dd, yy | Dec 30, 22 |
10 | select convert(varchar, getdate(), 10) | mm-dd-yy | 12-30-22 |
11 | select convert(varchar, getdate(), 11) | yy/mm/dd | 22/12/30 |
12 | select convert(varchar, getdate(), 12) | yymmdd | 221230 |
23 | select convert(varchar, getdate(), 23) | yyyy-mm-dd | 2022-12-30 |
31 | select convert(varchar, getdate(), 31) | yyyy-dd-mm | 2022-30-12 |
32 | select convert(varchar, getdate(), 32) | mm-dd-yyyy | 12-30-2022 |
33 | select convert(varchar, getdate(), 33) | mm-yyyy-dd | 12-2022-30 |
34 | select convert(varchar, getdate(), 34) | dd-mm-yyyy | 30-12-2022 |
35 | select convert(varchar, getdate(), 35) | dd-yyyy-mm | 30-2022-12 |
101 | select convert(varchar, getdate(), 101) | mm/dd/yyyy | 12/30/2022 |
102 | select convert(varchar, getdate(), 102) | yyyy.mm.dd | 2022.12.30 |
103 | select convert(varchar, getdate(), 103) | dd/mm/yyyy | 30/12/2022 |
104 | select convert(varchar, getdate(), 104) | dd.mm.yyyy | 30.12.2022 |
105 | select convert(varchar, getdate(), 105) | dd-mm-yyyy | 30-12-2022 |
106 | select convert(varchar, getdate(), 106) | dd Mon yyyy | 30 Dec 2022 |
107 | select convert(varchar, getdate(), 107) | Mon dd, yyyy | Dec 30, 2022 |
110 | select convert(varchar, getdate(), 110) | mm-dd-yyyy | 12-30-2022 |
111 | select convert(varchar, getdate(), 111) | yyyy/mm/dd | 2022/12/30 |
112 | select convert(varchar, getdate(), 112) | yyyymmdd | 20221230 |
TIME ONLY FORMATS | |||
8 | select convert(varchar, getdate(), 8) | hh:mm:ss | 00:38:54 |
14 | select convert(varchar, getdate(), 14) | hh:mm:ss:nnn | 00:38:54:840 |
24 | select convert(varchar, getdate(), 24) | hh:mm:ss | 00:38:54 |
108 | select convert(varchar, getdate(), 108) | hh:mm:ss | 00:38:54 |
114 | select convert(varchar, getdate(), 114) | hh:mm:ss:nnn | 00:38:54:840 |
DATE & TIME FORMATS | |||
0 | select convert(varchar, getdate(), 0) | Mon dd yyyy hh:mm AM/PM | Dec 30 2022 12:38AM |
9 | select convert(varchar, getdate(), 9) | Mon dd yyyy hh:mm:ss:nnn AM/PM | Dec 30 2022 12:38:54:840AM |
13 | select convert(varchar, getdate(), 13) | dd Mon yyyy hh:mm:ss:nnn AM/PM | 30 Dec 2022 00:38:54:840AM |
20 | select convert(varchar, getdate(), 20) | yyyy-mm-dd hh:mm:ss | 2022-12-30 00:38:54 |
21 | select convert(varchar, getdate(), 21) | yyyy-mm-dd hh:mm:ss:nnn | 2022-12-30 00:38:54.840 |
22 | select convert(varchar, getdate(), 22) | mm/dd/yy hh:mm:ss AM/PM | 12/30/22 12:38:54 AM |
25 | select convert(varchar, getdate(), 25) | yyyy-mm-dd hh:mm:ss:nnn | 2022-12-30 00:38:54.840 |
26 | select convert(varchar, getdate(), 26) | yyyy-dd-mm hh:mm:ss:nnn | 2022-30-12 00:38:54.840 |
27 | select convert(varchar, getdate(), 27) | mm-dd-yyyy hh:mm:ss:nnn | 12-30-2022 00:38:54.840 |
28 | select convert(varchar, getdate(), 28) | mm-yyyy-dd hh:mm:ss:nnn | 12-2022-30 00:38:54.840 |
29 | select convert(varchar, getdate(), 29) | dd-mm-yyyy hh:mm:ss:nnn | 30-12-2022 00:38:54.840 |
30 | select convert(varchar, getdate(), 30) | dd-yyyy-mm hh:mm:ss:nnn | 30-2022-12 00:38:54.840 |
100 | select convert(varchar, getdate(), 100) | Mon dd yyyy hh:mm AM/PM | Dec 30 2022 12:38AM |
109 | select convert(varchar, getdate(), 109) | Mon dd yyyy hh:mm:ss:nnn AM/PM | Dec 30 2022 12:38:54:840AM |
113 | select convert(varchar, getdate(), 113) | dd Mon yyyy hh:mm:ss:nnn | 30 Dec 2022 00:38:54:840 |
120 | select convert(varchar, getdate(), 120) | yyyy-mm-dd hh:mm:ss | 2022-12-30 00:38:54 |
121 | select convert(varchar, getdate(), 121) | yyyy-mm-dd hh:mm:ss:nnn | 2022-12-30 00:38:54.840 |
126 | select convert(varchar, getdate(), 126) | yyyy-mm-dd T hh:mm:ss:nnn | 2022-12-30T00:38:54.840 |
127 | select convert(varchar, getdate(), 127) | yyyy-mm-dd T hh:mm:ss:nnn | 2022-12-30T00:38:54.840 |
ISLAMIC CALENDAR DATES | |||
130 | select convert(nvarchar, getdate(), 130) | dd mmm yyyy hh:mi:ss:nnn AM/PM | |
131 | select convert(nvarchar, getdate(), 131) | dd mmm yyyy hh:mi:ss:nnn AM/PM | 10/12/1444 12:38:54:840AM |
You can also format the date or time without dividing characters, as well asconcatenate the date and time string:
Sample statement | Format | Output |
---|---|---|
select replace(convert(varchar, getdate(),101),'/','') | mmddyyyy | 12302022 |
select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar,getdate(),108),':','') | mmddyyyyhhmmss | 12302022004426 |
If you want to get a list of all valid date and time formats, you could use thecode below and change the @date to GETDATE() or any other date you want to use.This will output just the valid formats.
DECLARE @counter INT = 0DECLARE @date DATETIME = '2006-12-30 00:38:54.840'CREATE TABLE #dateFormats (dateFormatOption int, dateOutput nvarchar(40))WHILE (@counter <= 150 )BEGIN BEGIN TRY INSERT INTO #dateFormats SELECT CONVERT(nvarchar, @counter), CONVERT(nvarchar,@date, @counter) SET @counter = @counter + 1 END TRY BEGIN CATCH; SET @counter = @counter + 1 IF @counter >= 150 BEGIN BREAK END END CATCHENDSELECT * FROM #dateFormats
Table of Date Data Types in SQL Server
Data Type | Description | Range | Storage | Example |
---|---|---|---|---|
date | A date without a time | 0001-Jan-01 - 9999-Dec-31 | 3 bytes | 2023-Dec-17 |
Time (factional seconds) | A time on a 24-hour clock without a date.There can be between 0 and 7 decimal places to support fractional seconds.The default is 7. | 00:00:00.0000000 - 23:59:59.9999999 | Differs based on the scale of the fractionalseconds. 3 bytes for 0-2 4 bytes for 3-4 5 bytes for 5-7 | time(0)16:23:45 time(4)16:23:45.5468 time(7)16:23:45.5468375 |
datetime | A date and time together in one column.The precision of the time is 1/300thof a second. | 1753-Jan-0100:00:00.000 - 9999-Dec-2123:59:59.997 | 8 bytes | 2023-Dec-17 17:23:45.547 |
smalldatetime | A date and time together in one column.The precision of the time 1 minute. There are no seconds and no fractionsof a second. | 1900-Jan-01 00:00 - 2079-Jun-06 23:59 | 4 bytes | 2023-Dec-17 16:23 |
datetime2 (factional seconds) | A date and time together in one column.There can be between 0 and 7 decimal places to support fractional secondsof the time. The default is 7. | 0001-Jan-0100:00:00.0000000 - 9999-Dec-3123:59:59.9999999 | Differs based on the scale of the fractionalseconds. 6 bytes for 0-2 7 bytes for 3-4 8 bytes for 5-7 | datetime2(0)2023-Dec-17 16:23:45 datetime2(4)2023-Dec-17 16:23:45.5468 datetime2(7)2023-Dec-17 16:23:45.5468375 |
Datetimeoffset(factional seconds) | This is exactly the same as a datetime2 exceptthat it also includes a time zone offset value. This data type requiresat least SQL Server 2016 | 0001-Jan-0100:00:00.0000000 - 9999-Dec-3123:59:59.9999999 With a time zone between UTC-14:00 and UTC+14:00 | Differs based on the scale of the fractionalseconds. 8 bytes for 0-2 9 bytes for 3-4 10 bytes for 5-7 | datetimeoffset(0)2023-Dec-17 16:23:45+08:00 datetimeoffset(4)2023-Dec-17 16:23:45.5468+08:00 datetimeoffset(7)2023-Dec-17 16:23:45.5468375+08:00 |
Here are links to date data type articles:
- Working with Date and Time Data Types in SQL Server
- Understanding Time Zones in SQL Server
- Convert SQL Server DateTime Data Type to DateTimeOffset Data Type
- SQL Server Date Time Calculation Examples
- SQL Server Date and Time Functions with Examples
SQL Date Format dd/mm/yyyy with SQL CONVERT
Here is an example to get this date format.
-- The date used for this example was November 12, 2023.DECLARE @Datetime DATETIME;SET @Datetime = GETDATE(); --dd/mm/yyyy with 4 DIGIT YEARSELECT CONVERT(VARCHAR(10), @Datetime, 103) CurrentDateFormattedAsText; --dd/mm/yy with 2 DIGIT YEARSELECT CONVERT(VARCHAR(8), @Datetime, 3) CurrentDateFormattedAsText;-- pull data from a database table-- The date used for this example was January 25, 2013. --SELECT a datetime column as a string formatted dd/mm/yyyy (4 digit year)SELECT TOP 3 CONVERT(CHAR(10), ExpectedDeliveryDate, 103) ExpectedDeliveryDateFormattedAsTextFROM Purchasing.PurchaseOrdersWHERE OrderDate < @Datetime; --SELECT a datetime column as a string formatted dd/mm/yy (2 digit year)SELECT TOP 3 CONVERT(CHAR(8), ExpectedDeliveryDate, 3) ExpectedDeliveryDateFormattedAsTextFROM Purchasing.PurchaseOrdersWHERE OrderDate < @Datetime;
Here is the output.
SQL Date Format mm/dd/yyyy with SQL CONVERT
Here is an example to get this date format.
-- The date used for this example was November 12, 2023.DECLARE @Datetime DATETIME;SET @Datetime = GETDATE(); --mm/dd/yyyy with 4 DIGIT YEARSELECT CONVERT(VARCHAR(10), @Datetime, 101) CurrentDateFormattedAsText; --mm/dd/yy with 2 DIGIT YEARSELECT CONVERT(VARCHAR(8), @Datetime, 1) CurrentDateFormattedAsText;-- pull data from a database table-- The date used for this example was January 15, 2013.--SELECT a datetime column as a string formatted mm/dd/yyyy (4 digit year)SELECT TOP 3 CONVERT(CHAR(10), ExpectedDeliveryDate, 101) ExpectedDeliveryDateFormattedAsTextFROM Purchasing.PurchaseOrdersWHERE OrderDate < @Datetime; --SELECT a datetime column as a string formatted mm/dd/yy (2 digit year)SELECT TOP 3 CONVERT(CHAR(8), ExpectedDeliveryDate, 1) ExpectedDeliveryDateFormattedAsTextFROM Purchasing.PurchaseOrdersWHERE OrderDate < @Datetime;
Here is the output.
SQL Date Format yyyy mm dd with SQL CONVERT
Here is an example to get this date format.
-- The date used for this example was November 12, 2023.DECLARE @Datetime DATETIME;SET @Datetime = GETDATE(); --yyyy mm dd with 4 DIGIT YEARSELECT REPLACE(CONVERT(VARCHAR(10), @Datetime, 102), '.', ' ') CurrentDateFormattedAsText; --yy mm dd with 2 DIGIT YEARSELECT REPLACE(CONVERT(VARCHAR(8), @Datetime, 2), '.', ' ') CurrentDateFormattedAsText;-- pull data from a database table-- The date used for this example was January 15, 2013.--SELECT a datetime column as a string formatted yyyy mm dd (4 digit year)SELECT TOP 3 REPLACE(CONVERT(CHAR(10), ExpectedDeliveryDate, 102), '.', ' ') ExpectedDeliveryDateFormattedAsTextFROM Purchasing.PurchaseOrdersWHERE OrderDate < @Datetime; --SELECT a datetime column as a string formatted yy mm dd (2 digit year)SELECT TOP 3 REPLACE(CONVERT(CHAR(8), ExpectedDeliveryDate, 2), '.', ' ') ExpectedDeliveryDateFormattedAsTextFROM Purchasing.PurchaseOrdersWHERE OrderDate < @Datetime;
Here is the output.
SQL Date Format yyyymmdd with SQL CONVERT
Here is an example to get this date format.
-- The date used for this example was November 12, 2023.DECLARE @Datetime DATETIME;SET @Datetime = GETDATE(); --yyyymmdd with 4 DIGIT YEARSELECT REPLACE(CONVERT(VARCHAR(10), @Datetime, 112), '.', ' ') CurrentDateFormattedAsText; --yymmdd with 2 DIGIT YEARSELECT REPLACE(CONVERT(VARCHAR(8), @Datetime, 12), '.', ' ') CurrentDateFormattedAsText;-- pull data from a database table-- The date used for this example was January 15, 2013.--SELECT a datetime column as a string formatted yyyymmdd (4 digit year)SELECT TOP 3 REPLACE(CONVERT(CHAR(10), ExpectedDeliveryDate, 112), '.', ' ') ExpectedDeliveryDateFormattedAsTextFROM Purchasing.PurchaseOrdersWHERE OrderDate < @Datetime; --SELECT a datetime column as a string formatted yymmdd (2 digit year)SELECT TOP 3 REPLACE(CONVERT(CHAR(8), ExpectedDeliveryDate, 12), '.', ' ') ExpectedDeliveryDateFormattedAsTextFROM Purchasing.PurchaseOrdersWHERE OrderDate < @Datetime;
Here is the output.
SQL Date format ddmmyyyy with SQL CONVERT
Here is an example to get this date format.
-- The date used for this example was November 12, 2023.DECLARE @Datetime DATETIME;SET @Datetime = GETDATE(); --ddmmyyyy with 4 DIGIT YEARSELECT REPLACE(CONVERT(VARCHAR(10), @Datetime, 104), '.', '') CurrentDateFormattedAsText; --ddmmyy with 2 DIGIT YEARSELECT REPLACE(CONVERT(VARCHAR(8), @Datetime, 4), '.', '') CurrentDateFormattedAsText;-- pull data from a database table-- The date used for this example was January 15, 2013.--SELECT a datetime column as a string formatted ddmmyyyy (4 digit year)SELECT TOP 3 REPLACE(CONVERT(CHAR(10), ExpectedDeliveryDate, 104), '.', '') ExpectedDeliveryDateFormattedAsTextFROM Purchasing.PurchaseOrdersWHERE OrderDate < @Datetime; --SELECT a datetime column as a string formatted ddmmyy (2 digit year)SELECT TOP 3 REPLACE(CONVERT(CHAR(8), ExpectedDeliveryDate, 4), '.', '') ExpectedDeliveryDateFormattedAsTextFROM Purchasing.PurchaseOrdersWHERE OrderDate < @Datetime;
Here is the output.
SQL Date Format yyyy-mm-dd with SQL CONVERT
Here is an example to get this date format.
-- The date used for this example was November 12, 2023.DECLARE @Datetime DATETIME;SET @Datetime = GETDATE(); --yyyy-mm-dd with 4 DIGIT YEARSELECT REPLACE(CONVERT(VARCHAR(10), @Datetime, 111), '/', '-') CurrentDateFormattedAsText; --yy-mm-dd with 2 DIGIT YEARSELECT REPLACE(CONVERT(VARCHAR(8), @Datetime, 11), '/', '-') CurrentDateFormattedAsText;-- pull data from a database table-- The date used for this example was January 15, 2013.--SELECT a datetime column as a string formatted yyyy-mm-dd (4 digit year)SELECT TOP 3 REPLACE(CONVERT(CHAR(10), ExpectedDeliveryDate, 111), '/', '-') ExpectedDeliveryDateFormattedAsTextFROM Purchasing.PurchaseOrdersWHERE OrderDate < @Datetime; --SELECT a datetime column as a string formatted yy-mm-dd (2 digit year)SELECT TOP 3 REPLACE(CONVERT(CHAR(8), ExpectedDeliveryDate, 11), '/', '-') ExpectedDeliveryDateFormattedAsTextFROM Purchasing.PurchaseOrdersWHERE OrderDate < @Datetime;
Here is the output.
SQL Date Format mm/dd/yyyy with SQL CONVERT
Here is an example to get this date format.
-- The date used for this example was November 12, 2023.DECLARE @Datetime DATETIME;SET @Datetime = GETDATE(); --mm/dd/yyyy with 4 DIGIT YEARSELECT REPLACE(CONVERT(VARCHAR(10), @Datetime, 110), '/', '-') CurrentDateFormattedAsText; --mm/dd/yy with 2 DIGIT YEARSELECT REPLACE(CONVERT(VARCHAR(8), @Datetime, 10), '/', '-') CurrentDateFormattedAsText;-- pull data from a database table-- The date used for this example was January 15, 2013. --SELECT a datetime column as a string formatted mm/dd/yyyy (4 digit year)SELECT TOP 3 REPLACE(CONVERT(CHAR(10), ExpectedDeliveryDate, 110), '/', '-') ExpectedDeliveryDateFormattedAsTextFROM Purchasing.PurchaseOrdersWHERE OrderDate < @Datetime; --SELECT a datetime column as a string formatted mm/dd/yy (2 digit year)SELECT TOP 3 REPLACE(CONVERT(CHAR(8), ExpectedDeliveryDate, 10), '/', '-') ExpectedDeliveryDateFormattedAsTextFROM Purchasing.PurchaseOrdersWHERE OrderDate < @Datetime;
Here is the output.
Recommended Reading
Continue your learning on Microsoft SQL Server dates with these tips andtutorials which could be valuable to beginners and experienced Developers alike:
- Determine SQL Server Date and Time Parts with DATEPART and DATENAMEFunctions
- SQL Server Date and Time Data Types
- SQL Server function to convert integer date to datetime format
- SQL Database DateTime Best Practices
- Format SQL Server Dates with FORMAT Function
- SQLServer Date Functions
- Add and Subtract Dates using DATEADD in SQL Server
- DATEDIFF SQL Server Function
- SQL Date Function Tutorial - DATEADD, DATEDIFF, DATENAME, DATEPART and more
- Getting Started with SQL DATEDIFF and DATEDIFF_BIG Functions with Use Cases
- How SQL Server handles the date format YYYY-MM-DD
- SQL Convert Date to YYYYMMDD
Next Steps
- The formats listed above are not inclusive of all formats provided. Experimentwith various dates and the different format numbers to see what others are available.
- These formats can be used for all date/time functions, as well as data beingserved to clients, so experiment with these data format conversions to see ifthey can provide data more efficiently.
- Also, check out theSQL Server FORMAT Function to Format Dates.
Last updated by Eric Blinn on 2023-11-13
About the author
MSSQLTips.com was started in 2006 to provide SQL Server content about various aspects of SQL Server and other database platforms.
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Comments For This Article
Friday, May 24, 2024 - 11:22:15 AM - Raj | Back To Top (92261) |
Great article - I use it all the time being a Data Warehouse professional (lot to do with dates, left and right :) |
Thursday, January 4, 2024 - 12:48:48 AM - parmod | Back To Top (91829) |
Thank you for the article, I was trying to fix the Date format issue while troubleshooting a website and it helped a lot. Keep up the good work. |
Wednesday, March 1, 2023 - 5:55:34 AM - Alison | Back To Top (90964) |
A really useful article, and very clear. |
Friday, February 3, 2023 - 1:04:49 AM - Soniya | Back To Top (90875) |
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.' I found this type of error, I can i solve this |
Thursday, January 12, 2023 - 1:45:12 PM - Rahul | Back To Top (90825) |
Thank you for the article, I was trying to fix the Date format issue while troubleshooting a website and it helped a lot. Keep up the good work. |
Wednesday, November 2, 2022 - 5:41:37 AM - Ian | Back To Top (90655) |
Thanks for doing this article, very easy to follow explanation with examples of how to use dates in SQL. |
Tuesday, September 13, 2022 - 8:28:48 AM - Greg Robidoux | Back To Top (90470) |
Hi Osman, You could just do UPDATE statements to convert your text values to the standard time values you mentioned. Another option is to use a CASE statement to make the change if you don't want to change the actual data. https://www.mssqltips.com/sqlservertip/6031/sql-server-case-expression-overview/ -Greg |
Monday, September 12, 2022 - 6:29:56 PM - Osman | Back To Top (90467) |
Hi, Please help me with this.. I got a really messy data that I am trying to clean. There is a column there that has time. Most of the time values are entered in this format 18h00, which is fine. But there are rows that have “morning” or “afternoon” or “after dark” instead of actual time. My question is: how do I convert these strings like “afternoon” which I know could be like “13h00” or something to a time value instead of having it as a string. |
Tuesday, August 2, 2022 - 7:32:08 PM - LeLik | Back To Top (90331) |
Hi you have the typo for Format #13 |
Wednesday, April 27, 2022 - 12:27:58 AM - negah | Back To Top (90039) |
hi i convert '2015-04-14 00:30:00.000' to '2015-04-14 24:30:00.000' in sql (my parametr type as datetime) please help me |
Tuesday, December 14, 2021 - 11:16:06 AM - Greg Robidoux | Back To Top (89582) |
Hi Krishna, try this. declare @d int = 44542 -- Dec 14, 2021 -Greg |
Tuesday, December 14, 2021 - 7:43:18 AM - Krishna | Back To Top (89580) |
Hi, I want to convert a Text Field which is saving the Data in String into a Date field. The value stored is in the below format. Value Stored = 66028 Thanks, |
Monday, September 20, 2021 - 10:35:42 AM - Greg Robidoux | Back To Top (89246) |
Hi Amol, you could create another table with the mapping and then join to that table based on the day of the month to return your A, B, C, etc. values. -Greg |
Saturday, September 18, 2021 - 1:32:31 PM - Amol | Back To Top (89240) |
Hello, i am beginner in sql i have one task of conversion of date like. if day of month is 01 then conversion would like A, like for 02 it would be B( 01= A,02=B,.......27=AA,28=AB...) could we do like code this sql please suggest solution if it could be done. Thanks |
Monday, March 1, 2021 - 10:59:59 AM - Greg Robidoux | Back To Top (88317) |
Thanks Ron. This has been updated. Greg |
Sunday, February 28, 2021 - 11:54:18 AM - Ron Moses | Back To Top (88314) |
Example #3 st the top of the article is wrong. You indicate that YYYY will be returned by format value 1. But this format only returns YY, as you indicate in the chart immediately below. 101 returns the year with century. |
Friday, April 3, 2020 - 9:43:20 AM - Jeff Moden | Back To Top (85264) |
Be advised that the FORMAT function is horrible for performance. It takes a very typical 43 times longer than even some of the more complex things you can do with CONVERT. I STRONGLY RECOMMEND AVOIDING THE USE OF THE FORMAT FUNCTION FOR THAT REASON. It's the "death of a thousand small cuts". |
Tuesday, March 17, 2020 - 3:42:44 PM - Greg Robidoux | Back To Top (85131) |
Hi George, you can do this with the format function as well. Here is a tip about that:https://www.mssqltips.com/sqlservertip/2655/new-sql-server-function-to-format-dates/ Also, I have heard that the format function is a lot slower than the convert function. -Greg |
Tuesday, March 17, 2020 - 3:13:22 PM - George | Back To Top (85130) |
Wouldn't most of this be moot with the FORMAT function? That will automatically turn it into an NVARCHAR(4000) field, and you have a lot of flexibility to define it how you want. select |
Wednesday, January 15, 2020 - 3:40:50 AM - Damba | Back To Top (83774) |
THANK YOU! |
Monday, December 16, 2019 - 9:54:34 AM - Greg Robidoux | Back To Top (83436) |
Thanks Gregg for the feedback. We will see if we can update all of the examples. The reader could also run the T-SQL script and enter other dates. This will produce all valid results, so they can see how the date would be formatted. Thanks |
Saturday, December 14, 2019 - 11:27:03 AM - Gregg Tracton | Back To Top (83426) |
revise your example date, please. use a example date (ex, 1/2/1994) that has a month AND day less than 10 so readers can see if the leading zero is prefixed or not. |
Tuesday, July 30, 2019 - 5:42:29 AM - MANISH | Back To Top (81898) |
THANK YOU |
Wednesday, July 17, 2019 - 9:45:22 AM - jack | Back To Top (81778) |
Thank for the providing that giant table formats! |
Monday, June 3, 2019 - 11:55:28 AM - Anne Cao | Back To Top (81310) |
Thank you for the good tip. I also see sometimes the column could store seconds only. The seconds is the seconds since 0 hours of the day. Any conversion formula? Thanks |
Thursday, May 16, 2019 - 11:00:26 AM - Greg Robidoux | Back To Top (80105) |
Thanks Stewart for the input. As you mentioned this tip uses VARCHAR and that could be changed to NVARCHAR which will return the same results for most of the items and take care of format 130. -Greg |
Thursday, May 16, 2019 - 10:49:18 AM - Stewart | Back To Top (80104) |
Formats 130 and 131 are the Islamic calendar date. But 130 contains Arabic characters, and therefore you would need to convert to nvarchar, not varchar. Still, the order of the parts comes out in a bit of a muddle, unless I tell my software to render it right-to-left. |
Thursday, April 18, 2019 - 4:57:37 PM - Greg Robidoux | Back To Top (79597) |
Hi Ben, you can do this: SELECT CONVERT(datetime,'1/1/2018 12:00:00 AM') -Greg |
Thursday, April 18, 2019 - 3:19:51 PM - Ben LaRoche | Back To Top (79596) |
Does anyone know how to convertthis format "1/1/2018 12:00:00 AM" (currently in text) to a date time type? Thanks, Ben |
Friday, April 12, 2019 - 6:03:09 AM - Sindhuja K S | Back To Top (79542) |
Its very nice article . Its saved my time. Thank you. |
Wednesday, March 20, 2019 - 12:36:07 PM - Greg Robidoux | Back To Top (79351) |
Hi Zulfiqar, it looks like the output is 2019-03-20. You could also just do this and get the same result. The third parameter doesn't seem to make a difference. SELECT CONVERT(DATE, GETDATE()) -Greg |
Wednesday, March 20, 2019 - 12:13:12 PM - Zulfiqar Dholkawala | Back To Top (79350) |
Hi What would this output? CONVERT(DATE, GETDATE(), 123) Thank you. |
Wednesday, March 20, 2019 - 10:25:54 AM - marcus | Back To Top (79348) |
i love you guys |
Monday, March 11, 2019 - 2:04:56 PM - Greg Robidoux | Back To Top (79258) |
Hi David, Take a look at this tip: https://www.mssqltips.com/sqlservertip/2655/format-sql-server-dates-with-format-function/ -Greg |
Monday, March 11, 2019 - 7:31:30 AM - David | Back To Top (79245) |
Hello, Thank you for this great post,but i can't find this date format Jeu. 07 Mar 2019 any suggestion please ? Thank you |
Thursday, March 22, 2018 - 2:14:35 PM - Aubrey | Back To Top (75502) |
A simpler way to format the date is: SELECT CAST(GETDATE() AS DATE) AS ‘ColumnName’ This will produce a date in this format: 2018-03-22 You can replace the “GETDATE()” with the name of date column you want to modify. As for the Time, use: SELECT FORMAT(GETDATE(), ‘hh:mm’) AS ‘ColumnName’ This will produce the time in this format: 01:07 Or, if you want the time returned in Military format, change the ‘hh’ to upper case ‘HH’ SELECT FORMAT(GETDATE(), ‘HH:mm’) AS ‘Time’ This will produce the time in this format: 13:09 Hope this helps someone. |
Thursday, March 22, 2018 - 6:23:48 AM - Rihan | Back To Top (75500) |
This was helpful |
Saturday, October 21, 2017 - 7:27:09 AM - Zumer Jan | Back To Top (68613) |
Excellent Post |
Tuesday, May 9, 2017 - 11:26:34 AM - Julie | Back To Top (55621) |
I would add that cast(date_expression as date) is sometimes a usefulsolution. It keeps the date characteristics for sorting, comparing, etc. but drops the time portion. This is great for items going to excel. |
Thursday, April 6, 2017 - 2:11:44 PM - Greg Robidoux | Back To Top (54369) |
Hi Koduru, Take a look at this tip: https://www.mssqltips.com/sqlservertip/2507/determine-sql-server-date-and-time-with-datepart-and-datename-functions/ |
Thursday, April 6, 2017 - 1:01:01 PM - koduru jaladakshi | Back To Top (54364) |
hi how to get result in sql server like year:2017 date:06 month:04 please help me thank u. |
Thursday, December 10, 2015 - 2:16:54 AM - Adarsh v nair | Back To Top (40228) |
01/01/0100 10:00:00 how to convert time 10:00:0 |
Saturday, October 3, 2015 - 5:22:09 AM - senya | Back To Top (38820) |
hello,i just want to create a date from the table in sql server 2008.But there one error was araised whatever change the datatype(varchar,int,char).such as that error is, "Arithmetic overflow error converting expression to data type int. how ll clear this error. |
Monday, August 31, 2015 - 10:25:38 AM - Umit | Back To Top (38572) |
Hi there, I am retrieving data with SQL from a Oracle database where I encounter different date formats in the same data group. I run the SQL query in VBA and the query results are pasted in an excel tab. So in the same data column I have data with multiple date formats in excel. Only one date format recognized as date, according to pc's regional settings. I need to convert all data into same format before I retrieve them to excel. right now I use this:TO_CHAR(v.BASLANGICTARIHI, 'DD/MM/YYYY') but it does not help. Can someone help me here? Thanks. |
Monday, January 5, 2015 - 3:20:07 PM - Sharim | Back To Top (35831) |
--Output as char with space like 3 1 1 0 2 0 1 4. set @dt = CONVERT(varchar(26),getdate(),103) WHILE (@i < len(@dt)+1) |
Wednesday, November 5, 2014 - 1:40:28 AM - San | Back To Top (35184) |
HiGreg Robidoux, Thanks a lot. Your suggestion were working well... Really you have given me a great thing. Once again thanks.... |
Saturday, November 1, 2014 - 6:54:03 AM - Greg Robidoux | Back To Top (35146) |
Hi San, use this select replace(convert(varchar, getdate(),103),'/','') to get the output you need and then use one of these functions to add the space between each number http://dbamentalist.wordpress.com/2013/06/17/t-sql-function-add-a-space-between-all-characters/comment-page-1/ http://www.codeproject.com/Tips/426728/T-SQL-Function-Add-a-space-between-all-characters |
Saturday, November 1, 2014 - 6:42:45 AM - San | Back To Top (35145) |
Input date (31/10/2014) Output as char with space like 3 1 1 0 2 0 1 4. Can anyone help me? This is for cheque date printing... |
Monday, February 18, 2013 - 11:32:21 PM - giam | Back To Top (22251) |
thank you very much. |
Friday, November 30, 2012 - 9:42:22 AM - Jeremy Kadlec | Back To Top (20670) |
satheeshkumar, Have you checked out Tim Ford's tip - http://www.mssqltips.com/sqlservertip/1712/sql-server-function-to-convert-integer-date-to-datetime-format/? I believe he has a function to take care of the date logic, but I think you will have to modify it to include the time logic you need. HTH. Thank you, |
Friday, November 30, 2012 - 2:40:01 AM - satheeshkumar | Back To Top (20656) |
hI BELOW MY REQUORMENT Input - 20121130121020 = output 2012/11/30 12:10:20 Can any one help me out this |
Monday, November 19, 2012 - 9:37:04 AM - Jeremy Kadlec | Back To Top (20409) |
Dev, Can you post the date format you are seeing in SSMS and the final format you would like? Thank you, |
Monday, November 19, 2012 - 5:59:40 AM - Dev | Back To Top (20405) |
How to convert datetime format stored data to 24hr time format in SSMS 2008? |
Wednesday, September 19, 2012 - 1:06:03 AM - e | Back To Top (19559) |
Monday, September 17, 2012 - 6:10:13 PM - Mike | Back To Top (19540) |
Don't forget 23: 2006-12-30 |
Friday, August 17, 2012 - 12:57:12 AM - tintu | Back To Top (19089) |
i want extracting date from sql server,using php how to get this?? i used $d=convert(varchar,getdate(),101); echo $d; this is not working |
Tuesday, July 24, 2012 - 5:47:14 AM - Gayatri Tiwari | Back To Top (18766) |
its very very helpful... still i need few more details... for few more formulaes.... |
Wednesday, May 16, 2012 - 9:23:58 AM - Jeremy Kadlec | Back To Top (17482) | |||
Tessa, Would this work for you? convert(varchar,getdate(),105) + ' ' + convert(varchar,getdate(),108) Is there any reason you would not use this format:
Have you also seen these tips: http://www.mssqltips.com/sqlservertip/2588/new-date-and-time-functions-in-sql-server-2012/ http://www.mssqltips.com/sqlservertip/1616/sql-server-2008-date-and-time-data-types/ HTH. Thank you, |
Wednesday, May 16, 2012 - 7:42:46 AM - Tessa | Back To Top (17479) |
I would like to see the complete date and time a DD-MM-YYYY HH:MM:SS I use:convert(varchar,getdate(),105) + convert(varchar,getdate(),108) I get: 16-05-201213:42:18 How would I get the extra space between the Date and Time to get 16-05-2012 13:42:18 |
Thursday, May 12, 2011 - 11:15:21 AM - Vijay Prakash Vyas | Back To Top (13814) |
It's very helpful info thanks for help!!!!! |
Thursday, January 8, 2009 - 6:46:58 AM - tosscrosby | Back To Top (2533) |
Actually, I was simply complimenting the "tip" as it provided exactly what I needed for ANY date: select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','') This converts any MSSQL datetime to MMDDYYYYHHMMSS - exactly what the Oracle folks wanted! Thanks anyway :-) |
Thursday, January 8, 2009 - 6:27:17 AM - Senthilkumar.S | Back To Top (2529) |
Select the particular year,month, day,pls do following query --------------------------------------------------------------------------------------- select * from barrowBooks where year(barrow_date)='2008' and month(barrow_date)='11' and day(barrow_date)='12' order by barrow_date desc convert the date and time using this query ---------------------------------------------------------------- select convert(char(11),getdate(),108) -->Result of time 11:18:18 select convert(char(10),getdate(),101) -->Result of Date 11/06/2008 |
Wednesday, January 7, 2009 - 12:50:38 PM - tosscrosby | Back To Top (2520) |
I just had a request from our Oracle team to see if I could supply them with dates in a MMDDYYYYHHMMSS format. Came here and found what I need in all of about 30 seconds. Thanks. |