CODEWITHSUNDEEP
sqlsql-server
Hossam Oukli
Hossam Oukli

Reputation: 1296

How the select the date part from in sql server

I have this query in Postegre SQL:

SELECT * from myTable 
WHERE date(myTimeStampColumn) = '2019-01-01'

Since I switched to SQL, I use either this:

SELECT * 
FROM myTable 
WHERE (
        SELECT CONVERT(date, myDateTimeColumn)
      ) = '2019-01-01'

or this:

SELECT * 
FROM myTable 
WHERE date(myDateTimeColumn) > '2019-01-01'
  AND
SELECT * 
FROM myTable 
WHERE date(myDateTimeColumn) < '2019-01-02'

Is there a faster/simpler way to select the date part of datetime column in sqlserver ?

Upvotes: 0

Views: 210

Answers (2)

PSK
PSK

Reputation: 17943

Following query is equivalent to what you were writing in PostgreSQL

SELECT * FROM MYTABLE WHERE CAST(myTimeStampColumn AS DATE)='2019-01-01'

As suggested by Dan Guzman, above query is non-SARGable, so will not get the benefit of indexes.

For better performance you can write same query like following.

 SELECT * FROM MYTABLE WHERE
 myTimeStampColumn >= '2019-01-01' AND myTimeStampColumn < '2019-01-02'

Upvotes: 1

S.Jose
S.Jose

Reputation: 246

You can write the query as below

select * from myTable where CONVERT(date, myDateTimeColumn) = '2019-01-01'

Upvotes: 1

Related Questions