How to get datepart from datetime in a Column

Question

I want to convert the column (EXECUTION_LOCAL_DATE_TIME) which has datetime format as (YYYY-MM-DD hh:mm:ss.nnnnnnn) to format (YYYY-MM-DD). Ho do i get this. I am working on SQL server management studio

Answer 1

If your intention is to just get the DATE part of a DATETIME then you can just convert the format to DATE (note that this will return a 'Date' datatype, not specifically formatted to a string 'YYYY-MM-DD'.)

eg:

DECLARE @Dt DATETIME = '2019-01-25T12:00:00'
SELECT CONVERT(DATE, @Dt)

Will return '2019-01-25'

Answer 2

You want the CAST() function:

https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

Answer 3

Ideally you should return the datetime format from your query and let your presentation layer handle formatting.

However if you are using SQL server 2012 or higher then you can use the Format() function. See the below answer:

Convert Date format into DD/MMM/YYYY format in SQL Server

Answer 4

If you're using SQL Server 2012 or higher you can use the FORMAT() function.

In your case you'd need

SELECT FORMAT(EXECUTION_LOCAL_DATE_TIME, 'yyyy-MM-dd') FROM TABLE_NAME

You can find additional info here

https://www.mssqltips.com/sqlservertip/2655/format-sql-server-dates-with-format-function/ https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-2017

Answer 5

Here Is the Code Worked For me

SELECT convert(varchar, EXECUTION_LOCAL_DATE_TIME, 111) from Tablename

The Execution_Local_Date_Time will be Converted to yyyy/mm/dd format.