POSTS
How to
Date/Time To String Format Converter

Date/Time To String Format Converter

Table of contents

This tutorial will show you how to use the CONVERT() function in SQL Server to convert a datetime to a string.

The CONVERT() function's datetime to string conversion

Using the CONVERT() function, a datetime can be transformed into a string in the following ways:

Use CONVERT(VARCHAR, datetime [,style]).
Hidden meanings: The term "SQL" refers to the Structured Query Language. ( sql )

Here's the syntax:

  • As the first parameter, VARCHAR is the string type.
  • An expression that evaluates to a date or datetime value can be converted to a string using the datetime function.
  • When using sytle, the date must be written in the exact format that is defined by the style. Style is a SQL Server-defined numeric value. An alternative to specifying a style parameter is to

In the table below, we can see examples of acceptable datetime formats after string conversion.

Without the yy century Century (yyyy) Standard Format – 0 or 100 Instances of datetime and smalldatetime use this as their default. Monday, December 13, HH:MM (AM or PM) 1 101 U S Mm/dd/yyyy = 1
A date format of 101 = mm/dd/yyyy 2 102 ANSI 2 = yy mm dd
102 = yyyy mm dd 3 103 British/French Date Format: 3 = dd/mm/yy
The format of the year 103 is: dd/mm/yyyy 4 104 German 4 = dd mm yy
104 = dd mm yyyy 5 105 Italian 5. dd/mm/yy
505 = dd-mm-yyyy 6 106 – 6. = dd.m.yy
The formula for the year 106 is: dd.mon.yyyy 7 107 – 7 = Monday, DD, YY
The date format is: 107 = Monday, December 7th, yyyy 8 108 – hh:mi:ss – 9 or 109 Milliseconds by default Monday, December 31, HH:MI:SS:MM AM (or PM) 10 110 USA 00 = mm-dd-yy
110 = dd-mm-yyyy 11 111 JAPAN A date format of 11 = yy/mm/dd
Year, Month, Day = 111 12 112 ISO Year 12 = yymmdd
For example: 112 = yyyymmdd – 13 or 113 Milliseconds until Europe's default Time in format: dd:mon:yyyy hh:mi:ss:mmm(24h) 14 114 – hh:mi:ss:mmm(24h) – 20 or 120 ODBC standard (24h format) yyyy-mm-dd hh:mi:ss – 21 or 121 Time, dates, datetime2, and datetimeoffset all default to ODBC canonical (with milliseconds) format. dates formatted as yyyy-mm-dd hh:mi:ss mmm(24h) – 126 ISO8601 yyyy-mm-ddThh:mi:ss mm (no punctuation) – 127 Time Zone Z in ISO8601 yyyy-mm-ddThh:mi:ss (Without the spaces) mmmZ – 130 Hijri date: dd/mon/yyyy hh:mi:ss:mmmAM time: hh:mi:ss:mmmAM – 131 Hijri morning dd/mm/yyyy hh:mi:ss:mmmAM

Date and time string conversion examples

For starters, let's say you need to change a datetime from the typical "mon dd yyyy hh:miAM (or PM)" format to a string.

DECLARE DATETIME @dt = '2019-12-31 14:43:35 863' ; SELECT CONVERT ( VARCHAR ( 20 ),@dt, ) s1, CONVERT ( VARCHAR ( 20 ),@dt, 100 ) s2;
To translate the code: What is SQL? SQL stands for "Structured Query Language." ( sql )

You can see the results below:

s1 s2 Time: 12:43 PM, December 31st, 2019 It only affects the first row.
In secret language: Standard Query Language (SQL) ( sql )

Second, an illustration of how to change a datetime to a string using the mm/dd/yy and mm/dd/yyyy formats

DECLARE Attribute DATETIME @dt = '2019-12-31 14:43:35 863' ; SELECT CONVERT ( VARCHAR ( 10 ),@dt, 1 ) s1, CONVERT ( VARCHAR ( 10 ),@dt, 101 ) s2;
Secret code: This query was written in SQL (Structured Query Language). ( sql )

The result is as follows:

s1 s2 12/31/19 12/31/2019 Only the first row was modified.
Secret code: Standard Query Language (SQL) ( sql )

Monday, December 31, 2013 at 11:59:59 a.m. (or pm) is an example of a datetime string that has been converted from the AM/PM format.

DECLARE Date and Time Expression @dt = '2019-12-31 14:43:35 863' ; SELECT CONVERT ( VARCHAR ( 10 ),@dt, 3 ) s1, CONVERT ( VARCHAR ( 10 ),@dt, 103 ) s2;
Secret code: Standard Query Language (SQL) ( sql )

And the results are as follows:

s1 s2 31/12/19 31/12/2019 The problem occurs in the first row.
Secret language: What is SQL? SQL stands for "Structured Query Language." ( sql )

Fourthly, dd datetime to string conversion mm yy and dd mm dates in yyyy format

DECLARE DATETIME @dt = '2019-12-31 14:43:35 863' ; SELECT CONVERT ( VARCHAR ( 10 ),@dt, 4 ) s1, CONVERT ( VARCHAR ( 10 ),@dt, 104 ) s2;
Subtle communication: In computer science, this method is known as "SQL" (for "Structured Query Language ( sql )

The results are as follows.

s1 s2 31 12 19 31 12 2019 This only affects the first row.
Symbolic representation of The term "SQL" refers to the Structured Query Language. ( sql )

5. An illustration of converting a date/time to a string using the dd-mm-yy and dd-mm-yyyy formats

DECLARE DATETIME @dt = '2019-12-31 14:43:35 863' ; SELECT CONVERT ( VARCHAR ( 10 ),@dt, 5 ) s1, CONVERT ( VARCHAR ( 10 ),@dt, 105 ) s2;
Secret code: To query data in a database, use SQL (Structured Query Language). ( sql )

The result is as follows:

s1 s2 31-12-19 31-12-2019 (It only affects the first row)
Secret code: Slang for "Structured Query Language," or SQL. ( sql )

Formatting a date/time string to be displayed as dd/mm/yyyy is shown in Example 6 below.

DECLARE DATETIME = @dt '2019-12-31 14:43:35 863' ; SELECT CONVERT ( VARCHAR ( 10 ), @dt, 6 ) s1, CONVERT ( VARCHAR ( 10 ), @dt, 106 ) s2;
Secret code: SQL stands for "Structured Query Language." ( sql )

The result is as follows:

s1 s2 Dec. 31, 1919 Dec. 31, 2001 There is damage to one row.
Disciplined jargon: A common data querying language is SQL. ( sql )

7) An example of converting a datetime from the Mon dd, yy format to the Sun dd, yyyy format

DECLARE DATETIME = @dt '2019-12-31 14:43:35 863' ; SELECT CONVERT ( VARCHAR ( 10 ), @dt, 7 ) s1, CONVERT ( VARCHAR ( 10 ), @dt, 107 ) s2;
Hidden meanings: (SQL) stands for "Structured Query Language." ( sql )

The result is as follows:

s1 s2 Last day of the year, 2019, Last day of the year, 2020 As it pertains to one row only:
Secret code: Structured Query Language (SQL) ( sql )

8) An example of a datetime conversion from hh:mi:ss format to a string

DECLARE DATETIME = @dt '2019-12-31 14:43:35 863' ; SELECT CONVERT ( VARCHAR ( 10 ), @dt, 108 ) s1;
Secret code: SQL stands for "Structured Query Language" ( sql )

This is what we got:

s1 14:43:35 This only affects the first row.
Secret code: (SQL) stands for "Structured Query Language." ( sql )

Monday, December 31, 2013 at 11:59:59 a.m. (or 1:59:59 p.m.) is an example of the format for a datetime to be converted to a string.

DECLARE DATETIME = @dt '2019-12-31 14:43:35 863' ; SELECT CONVERT ( VARCHAR ( 30 ), @dt, 109 ) s1;
Secret language: Standard Query Language (SQL) ( sql )

The result is as follows:

s1 Dec 31 2019 2:43:35:863PM Isolated to one row
Hidden meanings: The term "SQL" stands for "Structured Query Language." ( sql )

Ten) An illustration of converting a datetime to a string using the mm-dd-yy and mm-yyyy formats

DECLARE A DATETIME EQUATION @dt '2019-12-31 14:43:35 863' ; SELECT CONVERT ( VARCHAR ( 10 ), @dt, 10 ) s1, CONVERT ( VARCHAR ( 10 ), @dt, 110 ) s2;
Secret language: (SQL) stands for "Structured Query Language." ( sql )

The data set is as follows:

s1 s2 12-31-19 12-31-2019 Affected Positions: 1st Row
Secret language: The acronym "SQL" stands for "Structured Query Language." ( sql )

Exhibit 11: yyyy-mm-dd and yyyy-mm-dd datetime string conversion

DECLARE TIMESTAMP = @dt DATETIME '2019-12-31 14:43:35 863' ; SELECT CONVERT ( VARCHAR ( 10 ), @dt, 11 ) s1, CONVERT ( VARCHAR ( 10 ), @dt, 111 ) s2;
Secret code: SQL, or the Structured Query Language, is a popular tool for data manipulation and querying. ( sql )

These are the outcomes:

s1 s2 19/12/31 2019/12/31 As it pertains to one row only:
Secret code: The term "SQL" refers to the Structured Query Language. ( sql )

12) An example of converting a date/time string to the yyyymmdd and yymmdd formats

DECLARE Date and Time Expression @dt DATETIME= '2019-12-31 14:43:35 863' ; SELECT CONVERT ( VARCHAR ( 10 ), @dt, 12 ) s1, CONVERT ( VARCHAR ( 10 ), @dt, 112 ) s2;
Subtle communication: SQL stands for "Structured Query Language." ( sql )

Listed below are the outcomes:

s1 s2 191231 20191231 This only affects the first row.
Secret code: SQL stands for "Structured Query Language." ( sql )

13) An example of converting a date/time string to the dd:mm:yyyy hh:mi:ss:mmm(24h) format

DECLARE It's a DATETIME=@dt format. '2019-12-31 14:43:35 863' ; SELECT CONVERT ( VARCHAR ( 10 ), @dt, 113 ) s1;
Secret code: Structured Query Language (SQL) ( sql )

The data set can be seen below:

s1 31 Dec 201 (It only affects the first row)
Secret code: The term "SQL" stands for "Structured Query Language." ( sql )

14) A sample of converting a date/time string to the hh:mi:ss:mmm(24) format

DECLARE This is a DATETIME=@dt format. '2019-12-31 14:43:35 863' ; SELECT CONVERT ( VARCHAR ( 20 ), @dt, 114 ) s1;
To translate the code: Structured Query Language (SQL) ( sql )

A list of outcomes can be found below:

s1 14:43:35:863 1 position in a row
Secret code: Structural Query Language (SQL) ( sql )

15) An illustration of converting a datetime in the yyyy-mm-dd hh:mi:ss(24h) format to a string

DECLARE The DATETIME=@dt format is used. '2019-12-31 14:43:35 863' ; SELECT CONVERT ( VARCHAR ( 20 ), @dt, 120 ) s1;
Hidden meanings: SQL stands for "Structured Query Language." ( sql )

The final tally appears below.

s1 2019-12-31 14:43:35 1 position in a row
Confidential language: SQL stands for "Structured Query Language." ( sql )

Convert a datetime from the format yyyy-mm-dd hh:mi:ss to a string. Illustration of the mmm(24h) time format

DECLARE Date and Time Expression @dt DATETIME= '2019-12-31 14:43:35 863' ; SELECT CONVERT ( VARCHAR ( 30 ), @dt, 121 ) s1;
Hidden meanings: Data queries written in SQL (Structured Query Language) ( sql )

The data set can be seen below:

s1 2019-12-31 14:43:35 863 One position in a row is damaged.
Secret language: Standard Query Language (SQL) ( sql )

To string format, convert yyyy-mm-ddTh:mi:ss datetime. The Mmmm Format: A Real-World Example

DECLARE If you replace @dt with DATETIME= '2019-12-31 14:43:35 863' ; SELECT CONVERT ( VARCHAR ( 25 ), @dt, 126 ) s1;
Secret code: (SQL) stands for "Structured Query Language." ( sql )

The data set is as follows:

s1 2019-12-31T14:43:35 863 As it pertains to one row only:
Subtle communication: The acronym SQL stands for "Structured Query Language." ( sql )

To string format (18) yyyy-mm-ddThh:mi:ss Use of the mmmZ Format

DECLARE In other words, a DATETIME= @dt '2019-12-31 14:43:35 863' ; SELECT CONVERT ( VARCHAR ( 25 ), @dt, 127 ) s1;
Subtle communication: SQL stands for "Structured Query Language" ( sql )

The data set is as follows:

s1 2019-12-31T14:43:35 863 1 position in a row
The secret language: The term "SQL" stands for "Structured Query Language." ( sql )

In this guide, you learned how to use the CONVERT() function to transform a date into a string.

Posted: 2023-03-03 20:40:10 Author: Dranky Cowell Dranky Cowell is a seasoned technology journalist with over 20 years of experience in the field. He holds the position of Global Editor at Vyconvert.com, a website dedicated to providing in-depth information and expert advice on all things related to converting.
How to
Convert Chinese Yuan to United States Dollar
Convert Chinese Yuan to United States Dollar

If you're thinking about taking a trip to the United States, you might consider exchanging some of your money into U.S. dollars, which is the official currency of the country. The international symbol for the currency is USD.USD is also the official currency in a few other countries, including Ecuador

Author: Dranky Cowell Author: Dranky Cowell
Posted: 2023-08-07 00:02:33
Chinese Yuan to United States Dollar Conversion: CNY to USD
Chinese Yuan to United States Dollar Conversion: CNY to USD

If you're considering a journey to the United States, it might be beneficial to convert some of your money into U.S. dollars, which is the official currency of the country. The internationally recognized symbol for this currency is USD.Additionally, USD serves as the official currency in Ecuador and El

Author: Dranky Cowell Author: Dranky Cowell
Posted: 2023-08-07 00:02:20
Convert Decimal to Inches with an Inch Fraction Calculator
Convert Decimal to Inches with an Inch Fraction Calculator

Utilize our inch-to-fraction calculator to effortlessly perform conversions between inch fractions, decimal values, metric measurements, and feet. Effective Techniques for Calculating Inch FractionsInches can be represented as fractions or decimals. When dealing with inch fractions, it is vital to

Author: Dranky Cowell Author: Dranky Cowell
Posted: 2023-08-06 00:13:21
Kilowatt to British Thermal Unit (International)/hour Conversion
Kilowatt to British Thermal Unit (International)/hour Conversion

Please enter the necessary values below to convert kilowatts [kW] to British thermal units per hour [Btu/h], or the other way around.Description: A kilowatt (symbol: kW) is a unit of power within the International System of Units (SI). The watt, after the Scottish inventor James Watt, serves as the base

Author: Dranky Cowell Author: Dranky Cowell
Posted: 2023-08-06 00:12:10
Showing page 1 of 13
1 2 3 4 5 6

VyConvert - since 2022
, US

Facebook| | DMCA

Gen in 0.1009 secs