CODEWITHSUNDEEP
sqlsql-serverdate
vishnudas
vishnudas

Reputation: 53

Comparing with date or datetime columns in SQL server

We are migrating from Oracle to SQL Server (using SSMA). In our Oracle environment, we were using the DATE datatype for some columns, during the datatype mapping for SQL Server we set DATETIME datatype for the corresponding columns.

Now, suppose I have a table as follows:

+--------+-------------+---------------------------+
| grn_id | grn_no      | grn_dt                    |
+--------+-------------+---------------------------+
| 1      | 0000000001  | 2018-01-03 00:00:00.000   |
| 2      | 0000000002  | 2018-01-03 00:00:00.000   |
| 3      | 0000000003  | 2018-01-03 00:00:00.000   |
| 4      | 0000000001  | 2018-01-27 00:00:00.000   |
+--------+-------------+---------------------------+

I'm trying to filter this data with grn_dt(DATATYPE IS DATETIME) with a string like '2018-01-27'. What is the right way to do this?

We have already used a convert function as follows:

SELECT * FROM grn_header WHERE convert(date,grn_dt) <= '2018-01-27'

Anyway, we are getting the required output, but is this conversion right?

Do I have to set the conversion for the string ('2018-01-27') also as follows?

SELECT * FROM grn_header WHERE convert(date,grn_dt) <= convert(date,'2018-01-27')

Is there any alternative way to do this?

Upvotes: 0

Views: 102

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

This is fine:

where convert(date, grn_dt) <= '2018-01-27'

SQL Server will use an index, if available, on grn_dt despite the convert().

I am also inclined to write this as:

where grn_dt < dateadd(day, 1, '2018-01-27')

Upvotes: 3

Related Questions