What is best way to convert datetime to date in Sql Server?
Dung Do Tien
Feb 01 2022
49
Hello Guys, I'm using MSSQL 2019 and I have a table name News and its content CreatedDate column(DateTime data type). Now I want to select and format that column with format MM/dd/yyyy. And I tried to cast it CAST(n.CreateDate as DATE) as below:
SELECT n.Id, n.Title, n.Sapo, n.Content, n.NewsType,
CAST(n.CreateDate as DATE) as CreatedDate,
m.Id as UserId, m.FullName
FROM News n
INNER JOIN Member m ON m.Id = n.UsedId
But it's return default with format yyyy-MM-dd as the image below:
How can I convert datetime to date data type in Sql Server?
Thanks for any suggestions.
Have 2 answer(s) found.
-
A0
ANDREW Feb 01 2022
You can use
Convert()method to help convert DateTime to string with some format you want.For example:
SELECT CONVERT(VARCHAR(10), getdate(), 101); --=> return: 02/01/2022What is the mean of
101? This is a style of format, there are many other styles you can choose from. Here are some common types:Style How it’s displayed 101 mm/dd/yyyy 102 yyyy.mm.dd 103 dd/mm/yyyy 104 dd.mm.yyyy 105 dd-mm-yyyy 110 mm-dd-yyyy 111 yyyy/mm/dd 106 dd mon yyyy 107 Mon dd, yyyy And below is the solution for your query:
SELECT n.Id, n.Title, n.Sapo, n.Content, n.NewsType, CONVERT(VARCHAR(10), n.CreateDate, 101) as CreatedDate, m.Id as UserId, m.FullName FROM News n INNER JOIN Member m ON m.Id = n.UsedIdI hope it helpful for you.
-
k0
khossain Feb 01 2022
You can try to use two ways below:
SELECT TRY_CONVERT(VARCHAR(10), getdate(), 101); -- OR SELECT CONVERT(VARCHAR(10), getdate(), 101);All of them return the same value is 02/02/2022.
* Type maximum 2000 characters.
* All comments have to wait approved before display.
* Please polite comment and respect questions and answers of others.