CODEWITHSUNDEEP
sqlsql-serveroracle-databasedatetype-conversion
user3341592
user3341592

Reputation: 1561

Write generic SQL for both SQL Server and Oracle for displaying dates

I do have to query SQL Server and Oracle DB for the same project. The DBMS depends on the client, but the structure is exactly the same.

For example, I do want to output dates without the "00:00:00" part, which is heavy and useless in a long output.

Currently, I do have to edit the query each time:

SELECT col1, dated_requested_without_time, col3, col4, col5, *
FROM table
WHERE
    date_requested = CONVERT(varchar(10), GETDATE(), 120)
--  date_requested = TRUNC(SYSDATE)

Is there a clever way to write it in a way that's executable in both environments without any editing needed?

The goal is to display and compare against today's date (YYYY-MM-DD)...

I did succeed to use concatenations like this

CONCAT(CONCAT(CONCAT(col1, ' '), col2), '.')

instead of the

col1 + ' ' + col2 + '.'      -- For SQL Server.
col1 || ' ' || col2 || '.'   -- For Oracle.

for example. Hoping the same for the above date query…

Any successful help would be greatly appreciated!

Upvotes: 0

Views: 161

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89091

How about:

select cast(current_timestamp as date)

?

Upvotes: 1

Related Questions