9

I need to use sysdate in both Oracle and SQL Server, but SQL Server has a GETDATE() function instead.

But I don't want to create 2 separate queries

Is there any specific common syntax which gives same output to sysdate and getdate in SQL Server and oracle

Pete Carter
  • 2,691
  • 3
  • 23
  • 34
Akshat
  • 575
  • 2
  • 12
  • 28
  • It's not clear what your asking. SQL Server has GETDATE() sYSTEMDATETIME() and GETUTCDATE() – Pete Carter Oct 19 '12 at 17:00
  • What API are you using? Many APIs support the ODBC escape sequence `{fn current_date}` and/or `{fn current_timestamp}` and convert that to the appropriate function call in whatever database you're using. – Justin Cave Oct 19 '12 at 17:02
  • I have a select query which needs get data based on the date function for MSSQL the query would be based on getdate() ,, But if i am using oracle db this query dosn't work it instead understands sysdate() ,, But i don't want to create 2 different queries for two db's , so is there any way to achieve the same i.e don't use sysdate and getdate() instead some common date function which will be applicable for both the db's – Akshat Oct 19 '12 at 17:02
  • The standard is `CURRENT_TIMESTAMP` even though people in SQL Server more commonly use `GETDATE()` (fewer characters?). Whether other vendors follow suit, not sure. – Aaron Bertrand Oct 19 '12 at 17:12

2 Answers2

24

CURRENT_TIMESTAMP is valid syntax for both SQL Server and Oracle. Oracle will return the current date and time in the session time zone. SQL Server will return the current date and time of the Operating System

Kermit
  • 33,827
  • 13
  • 85
  • 121
Pete Carter
  • 2,691
  • 3
  • 23
  • 34
  • 1
    According to this SO answer, `sysdate` and `CURRENT_TIMESTAMP` may not be equal if the client is in a different timezone than the DB server. http://stackoverflow.com/a/17925834/378151 – Snekse Jun 29 '14 at 17:54
3

You could also just alias it in t-sql to match Oracle's function?

CREATE FUNCTION sysdate ()
RETURNS DATETIME
AS
BEGIN
    RETURN getdate()
END
GO
Oxydel
  • 176
  • 1
  • 5
  • in this case i have to use dbo.sysdate() is there a possibility that it behave same as oracle e.g. sysdate? – Haider Jul 28 '14 at 11:08