-1

I have a query for Oracle data which has TO_DATE('01-JAN-2017', 'DD-MON-YYYY') in it. I need to run the same query on SQL Server, but I am facing a problem since the TO_DATE() function is not available for T-SQL.

Can someone please help me with the equivalent of to_date() function which does the same as TO_DATE('01-JAN-2017', 'DD-MON-YYYY').

Original SQL query: 

INSERT INTO LOC_CLOSING_MESSAGE 
VALUES (2, 1, 'Store One.', TO_DATE('01-JAN-2017', 'DD-MON-YYYY'),
       'TEST_USER', SYSDATE, 'TEST_USER', NULL)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
abhishek pandey
  • 83
  • 1
  • 3
  • 13

2 Answers2

1

You should be using CONVERT function because it allows you to specify the format that you want to convert to.

In your case that would be:

INSERT INTO LOC_CLOSING_MESSAGE
  VALUES (2, 1, 'Store One.', 
    CONVERT('01-JAN-2017', 'DD-MON-YYYY', 105),
    'TEST_USER', SYSDATE,
    'TEST_USER', NULL
);

For list of all CONVERT formats, check out this link.

NOTE: Since I don't know what data type is your fourth attribute in table LOC_CLOSING_MESSAGE is, I don't really know if this formatting part is even needed in your case. Nevertheless, it can't hurt and gives you exactly what you asked for.

MK_
  • 1,139
  • 7
  • 18
0

Try this:

INSERT INTO LOC_CLOSING_MESSAGE VALUES (2, 1, 'Store One.', CONVERT(DATETIME, '01-JAN-2017'), 'TEST_USER', SYSDATE, 'TEST_USER', NULL)
DineshDB
  • 5,998
  • 7
  • 33
  • 49