convert yyyy-mm-dd hh:mm:ss to yyyy-mm-dd in sql

Question

I need to convert yyyy-mm-dd hh:mm:ss.msec to yyyy-mm-dd in sql.

I tried

CONVERT(DATETIME,MIN(Vdaily_calender.VDC_day_date),120)

but getting same result.

Please advise

Thanks Ar

Solution 1

I suspect you are overthinking it. A simple cast as date will do the trick

Select Cast(SomeDateTime as date)

Solution 2

CAST(MyDateTimeField AS Date) AS MyDateField

Solution 3

Select Cast(SomeDate as datetime)

Solution 4

maybe you can use

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'

Solution 5

Since you need the date without the time, then a convert or cast to a date will do.

CONVERT(DATE,MIN(Vdaily_calender.VDC_day_date),20)

An example showing convert, cast, format:

declare @mydatetime datetime = '2016-10-17 15:30:45.123';

select
@mydatetime as MyDateTime, 
convert(date,@mydatetime) as ConvertedToDate,
convert(varchar(10),@mydatetime,21) as ConvertedToDateString,
cast(@mydatetime as date) as CastedAsDate,
cast(cast(@mydatetime as date) as varchar) CastedAsDateString,
format(@mydatetime,'yyyy-MM-dd') as FormattedToDateString; -- SQL Server 2012 or higher

View more solutions

Comments

[HABO]

Tip: When you ask database questions, tag the question with the database software you are using, e.g. sql-server-2014, and specify the data types of the values involved. Please read this for some additional tips.

[Joel Meza Baca]

could you explain to me why that parameter 120?