CODEWITHSUNDEEP
sql-server
Vannessa
Vannessa

Reputation: 183

Omit time in datetime type in SQL Server

I would like to compare both dates in 2 columns SHIFTA_Start and SHIFTA_End.
However each column contains both date and time fields,
I just want to compare the dates, and omit the time.

Image of Date and Time in Datetime Datatype:

enter image description here

Upvotes: 0

Views: 112

Answers (3)

Mike D.
Mike D.

Reputation: 4104

If you aren't fussed about the data type the easiest way is probably to CAST the value to a DATE data type.

However, if you want to keep the DATETIME data type but just flatten out the time portion my preferred method is to use DATEADD & DATEDIFF from a fixed point.

E.g.:

DATEADD(DD, DATEDIFF(DD, 0, DateField), 0)

This takes the number of days between date 0 and the DateField and then adds that back to date 0 effectively removing the time portion. You can also alter the interval portion of the DATEADD and DATEDIFF functions to flatten the value to the month or year. Or add a fixed integer to the DATEDIFF value to get a certain date in the future (without the time).

E.g.:

DATEADD(DD, DATEDIFF(DD, 0, DateField) + 1, 0)

Will return the tomorrow relative to DateField but set to 00:00:00.

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can CAST them to DATE

SELECT 
    CAST(SHIFTA_Start AS DATE),
    CAST(SHIFTA_End AS DATE)

If you're not allowed to CAST to DATE due to having a lower version of SQL Server, you can use DATEADD and DATEDIFF:

SELECT 
    DATEADD(DAY, DATEDIFF(DAY, '19000101', SHIFTA_Start), '19000101'),
    DATEADD(DAY, DATEDIFF(DAY, '19000101', SHIFTA_End), '19000101')

Upvotes: 1

Abdul Rasheed
Abdul Rasheed

Reputation: 6709

You can CAST them DATE to compare

CAST(SHIFTA_Start AS DATE) ,
CAST(SHIFTA_End AS DATE)

Upvotes: 0

Related Questions