Oracle to MS SQL

Question

Post reply

Oracle to MS SQL

TheNewbie

Old Hand

Points: 361
More actions
June 15, 2022 at 9:56 am

#4049789
Hi,
I'm converting Oracle Queries into MS SQL and there are fucntions which are specific only to Oracle.
I'm struggling on a query which in the WHERE-Clause I have this (tab is DATETIME2)
```
select tab.a
tab2.b
tab3.c
from tab, tab2, tab3
Where TO_CHAR(tab.x, 'MMYYYY') = TO_CHAR(trunc(Sysdate,'MM')-1,'MMYYYY'))
```
I'm not pretty sure what does this is about but I think its trying to truncate the Month, right?(Can you explain it to me further?)
I tried this
```
FORMAT(tab.x, 'yyyy-MM') = CONVERT(DATETIME, CONVERT(VARCHAR(7), GETDATE(), 120) + '-01')
```
but didn't work.
Any help?
- This topic was modified 2 years, 5 months ago by TheNewbie.
- This topic was modified 2 years, 5 months ago by TheNewbie.
- This topic was modified 2 years, 5 months ago by TheNewbie.
- This topic was modified 2 years, 5 months ago by TheNewbie. Reason: tab 1 instead of tab

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246963 More actions · Answer 1

If you can explain the requirement in English, I'm sure that someone here will help with the T-SQL.

It would also be useful to know the datatype of tab.x.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

frederico_fonseca SSCoach Points: 16155 More actions · Answer 2

TO_CHAR(trunc(Sysdate,'MM')-1,'MMYYYY')) - this is returning the first day of the current month (trunc(sysdate, 'MM) minus 1 day (-1) in format MMYYYY

TO_CHAR(tab.x, 'MMYYYY') is returning the value of tab.x on format MMYYYY

so basically the code is returning all rows where the year+month is equal to the previous month (and year if so happens)

assuming your tab.x data type is SQLserver is a date/datetime then you should change the code so that it uses proper date comparison (which will be sargeable) instead of the conversion of tab.x to a string to do the compare

tab.x >=  dateadd(mm, datediff(mm, 0, getdate()) - 1, 0) -- Beginning of previous month
and tab.x < dateadd(mm, datediff(mm, 0, getdate()), 0)     -- Beginning of this month

and please please ignore that FORMAT option - it is very very slow and should not be used except for ad-hoc small volumes purposes.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

You want to look at date functions like EOMONTH. Here are some samples that are pretty much what you're looking for.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

TheNewbie Old Hand Points: 361 More actions · Answer 4

frederico_fonseca wrote:

TO_CHAR(trunc(Sysdate,'MM')-1,'MMYYYY')) - this is returning the first day of the current month (trunc(sysdate, 'MM) minus 1 day (-1) in format MMYYYY
so basically the code is returning all rows where the year+month is equal to the previous month (and year if so happens)
--> Thank you for this explanation
TO_CHAR(tab.x, 'MMYYYY') is returning the value of tab.x on format MMYYYY (-> I already knew this one 🙂 )
assuming your tab.x data type is SQLserver is a date/datetime then you should change the code so that it uses proper date comparison (which will be sargeable) instead of the conversion of tab.x to a string to do the compare
tab.x >=  dateadd(mm, datediff(mm, 0, getdate()) - 1, 0) -- Beginning of previous month
and tab.x < dateadd(mm, datediff(mm, 0, getdate()), 0)     -- Beginning of this month
and please please ignore that FORMAT option - it is very very slow and should not be used except for ad-hoc small volumes purposes.
-> noted (what should I use then please?)

This reply was modified 2 years, 5 months ago by TheNewbie.
This reply was modified 2 years, 5 months ago by TheNewbie.

TheNewbie Old Hand Points: 361 More actions · Answer 5

frederico_fonseca wrote:

TO_CHAR(trunc(Sysdate,'MM')-1,'MMYYYY')) - this is returning the first day of the current month (trunc(sysdate, 'MM) minus 1 day (-1) in format MMYYYY
so basically the code is returning all rows where the year+month is equal to the previous month (and year if so happens)
--> Thank you for this explanation

TO_CHAR(tab.x, 'MMYYYY') is returning the value of tab.x on format MMYYYY (-> I already knew this one 🙂 )
assuming your tab.x data type is SQLserver is a date/datetime then you should change the code so that it uses proper date comparison (which will be sargeable) instead of the conversion of tab.x to a string to do the compare
tab.x >=  dateadd(mm, datediff(mm, 0, getdate()) - 1, 0) -- Beginning of previous month
and tab.x < dateadd(mm, datediff(mm, 0, getdate()), 0)     -- Beginning of this month
and please please ignore that FORMAT option - it is very very slow and should not be used except for ad-hoc small volumes purposes.
-> noted

This reply was modified 2 years, 5 months ago by TheNewbie. Reason: can an admin delete this response?

TheNewbie Old Hand Points: 361 More actions · Answer 6

Grant Fritchey wrote:

You want to look at date functions like EOMONTH. Here are some samples that are pretty much what you're looking for.

Thanks for the link