Oracle convert to SQL dates

Transact-SQL
1

Hi , I have below oracle that I am converting to SQL But I get error ---> Msg 8116, Level 16, State 1, Line 15
Argument data type varchar is invalid for argument 3 of convert function.

what am I doing wrong?
Thanks in advance!
Pasi

( ( to_char(SYSDATE, 'MM') between '01' and '03') AND ---Oracle
--( to_char(E.FDT, 'MM') between '10' and '12') AND ---Oracle
-- ( to_char(E.FDT, 'YYYY') = (to_char(sysdate,'YYYY') - 1)) ) ---Oracle

error---> ( ( Convert(VARCHAR,GETDATE(), 'MM') between '01' and '03') AND
( convert(varchar,E.FDT, 'MM') between '10' and '12') AND
( convert(varchar,E.FDT, 'YYYY') = (Convert(VARCHAR,GETDATE(),'YYYY') - 1)) )

2

Don't use VARCHAR with no size parameter. The default size is an accident waiting to happen

'YYYY' is not a valid parameter #3 for CONVERT(). See the DOCs

3

If that is checking the Month then suggest you use DATEPART

Converting a Date column and GetDate to text in order to check if the year is the same will be very inefficient in MS SQL. If you just want to know if it is current year then "rounding" the date to the year only uses integer maths and is very quick. If you want to compare two dates to see if they have the same year then probably DATEPART is best.

4

Thanks Kristen, I just dont know how to construct the statement? I have not used DATEPART

This is what I used but it does not like the "between"? I know you said not to use VARCHAR but not sure what I can use?

Thanks.

(CONVERT(varchar(2), DATEPART(MONTH, 'MM') between '01' and '03')

5

As per the DOCs

parameter #2 is a date, not a string.

6

This is how I constructed it but I get no data?

( to_char(SYSDATE, 'MM') between '01' and '03') AND ---Oracle
--( to_char(E.FDT, 'MM') between '10' and '12') AND ---Oracle
-- ( to_char(E.FDT, 'YYYY') = (to_char(sysdate,'YYYY') - 1)) ) ---Oracle

(  ( CONVERT(char,DATEPART(MONTH,GETDATE())) between '01' and '03') AND
 ( CONVERT(char, DATEPART(MONTH,EH.EFFDT)) between '10' and '12') AND
	   ( CONVERT(CHAR, DATEPART(YEAR,EH.EFFDT)) = CONVERT(CHAR, DATEPART(YEAR, GETDATE())) - 1) )
7

This applies to CHAR too

But don't convert to String to do a numeric comparison, DATEPART returns INT so just make an arithmetic comparison.

If you convert to String (unnecesarily) you will slow down your query.

Personally I think you need to figure out what this is trying to do, rather than converting it "as is". The individual bits are going to be slow and horrible in MS SQL.

Looks to me as though it selects when the current date is in the first 3 months of the year, and the EH.EFFDT is the last 3 months of year, and is in the current year. So actually all you need to do is check that EH.EFFDT is in the last three months of the current year (assuming I have understood the Oracle correctly). That would be SARGable using an index, which will run like grease-lightning rather than a straight conversion of the original Oracle.

Unless you need to conversion to perform poorly of course ...

8

SELECT cols
FROM tables

WHERE MONTH(GETDATE()) BETWEEN 1 AND 3 AND
    EH.EFFDT >= DATEADD(MONTH, 9, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0))  AND
    EH.EFFDT <  DATEADD(MONTH, 12, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0))
9

Thanks Scott, Let me try this.. this is what I was looking for! Didn't know how to construct it. So As I understand this is getting EFFDT between sep and last day in Dec which is (-1) is this correct?
Pasi.

10

The "last day in December" is calculated as: "Find the first day of the year, and add 12 months to that" and then "only include dates that are LESS than that".

< ENDPOINT is preferred to <= ENDPOINT for dates because IF they include BOTH Date AND Time then it is difficult to provide an endpoint-time that is the last-possible-value in the day (it depends on the precision of the value and the datatype). So "Less than one day past the endpoint" is much easier, precise and unambiguous.

11

No, it looks for dates >= Oct 1 of the prior year and less than Jan 1 of the next year, i.e., thru Dec 31.

When doing date or datetime ranges, it's best and most accurate to use less than the next time period rather than <=.

For example:
a smalldatetime in SQL Server is only accurate to 1m. So the latest time in the year is:
12-31-yyyy 23:59:59
a datetime in accurate to 3ms. So the latest time in the year is:
23:59:59.997
for datetime2, it's:
23:59:59.999999 (idk if it's 6 or 7 decimals, but you get the point).

So trying to code <= '12-31-yyyy <end_time>' is very tricky, especially if, say, the column changes to a datetime2 from a datetime.

So, to avoid all such problems, it's easiest to just say:
< Jan-01-yyyy+1

12

Thanks Scott , Make sense in the oracle code is basically saying :slight_smile:

to_char(SYSDATE, 'MM') between '01' and '03') AND ---Oracle ----------> first quarter
--( to_char(EH.EFFDT, 'MM') between '10' and '12') AND ---Oracle between Oct-Dec
-- ( to_char(EH.EFFDT, 'YYYY') = (to_char(sysdate,'YYYY') - 1)) ) ---Oracle ----Previous year

13

Yes. My code is doing exactly the same thing, but while also keeping the comparison on EH.EFFDT "sargable" for possible efficiency gains; big gains if the table is clustered on that date.

14

Ok got it. Thanks again for the help!

15

I'm curious how efficient that "format date to string" and then do "string range test" is in Oracle.

Over the years I've seen quite a number of questions about conversion of Oracle code to MS SQL, and that type of use of to_char() comes up quite often, and I've always assumed "that's just how it is in Oracle", but like-for-like doing date comparison in a similar way in MS SQL would be horrific (performance-wise), I wonder why that is not a performance issue in Oracle too? Maybe it is ..

But of course, except for old-hands, MS SQL constructions like

 DATEADD(MONTH, 12, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0))

are pretty obtuse!

16

Hello Scott I have put together your suggestion and I am not getting data at all? can you please take a look and see what I have done wrong? I am only getting the column headers??

image

Thanks!
Pasi.

SELECT EH.COMPANY, EH.COMPANY_NAME, EH.EMPLID, EH.NAME, EH.REHIRE_DT, EH.EFFDT,
EH.HIRE_DT
FROM EMPL_HIST EH , EMPL_CURR EC
WHERE
EH.ACTION IN ('HIR', 'REH')
AND EH.COMPANY IN ('01', '10', '15')
AND EH.EMPL_STATUS IN ('A', 'L', 'P', 'S')
AND
(

-- (  ( to_char(SYSDATE, 'MM') between '01' and '03') AND  -- Oracle
--   ( to_char(EH.EFFDT, 'MM') between '10' and '12')  AND    ---Oracle
  --- ( to_char(EH.EFFDT, 'YYYY') =  (to_char(sysdate,'YYYY') - 1))  )    ---Oracle

MONTH(GETDATE()) BETWEEN 01 AND 03 AND
EH.EFFDT >= DATEADD(MONTH, 10, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0)) AND
EH.EFFDT < DATEADD(MONTH, 12, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0))

OR

--( (to_char(SYSDATE, 'MM') between '04' and '06') AND --Oracle
-- ( to_char(EH.EFFDT, 'MM') between '01' and '03' ) AND ---Oracle
-- ( to_char(EH.EFFDT, 'YYYY') = to_char(sysdate,'YYYY')) ) ---Oracle

MONTH(GETDATE()) BETWEEN 04 AND 06 AND
EH.EFFDT >= DATEADD(MONTH, 01, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) AND
EH.EFFDT < DATEADD(MONTH, 03, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))

--( (to_char(SYSDATE, 'MM') between '07' and '09') AND --Oracle
-- ( to_char(EH.EFFDT, 'MM') between '04' and '06' ) AND --Orcale
-- ( to_char(EH.EFFDT, 'YYYY') = to_char(sysdate,'YYYY')) ) --Oracle
OR

 MONTH(GETDATE()) BETWEEN 07 AND 09 AND
 EH.EFFDT >= DATEADD(MONTH, 04, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))  AND
 EH.EFFDT <  DATEADD(MONTH, 06, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))

--( (to_char(SYSDATE, 'MM') between '10' and '12') AND --Oracle
-- ( to_char(EH.EFFDT, 'MM') between '07' and '09' ) AND --Oracle
-- ( to_char(EH.EFFDT, 'YYYY') = to_char(sysdate,'YYYY')) ) --Oracle

 OR
 MONTH(GETDATE()) BETWEEN 10 AND 12 AND
 EH.EFFDT >= DATEADD(MONTH, 07, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))  AND
 EH.EFFDT <  DATEADD(MONTH, 09, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) 

-

)
AND EC.EMPL_STATUS IN ('A', 'L', 'P', 'S')
AND EC.EMPLID = EH.EMPLId

17

You are mixing AND and OR without parenthesis to clarify.

"I'd like a hamburger and fries or hash browns"

You are presumably NOT expecting to JUST get hash browns? ...

"I'd like a hamburger and (fries or hash browns)"

unambiguous

18

I like hamburger fries and hasbrown all together. :slight_smile: I have tried with Parenthesis but still getting no results?

Pasi

Blockquote

SELECT EH.COMPANY, EH.COMPANY_NAME, EH.EMPLID, EH.NAME, EH.REHIRE_DT, EH.EFFDT,
EH.HIRE_DT
FROM EMPL_HIST EH , EMPL_CURR EC
WHERE
EH.ACTION IN ('HIR', 'REH')
AND EH.COMPANY IN ('01', '10', '15')
AND EH.EMPL_STATUS IN ('A', 'L', 'P', 'S')
AND
(

-- ( ( to_char(SYSDATE, 'MM') between '01' and '03') AND -- Oracle
-- ( to_char(EH.EFFDT, 'MM') between '10' and '12') AND ---Oracle
--- ( to_char(EH.EFFDT, 'YYYY') = (to_char(sysdate,'YYYY') - 1)) ) ---Oracle

(
(MONTH(GETDATE()) BETWEEN 01 AND 03) AND
EH.EFFDT >= DATEADD(MONTH, 10, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0)) AND
EH.EFFDT < DATEADD(MONTH, 12, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0))
)

OR
--( (to_char(SYSDATE, 'MM') between '04' and '06') AND --Oracle
-- ( to_char(EH.EFFDT, 'MM') between '01' and '03' ) AND ---Oracle
-- ( to_char(EH.EFFDT, 'YYYY') = to_char(sysdate,'YYYY')) ) ---Oracle

(
( MONTH(GETDATE()) BETWEEN 04 AND 06) AND
EH.EFFDT >= DATEADD(MONTH, 01, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) AND
EH.EFFDT < DATEADD(MONTH, 03, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
)

--( (to_char(SYSDATE, 'MM') between '07' and '09') AND --Oracle
-- ( to_char(EH.EFFDT, 'MM') between '04' and '06' ) AND --Orcale
-- ( to_char(EH.EFFDT, 'YYYY') = to_char(sysdate,'YYYY')) ) --Oracle

OR

(
( MONTH(GETDATE()) BETWEEN 07 AND 09) AND
EH.EFFDT >= DATEADD(MONTH, 04, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) AND
EH.EFFDT < DATEADD(MONTH, 06, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
)

--( (to_char(SYSDATE, 'MM') between '10' and '12') AND --Oracle
-- ( to_char(EH.EFFDT, 'MM') between '07' and '09' ) AND --Oracle
-- ( to_char(EH.EFFDT, 'YYYY') = to_char(sysdate,'YYYY')) ) --Oracle

OR

(
(MONTH(GETDATE()) BETWEEN 10 AND 12) AND
EH.EFFDT >= DATEADD(MONTH, 07, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) AND
EH.EFFDT < DATEADD(MONTH, 09, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
)

)
AND EC.EMPL_STATUS IN ('A', 'L', 'P', 'S')
AND EC.EMPLID = EH.EMPLId

19

Looks pretty good overall, although the staring month needs to add 1 less than what you are adding. If you go back, you'll see I added "9" months not "10" for the starting date. Don't forget, the minimum month is month 1, not month 0, so you have to add one less. For the ending month, you do add the full amount, in this case 12, since the comparison is less than the first day of the next month.

(
(MONTH(GETDATE()) BETWEEN 01 AND 03) AND
EH.EFFDT >= DATEADD(MONTH, 09, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0)) AND
EH.EFFDT < DATEADD(MONTH, 12, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0))
)

OR

(
( MONTH(GETDATE()) BETWEEN 04 AND 06) AND
EH.EFFDT >= DATEADD(MONTH, 00, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) AND
EH.EFFDT < DATEADD(MONTH, 03, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
)

...

20

Thanks Scott, Really appreciate. so I should change the starting month for other statement as well correct, I know you did 2 of them but not clear if other are ok or not?
Pasi