Thursday, April 21, 2011

SQL Server Basics: TO_DATE() Function in SQL Server

Dealing with data conversion functions in SQL Server, database developers with Oracle or Java background usually ask one same question “is there any equivalent function TO_DATE() to convert  string into datetime or to get different formats of date ?” .
There is no TO_DATE() function in SQL Server.
Equivalent functions are CAST() and CONVERT().  Where CONVERT() is more rich function and mostly used to get different styles of datetime data.
Oracle:                SELECT to_date('01-JAN-2009') FROM dual
SQL Server:          SELECT CAST('01-JAN-2009' AS DATETIME)
Or you can use      SELECT CONVERT(DATETIME,'01/JAN/2009')
To get output in different formats you can use CONVERT() function as follow.
DECLARE @MyDate DATETIME
SELECT @MyDate = '01/JAN/09'
SELECT CONVERT(VARCHAR(20),@MyDate,106)
106 is style number. Get top 20 commonly used datetime formats here



************************************************************

Recommended Posts

 

 

9 comments:

  1. there is an emp table where details of employees are present, for eg date of joining etc.

    i need a query which display the employes who are joining in year 1981.......your reply is highly appreciated

    ReplyDelete
  2. SELECT * FROM YourTable
    WHERE DATEDIFF(Y,JoiningDate,'1980')=0

    ReplyDelete
  3. This comment has been removed by a blog administrator.

    ReplyDelete
  4. Excellent! I needed a conversion from Oracle to MSSql and your solution did the job :)

    ReplyDelete
  5. In the TO_DATE function I can use many different masks for a date, like 'DD-MM-YYYY' or 'DD-MM-YY' or 'YYYY-MONTH-DD' or whatever combination that you imagine, how can I get this versatility in sql server?

    ReplyDelete
    Replies
    1. http://connectsql.blogspot.com/2011/02/sql-server-20-most-commonly-used.html

      Delete
    2. Hmm, seems like someone doesn't read pretty well your entry, u_u'
      If you have some stuff about regular expressions, I'll be thankful if can share it.

      Very useful information, thank you Aasim!

      Delete
  6. TO_CHAR(to_date( '" + Some_Value + "', 'DD-MM-YYYY')) this is in ORACLE,

    Can you help me to write the same in SQL Server

    ReplyDelete
  7. create table customers (customerid char(10) primary key, name varchar(25), birthdate datetime, frequentflieron char(2) references airlines(airlineid));

    i need help in sql server question

    ReplyDelete

All suggestions are welcome