June 15, 2022 at 9:56 am
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?
June 15, 2022 at 10:04 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 15, 2022 at 10:53 am
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.
June 15, 2022 at 2:09 pm
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 17, 2022 at 6:40 am
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 monthand 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?)
June 17, 2022 at 6:41 am
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 monthand please please ignore that FORMAT option - it is very very slow and should not be used except for ad-hoc small volumes purposes.
-> noted
June 17, 2022 at 6:42 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply