Oracle convert to SQL dates

Transact-SQL
21

Also, I am trying to to convert the following but no success? I get errors

The field ACTION_DT is a "date" field in dB.

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

--AND (EH.ACTION_DT>=TO_DATE ('17-12-2017 00:00:00', 'DD-MM-YYYY HH24:MI:SS')---oracle
-- AND EH.ACTION_DT<TO_DATE ('24-12-2017 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))--- oracle

AND (EH.ACTION_DT>= CAST('17-12-2017 00:00:00' As datetime) --sql
and EH.ACTION_DT< CAST('24-12-2017 00:00:00' as datetime)) --sql

Also tried this as well but no success:

and (EH.ACTION_DT>= Convert(datetime,'17-12-2017 00:00:00', 114)
and EH.ACTION_DT< Convert(datetime ,'24-12-2017 00:00:00', 114))

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

22

The others need adjusted too; I just gave the first two as samples.

23

Thanks did you see my other issues I had? see below pls: this is different conversion:
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

--AND (EH.ACTION_DT>=TO_DATE ('17-12-2017 00:00:00', 'DD-MM-YYYY HH24:MI:SS')---oracle
-- AND EH.ACTION_DT<TO_DATE ('24-12-2017 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))--- oracle

AND (EH.ACTION_DT>= CAST('17-12-2017 00:00:00' As datetime) --sql
and EH.ACTION_DT< CAST('24-12-2017 00:00:00' as datetime)) --sql

Also tried this as well but no success:

and (EH.ACTION_DT>= Convert(datetime,'17-12-2017 00:00:00', 114)
and EH.ACTION_DT< Convert(datetime ,'24-12-2017 00:00:00', 114))

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

24

Typically SQL uses mm-dd-yyyy, which appears to be the case on your server. You don't need to explicitly cast the literal, SQL will automatically match it to the column.

AND (EH.ACTION_DT>= '12-17-2017'
and EH.ACTION_DT< '12-24-2017'

25

Awesome thanks, so the time and min like---> 00:00:00', 'DD-MM-YYYY HH24:MI:SS' will be converted automatically? this is where it throw me off cause I thought I had to convert them as well. this is how it appeared in oracle but even in oracle its only a date field.
Pasi

26

In SQL Server, time will default to '00:00:00'. If you needed a different time, for example 8PM, you would have to explicitly specify that:

'12-17-2017 20:00'

27

OK thanks.
Pasi.

28

If you provide the date (and optional time) as a text string then implicit conversion is dependent on the way the wind is blowing! Lots of things effect the rules that SQL applies, for example the LANGUAGE of the currently connected user.

There are two unambiguous formats for DATETIME datatype:

'YYYYMMDD' or 'YYYYMMDD HH:MM:SS.sss' -- Note: NO punctuation allowed
'YYYY-MM-DDTHH:MM:SS.sss' - the ISO format - the T delimiter is required

and for DATE datatype also:
'YYYY-MM-DD'

the DATE datatype is somewhat newer, so I always stick to 'YYYYMMDD' just in case the object I am saving to is a DATETIME

You can also provide hints if converting a value in a different style

SET DATEFORMAT dmy
INSERT INTO MyTable(MyDateTimeColumn) VALUES('17-12-2017'),('17/12/2017'),('17.12.2017')

in which case a number of styles of punctuation/delimiters are valid, its just the sequence of Day, Month and Year that is controlled.

Note that the DATEFORMAT change is persistent for that session; for individual date conversions you can use CONVERT instead, with a "style" number, but the styles available are limited

CREATE TABLE #TEMP
(
	MyDateTimeColumn datetime
)

SET DATEFORMAT dmy

INSERT INTO #TEMP(MyDateTimeColumn)
VALUES	('17-12-2017')
	, ('17/12/2017')
	, ('17.12.2017')
--	, ('17122017')	-- This is NOT valid, all 6 & 8 digits parse as YYYYMMDD

INSERT INTO #TEMP(MyDateTimeColumn)
SELECT	CONVERT(datetime, '17-12-2017', 105)	-- 105=dd-mm-yyyy format

INSERT INTO #TEMP(MyDateTimeColumn)
SELECT	CONVERT(datetime, '17.12.2017', 105)

INSERT INTO #TEMP(MyDateTimeColumn)
SELECT	CONVERT(datetime, '12/17/2017', 101)	-- 101=mm/dd/yyyy  format

SELECT	*
FROM	#TEMP

DROP TABLE #TEMP