Timestamp in the SQL Server where clause

Oracle to SQL Server: month + year where clause - SQL-Server, Oracle, Tsql, To-Char

I have an Oracle query that reads as follows:

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

Is there something that will allow me to apply a month-year constraint like Oracle with to_char in a single step? I need to parameterize this and maintain compatibility with both databases over a common interface. Now you would need two parameters and the other only one.

Reply:

2 for the answer № 1

I think that would be the closest Oracle function in SQL Server comes in. But even with I don't see any possibility of just requesting the year and month. Rather, we would at least have to accept the day and possibly also a time stamp.

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

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

where i went undefined as i don't know what day a particular record might have. Now we can approach what you want by using the left 6 characters of this output e.g.


3 for the answer № 2

To get the year and month in SQL Server (2012 and above) you can use:

FORMAT (value, format [, culture])

Rextester demo

Please remember that it is not SARG-compatible, so no index is used.