SQL Query to Convert Datetime to String
Last Updated :
25 Oct, 2021
In order to convert a DateTime to a string, we can use CONVERT() and CAST() function. These functions are used to converts a value(of any datatype) into a specified datatype.
CONVERT() Function
Syntax:
CONVERT(VARCHAR, datetime [,style])
- VARCHAR – It represent the string type.
- datetime – It can be the expression that evaluates date or datetime value that you want to convert into string.
- style – It specifies the format of the date. It’s value is predefined by the SQL Server. The style parameter is optional.
CAST() Function
Syntax:
CAST(EXPRESSION AS DATATYPE(length))
- EXPRESSION – It represent the value that need to be converted.
- DATATYPE – It is the type of the data to which we want to convert our expression.
- length – It represent the length of the resulting datatype(optional).
Without
Century (YY)
|
With
Century(YYYY)
|
Standard
|
Format
|
–
|
0 or 100
|
Default for datetime
and smalldatetime
|
mon dd yyyy
hh:miAM (or PM)
|
1
|
101
|
U.S.
|
1 = mm/dd/yy
101 = mm/dd/yyyy
|
2
|
102
|
ANSI
|
2 = yy.mm.dd
102 = yyyy.mm.dd
|
3
|
103
|
British/French
|
3 = dd/mm/yy
103 = dd/mm/yyyy
|
4
|
104
|
German
|
4 = dd.mm.yy
104 = dd.mm.yyyy
|
5
|
105
|
Italian
|
5 = dd-mm-yy
105 = dd-mm-yyyy
|
6
|
106
|
–
|
6 = dd mon yy
106 = dd mon yyyy
|
7
|
107
|
–
|
7 = Mon dd, yy
107 = Mon dd, yyyy
|
8
|
108
|
–
|
hh:mm:ss
|
In the below example, we will convert the DateTime into a string in different formats.
Step 1: Create a database
Query:
CREATE DATABASE Product_order;
Figure: Create DATABASE
Step 2: Create a table
Now, we need to create a table inside our database. For this, we will use CREATE statement.
Query:
CREATE TABLE orders (prod_id INT,
prod_name VARCHAR(255),
order_date DATE,
PRIMARY KEY(prod_id));
Figure: Create a table orders
Step 3: Insert data into a table
In this step, we will insert data inside our orders table. For inserting data we will use an INSERT statement.
Query:
INSERT INTO orders VALUES (101, 'iPhone', '2020-07-20'),
(102, 'iPad', '2018-01-01'),
(103, 'iWatch', '2019-03-15'),
(104, 'iMac', '2016-05-13');
Figure: Insert data into the order table
Step 4: In order to verify the contents of the table, we will be using the SELECT statement.
SELECT * FROM orders;
Figure: Select statement query
Output:
Figure: Order table
Step 5: Using CONVERT() function
Query :
/*Declaring DATETIME as dt*/
DECLARE @dt DATETIME = (SELECT order_date
FROM orders WHERE prod_id = 101);
/*SELECT statement is used to print the s1 message*/
SELECT
CONVERT(VARCHAR(20),@dt,0) s1;
Figure: CONVERT() function query
Output:
Figure: Output
Query :
In this, we are changing the style parameter to 1. Similarly, you can use different style parameter values from the above table.
/*Declaring DATETIME as dt*/
DECLARE @dt DATETIME = (SELECT order_date
FROM orders WHERE prod_id = 103);
/*SELECT statement is used to print the s1 message*/
SELECT
CONVERT(VARCHAR(20),@dt,1) s1;
Figure: CONVERT() function query
Output :
Figure: Output
Step 6: Using CAST() function
Query:
/*Declaring DATETIME as dt*/
DECLARE @dt DATETIME = (SELECT order_date
FROM orders WHERE prod_id = 102);
/*SELECT statement is used to print the s1 message*/
SELECT
CAST(@dt AS DATETIME) s1;
Figure: CAST() function query
Output:
Figure: Output
Share your thoughts in the comments
Please Login to comment...