-2

I am converting Oracle queries to a SQL Server equivalent, some have been easier than others, right now I am stuck on a query that is containing this in a where clause

TRUNC(TO_NUMBER(TO_DATE(SYSDATE) - MyTable.DOBDATE) / 365, 0)

I've read that Convert is SQL Server's equivalent to ORACLE's TRUNC,

I know the SYSDATE will be GetDate() but I am lost on this part of the query

TRUNC(TO_NUMBER(TO_DATE(SYSDATE)

What would be SQL Server's equivalent to this ?

EDIT in short how do I take this Oracle statement

TRUNC(TO_NUMBER(TO_DATE(SYSDATE) - MyTable.DOBDATE) / 365, 0)

and convert to its SQL Server equivalent

Chris
  • 2,953
  • 10
  • 48
  • 118
  • Can you explain in words, not in code, what do you want to receive from getdate()? – sepupic Aug 20 '17 at 15:51
  • @sepupic, I know that Oracle's SYSDATE would be GETDATE() in SQL. Its the TRUNC(TO_NUMBER(TO_DATE is my problem. I don't know SQL's equivalent – Chris Aug 20 '17 at 15:56
  • ...and I don't know what TRUNC(TO_NUMBER(TO_DATE mean. Can you please explain in WORDS (for example: I want to get the number of days passed from...or what else you want) – sepupic Aug 20 '17 at 15:57
  • Please make a small effort to help everyone, including yourself. First, use Google or any other means to understand the difference between `SQL` and `SQL Server` (a commercial product sold by Microsoft). Then edit your post and change "SQL" to "SQL Server" everywhere. –  Aug 20 '17 at 15:58
  • @mathguy, gotcha. made the change and made the question clearer – Chris Aug 20 '17 at 16:01
  • The whole formula is nonsense in Oracle. TO_DATE converts a string to a date, but SYSDATE is already a date, not a string; `TO_DATE(SYSDATE)` will very likely lead either to an error or, worse, an incorrect result without warning. (It may also have no effect, but that would be pure luck). Then the date difference is already a number, that is divided by 365 to convert to years, that is fine so far, but then you have TO_NUMBER(...). TO_NUMBER converts a string to a number, but the argument in your formula is a number already. Applying TO_NUMBER to it may result in error or wrong result. –  Aug 20 '17 at 16:01
  • That's why I asked him to explain what he wants to achieve. But it seems that he himself has no idea what it was in Oracle, so he cannot say what he wants to get in SQL Server %) – sepupic Aug 20 '17 at 16:04
  • @mathguy, I didn't write the Oracle query..here is the remainder of the query: mytable.expidate >= sysdate - 0 and TRUNC(TO_NUMBER(TO_DATE(SYSDATE) - mytable.DOBDATE) / 365, 0) between 1 and 6 – Chris Aug 20 '17 at 16:05
  • Wait, I don't understand. Are you trying to convert to SQL Server, or simply to "theoretical" SQL, which doesn't exist anywhere in real life? –  Aug 20 '17 at 16:05
  • @sepupic, correct, I have no idea what I want because I don't have access to the Oracle database to run the oracle queries against. So I have no idea what I should be getting back – Chris Aug 20 '17 at 16:06
  • @mathguy, I am converting real life oracle queries to its sql equivalents – Chris Aug 20 '17 at 16:07
  • An Oracle db has been moved to sql – Chris Aug 20 '17 at 16:07
  • "Moved to SQL" doesn't make sense, since there is no SQL. Do you mean Microsoft SQL **Server**? The name of the product is "SQL Server", not "SQL". We can't help you until you clarify this. –  Aug 20 '17 at 16:09
  • If someone here translates into simple words what it was in Oracle I can translate it to T-SQL. Could it be the number of years between the mytable.DOBDATE and today's date? – sepupic Aug 20 '17 at 16:09
  • 1
    @sepupic - that's easy; the formula computes the truncated age of a person. SYSDATE is today's date, minus DOBDATE (date of birth) is the difference **in days**, divided by 365, and truncated to an integer. It is probably used to filter by age >= 18 or similar. –  Aug 20 '17 at 16:10
  • @mathguy, yes, I should have said "moved an oracle database to a sql server database". – Chris Aug 20 '17 at 16:13
  • 1
    Ok, so it can be select datediff(day, mytable.DOBDATE, getdate()) / 365 – sepupic Aug 20 '17 at 16:13
  • @sepupic, i believe that mathguy explained what i was getting at – Chris Aug 20 '17 at 16:14
  • I supplied the equivalent of this: number of days between mytable.DOBDATE and getdate() devided by 365, the result is always integer, like truncate – sepupic Aug 20 '17 at 16:20
  • @sepupic, I just tested it and looks good. If you want you can put it to an answer and i will accept it – Chris Aug 20 '17 at 16:22
  • Unrelated, but: `TO_DATE(SYSDATE)` is utter nonsense. `to_date()` converts a `varchar` to a `date`. But `sysdate` already **is** a date. –  Aug 20 '17 at 19:53

1 Answers1

0

The equivalent would be:

select datediff(day, MyTable.DOBDate, getdate()) / 365

Note that SQL Server does integer division, so the result is an integer. No need for an additional function.

The to_number() is redundant in Oracle, I think. Neither set of code exactly calculates the age in years, because neither takes leap years into account.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I have ran this and added a where clause to the complete query, : select datediff(day, MemberDOB, getdate()) / 365 from tblMember where MemberDOB between 1 and 6, and get this error: Operand type clash: date is incompatible with tinyint – Chris Aug 20 '17 at 19:05
  • Presumably, `MemberDOB` is a date. Why are you comparing it to an integer? – Gordon Linoff Aug 20 '17 at 19:21
  • I been working with this query and here it is in its entirety... select count(*) from tblLicense l inner join tblMember m on m.MemberID = l.MemberID where ISNULL(m.MemberStatusID, '') <> 3 and l.LicenseNumber = '2090' and l.LicenseExpirationDate >= getdate() - 0 and datediff(day, m.MemberDOB, getdate()) / 365 – Chris Aug 20 '17 at 19:22
  • @Chris . . . You should ask another question. This question is about a very specific construct, and I think this answer answers it. You should include your complete query, and other information that is relevant for answering the question. – Gordon Linoff Aug 20 '17 at 19:55