How do I specify date literal when writing SQL query from SQL Server that is linked to Oracle?

by

I prefer the ODBC format:

--DateTime
SELECT {ts'2015-09-20 12:30:00'}
--Time (however this comes with "today"-time)
SELECT {t'12:30:00'}
--Date
SELECT {d'2015-09-20'}
GO

The simple date literal is not culture independent…

SET LANGUAGE ENGLISH;
SELECT CAST('2014-09-13' AS DATETIME);
GO
SET LANGUAGE GERMAN;
SELECT CAST('2014-09-13' AS DATETIME);--ERROR: there's no month "13"
GO

But it works – however – with target type DATE (this difference is rather weird…):

SET LANGUAGE ENGLISH;
SELECT CAST('2014-09-13' AS DATE);
GO
SET LANGUAGE GERMAN;
SELECT CAST('2014-09-13' AS DATE);--ERROR: there's no month "13"
GO

Thx to lad2025 I want to add for completness the “full” ISO 8601, which works fine:

SET LANGUAGE ENGLISH;
SELECT CAST('2014-09-13T12:30:00' AS DATETIME);
GO
SET LANGUAGE GERMAN;
SELECT CAST('2014-09-13T12:30:00' AS DATETIME);
GO

More Related Contents:

  1. ORA-01843 Not a valid month
  2. Is there a combination of “LIKE” and “IN” in SQL?
  3. SQL – How to store and navigate hierarchies?
  4. Can a foreign key be NULL and/or duplicate?
  5. Multiplication aggregate operator in SQL
  6. Stored Procedure with optional “WHERE” parameters
  7. Deferrable Constraints in SQL Server
  8. Benefits Of Using SQL Ordinal Position Notation?
  9. Unordered results in SQL
  10. update columns values with column of another table based on condition [duplicate]
  11. How do I list all the columns in a table?
  12. What’s the correct name for an “association table” (a many-to-many relationship) [closed]
  13. Backup SQL Schema Only?
  14. Connecting to Oracle Database using Sql Server Integration Services
  15. Is there an Oracle equivalent to SQL Server’s OUTPUT INSERTED.*?
  16. Reset identity seed after deleting records in SQL Server
  17. How to avoid the “divide by zero” error in SQL?
  18. Multiple INSERT statements vs. single INSERT with multiple VALUES
  19. How to declare variable and use it in the same Oracle SQL script?
  20. Are a CASE statement and a DECODE equivalent?
  21. Oracle SQL – Identify sequential value ranges
  22. How to return multiple rows from the stored procedure? (Oracle PL/SQL)
  23. Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null
  24. How to extract week number in sql
  25. In Oracle, is it possible to INSERT or UPDATE a record through a view?
  26. SQL Server SELECT INTO @variable?
  27. Select a column if other column is null
  28. How to subtract 30 days from the current date using SQL Server
  29. How to do a case sensitive GROUP BY?
  30. How to automatically generate unique id in SQL like UID12345678?

Leave a Comment