Oracle to SQL Server: Month + Year where clause

Question

I have an Oracle Query that goes as follow:

SELECT ...
FROM ...
WHERE ...
    AND to_char(a.bill_Date, 'MMYYYY') = '072017'
    ...
ORDER BY ...

In SQL Server, we have the MONTH() and YEAR() functions.

SELECT ...
FROM ...
WHERE ...
    AND MONTH(a.BILL_DATE) = MONTH(GETDATE())
    AND YEAR(a.BILL_DATE) =  YEAR(GETDATE())
    ...
ORDER BY ...

Is there something that allows me to apply a Month-Year constraint like Oracle does with to_char through a single step? I have to make this parameterized and maintain compatibility with both databases through a common interface. Now, one would require two parameters and other would just one.

Possible duplicate of [Getting only Month and Year from SQL DATE](https://stackoverflow.com/questions/1781946/getting-only-month-and-year-from-sql-date) — Tim Biegeleisen, Aug 20 '17 at 06:33
Not as far as I know. The closest thing to Oracle's `TO_CHAR()` function would be SQL Server's `CONVERT()`. But this would generate a string with a day component. — Tim Biegeleisen, Aug 20 '17 at 06:34
I started off with CONVERT and ended up with a complex clause. You are correct. — DoomerDGR8, Aug 20 '17 at 06:35

Lukasz Szozda · Answer 1 · 2017-08-20T08:19:58.070

3

To get year and month in SQL Server (2012 and above) you could use FORMAT:

FORMAT ( value, format [, culture ] )

WHERE ...
  AND FORMAT(a.bill_Date, 'yyyyMM') = '201707';

--or

WHERE ...
  AND FORMAT(a.bill_Date, 'MMyyyy') = '072017';

Rextester Demo

Please keep in mind that FORMAT(a.bil_Date, 'MMyyyy') is not SARG-able so there will be no index usage.

edited Aug 20 '17 at 08:19

answered Aug 20 '17 at 08:15

Lukasz Szozda

162,964
23
234
275

1

Good answer, didn't think of using `FORMAT` +1 – Tim Biegeleisen Aug 20 '17 at 08:19

Tim Biegeleisen · Accepted Answer · 2017-08-20T06:43:22.097

2

I think the closest thing to Oracle's TO_CHAR function in SQL Server would be CONVERT. But even with CONVERT I see no option to request only the year and month. Rather, we would need to accept at least the day as well, and possibly a timestamp in addition to this.

CONVERT accepts a date format mask parameter as its third argument. The 112 mask returns a date only in the following format:

yyyymmdd

So for the date in your question it would return this:

201707dd

where I have left dd undefined as I don't know which day a given record might have. Now we can get close to what you want by taking the left 6 characters of this output, e.g.

WHERE ... AND
    LEFT(CONVERT(varchar, a.bill_Date, 112), 6) = '201707'

edited Aug 20 '17 at 06:43

answered Aug 20 '17 at 06:38

Tim Biegeleisen

502,043
27
286
360

Aim is to get data for the whole month. Don't want to attempt a BETWEEN – DoomerDGR8 Aug 20 '17 at 07:02
1

There is nothing wrong with using `BETWEEN` and in fact I believe that both `TO_CHAR` and `CONVERT` may preclude the possibility of using an index on your date column. Given your other constraints, this may be your only option, but it might not be the most performant one. – Tim Biegeleisen Aug 20 '17 at 07:16
@TimBiegeleisen I would say that in SQL Server `FORMAT` is closest to `TO_CHAR` :) – Lukasz Szozda Aug 20 '17 at 08:18

score 1 · Answer 3 · answered Dec 12 '19 at 18:45

You could rewrite your Oracle query to avoid data to char conversions:

SELECT ...
FROM ...
WHERE ...
    AND EXTRACT (MONTH FROM a.bill_Date) = 7 
    AND EXTRACT (YEAR FROM a.bill_Date) = 2017 
    ...
ORDER BY ...

and the equivalent SQL Server

SELECT ... 
FROM ... 
WHERE ...
        AND MONTH(a.bill_Date) = 7 
        AND YEAR(a.bill_Date) = 2017 
        ... 
ORDER BY ...

Oracle to SQL Server: Month + Year where clause

3 Answers3