How to Convert DateTime to String in SQL Server

Do you know how to convert Datetime to string in SQL Server? If not, follow this tutorial to learn how. We can use the CONVERT and CAST functions to convert a value(of any datatype) into a specified datatype. Continue reading the tutorial to know more.

Convert Datetime using the CAST function

To convert Datatime to string using the CAST function, use the syntax below.

• EXPRESSION – It represents value that needs to be converted.
• DATATYPE – The type of data we want to convert our expression.
• length – It represents the length of the resulting datatype(optional).

CAST(EXPRESSION AS DATATYPE(length))

Convert Datetime using the Convert function

CONVERT(VARCHAR, datetime [,style])

• VARCHAR – It represents the string type.
• datetime – The expression checks the date or datetime that you want to convert into a string.
• Style – This parameter specifies the date format. This is optional, and SQL Server predefined its value.

Without Century	With Century	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

Convert Datetime to String SQL

Here I have the table called smartproduct where I have productid, productname, orderdate.

1. Create the table
2. Insert the values
3. Show the table

CREATE TABLE smartproduct (prod_id INT Primary key,
                     prod_name VARCHAR(25),
                     order_date DATE);


INSERT INTO smartproduct VALUES (101, 'iPhone', '2020-07-20'),
                          (102, 'iPad', '2018-01-01'),
                          (103, 'iWatch', '2019-03-15'),
                          (104, 'iMac', '2016-05-13');
         

select * from  smartproduct

• Now, we are using the convert function in SQL Server to convert datetime to string. s1 is optional, that is, column name. If you are not specifying anything, it will reflect as No column name.

DECLARE @dt DATETIME = (SELECT order_date 
FROM smartproduct WHERE prod_id = 101);

SELECT 
    CONVERT(VARCHAR(20),@dt,0) s1;

• If I want to change 0 to 1, then the output will be shown as, mm/dd/yy.

DECLARE @dt DATETIME = (SELECT order_date 
FROM smartproduct WHERE prod_id = 101);

SELECT 
    CONVERT(VARCHAR(20),@dt,1) s1;

• Use the CAST function to change from datetime to String in SQL Server.

/*Declaring DATETIME as dt*/
DECLARE @dt DATETIME = (SELECT order_date 
FROM smartproduct WHERE prod_id = 102);
/*SELECT statement is used to print the s1 message*/
SELECT 
    CAST(@dt AS DATETIME) s1;

Advantages of converting data type in SQL Server

Below are the major advantages of converting datatype in SQL Server,

• Ensure Correct Data Storage – Converting data from one data type to another is stored correctly. For example, storing Datetime prevents invalid date entries.
  Optimized Index Usage – When data is stored in its proper type, SQL Server can make better use of indexes, which speeds up query performance.
• Efficient Data Retrieval- Operations on properly typed data (like date comparisons or mathematical calculations) are faster and more efficient, as SQL Server optimizes execution plans based on data types.
• Advanced Query Capabilities- Conversions allow for more advanced query constructs and logic, enabling complex data transformations and conditional processing.

Conclusion

Converting data types in SQL Server is important for maintaining data integrity, improving performance, ensuring compatibility, and enhancing the overall capability of SQL operations. Using the proper conversions will help your database run correctly, quickly, and related to industry standards.

Read also;

• SQL Server Replace Function
• How to Create Functions in SQL Server
• How to Convert Int to Varchar in SQL Server