What is best way to convert datetime to date in Sql Server?

Dung Do Tien Feb 01 2022 51

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:

What is best way to convert datetime to date in Sql Server?

How can I convert datetime to date data type in Sql Server?

Thanks for any suggestions.

Have 2 answer(s) found.
  • A

    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/2022 

    What 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:

    StyleHow it’s displayed
    101mm/dd/yyyy
    102yyyy.mm.dd
    103dd/mm/yyyy
    104dd.mm.yyyy
    105dd-mm-yyyy
    110mm-dd-yyyy
    111yyyy/mm/dd
    106dd mon yyyy
    107Mon 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.UsedId

    I hope it helpful for you.

  • k

    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.

Leave An Answer
* NOTE: You need Login before leave an answer

* Type maximum 2000 characters.

* All comments have to wait approved before display.

* Please polite comment and respect questions and answers of others.

Popular Tips

X Close