>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
Advertisements

Posted on April 21, 2011, in Sql Server 2005, Sql Server 2008, SQL Server Basics, TSQL Tips n Tricks. Bookmark the permalink. 3 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

  2. SELECT * FROM YourTableWHERE DATEDIFF(Y,JoiningDate,'1980')=0

  3. i want to convert the date into the same format of getdate() to have comparision with the date passed from front.? how can i do that..?boston long distance movers

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: