To_Date in SQL Server 2005

to_date in SQL Server 2005

Use:

WHERE registrationdate BETWEEN '01/01/2003' AND '12/31/2003'

...but as gbn pointed out, to be locale safe - use:

WHERE registrationdate BETWEEN '20030101' AND '20031231'

SQL Server will perform implicit conversion of the string into a date, providing it's a supported format. Explicit conversion is when you have to use CAST or CONVERT to change the data type.

When converting '01/01/2003' to a DATETIME, the time portion will be 00:00:00 because it wasn't specified.

Sql Server string to date conversion

Try this

Cast('7/7/2011' as datetime)

and

Convert(DATETIME, '7/7/2011', 101)

See CAST and CONVERT (Transact-SQL) for more details.

Proper Use of TO_DATE FUNCTION

However, I am unsure which type of values I can put into the TO_DATE function

When you have such question you typically want to consult with the documentation TO_DATE

TO_DATE(char [, fmt [, 'nlsparam' ] ])

All possible format models (fmt) are described in Format Models

would it be acceptable if I did the following: TO_DATE('MAR', 2012)

No, it wouldn't. But you can do

SELECT TO_DATE('MAR 2012', 'MON YYYY') dt FROM dual;

Output:

|                           DT |
|------------------------------|
| March, 01 2012 00:00:00+0000 |

Here is SQLFiddle demo

Convert 10JAN2000:00:00:00 to DATETIME in SQL

Most databases have some sort of to_date() or parse_date() functionality . . . except SQL Server.

If you are using SQL Server, then this should work:

select cast(stuff('10JAN2000:00:00:00', 10, 1, ' ') as datetime)

Use TO_DATE in SQL Server 2012

SQL-Server has no TO_DATE function. You have to use convert.
See here

-- Specify a datetime string and its exact format
SELECT TO_DATE('2012-06-05', 'YYYY-MM-DD') FROM dual;

-- Specify a datetime string and style 102 (ANSI format), raises an error if conversion fails
  SELECT CONVERT(DATETIME, '2012-06-05', 102);

  -- TRY_CONVERT available since SQL Server 2012 (returns NULL if conversion fails)
  SELECT TRY_CONVERT(DATETIME, '2012-06-05', 102);

For your specific case use:

convert(DATETIME, '2011-11-09 00:00:00')

Convert Date format into DD/MMM/YYYY format in SQL Server

I'm not sure there is an exact match for the format you want. But you can get close with convert() and style 106. Then, replace the spaces:

SELECT replace(convert(NVARCHAR, getdate(), 106), ' ', '/')

How can I convert a SQL Server date format to Oracle?

I assume you're using insert statements?

Convert your dates using:

TO\_DATE(sql\_server\_value,'YYYY.MM.DD HH24:MI:SS')

---

Related Topics