Oracle to SQL Server: Month + Year where clause

extract(month and year from date in oracle)
oracle extract(month from date)
oracle get month number from date
how to get monthly data in oracle
how to get previous month in oracle
oracle month in text
how to get current month and year in oracle sql
extract date from datetime in oracle

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.

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'

MONTH function, The argument must be a date, timestamp, or a valid character string representation of a date or timestamp that is not a CLOB, LONG VARCHAR, or XML value. Summary: in this tutorial, you will learn how to use the SQL Serer EOMONTH() function to get the last day of the month of a specified date.. SQL Server EOMONTH() overview. The EOMONTH() function returns the last day of the month of a specified date, with an optional offset.

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.

EXTRACT (datetime), Database SQL Reference. Contents In these cases, Oracle Database returns UNKNOWN (see the examples that follow for additional information). The field For example, you can extract only YEAR , MONTH , and DAY from a DATE value. If the resulting month has fewer days than the day in the input value, then both Oracle ADD_MONTHS and SQL Server DATEADD set the day to the last day of the resulting month: For example, adding 1 month to January 31, 2012 gives February 29, 2012:

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 ...

ADD_MONTHS Function - Oracle to SQL Server Migration, ADD_MONTHS Function - Oracle to SQL Server Migration. In Oracle, ADD_MONTHS function adds the specified number of months to a datetime value. In SQL Server, you can use DATEADD function. Note that the output format for DATE values depends on the NLS_DATE_FORMAT session variable. In these cases, Oracle Database returns UNKNOWN (see the examples that follow for additional information). The field you are extracting must be a field of the datetime_value_expr or interval_value_expr. For example, you can extract only YEAR, MONTH, and DAY from a DATE value.

MONTHS_BETWEEN Function, In Oracle, MONTHS_BETWEEN(date1, date2) function returns the number of months between two dates as a decimal number. Note that SQL� In Oracle, TRUNC(datetime, unit) function allows you to truncate a datetime value to the specified unit (set zero time, set the first day of the month i.e).In SQL Server, you can use various expressions using CONVERT function to get the same result.

SQL Server vs Oracle: End of Month – SQL TECH BLOG, Continuing my series on SQL Server and Oracle, I thought I'd highlight a function that has been in Oracle from some time and has only just� Sometimes when working with SQL Server (or any other DBMS for that matter), you need to return the short name for a month. By this I mean the 3 letter abbreviation of a month. For example, you need “Dec” instead of “December”. Here are four ways you can extract the shortened month name from a date in SQL Server. The FORMAT() Function

SQL Server MONTH() Function, Definition and Usage. The MONTH() function returns the month part for a specified date (a number from 1 to 12). Syntax. MONTH(date). Parameter Values � The MONTH() function returns the month part for a specified date (a number from 1 to 12). Syntax. SQL Server (starting with 2008), Azure SQL Database, Azure SQL

Comments
  • Possible duplicate of Getting only Month and Year from SQL DATE
  • 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.
  • I started off with CONVERT and ended up with a complex clause. You are correct.
  • Aim is to get data for the whole month. Don't want to attempt a BETWEEN
  • 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.
  • @TimBiegeleisen I would say that in SQL Server FORMAT is closest to TO_CHAR :)
  • Good answer, didn't think of using FORMAT +1