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,10
106 is style number. Get top 20 commonly used datetime formats here
************************************************************
************************************************************
Recommended Posts
SQL Server Denali: Get Date/Time from Parts
SQL Server: 20 Most Commonly Used DateTime Formats
SQL Server: Data Type DATETIME or Only DATE
there is an emp table where details of employees are present, for eg date of joining etc.
ReplyDeletei need a query which display the employes who are joining in year 1981.......your reply is highly appreciated
SELECT * FROM YourTable
ReplyDeleteWHERE DATEDIFF(Y,JoiningDate,'1980')=0
This comment has been removed by a blog administrator.
ReplyDeleteExcellent! I needed a conversion from Oracle to MSSql and your solution did the job :)
ReplyDeleteIn 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?
ReplyDeletehttp://connectsql.blogspot.com/2011/02/sql-server-20-most-commonly-used.html
DeleteHmm, seems like someone doesn't read pretty well your entry, u_u'
DeleteIf you have some stuff about regular expressions, I'll be thankful if can share it.
Very useful information, thank you Aasim!
TO_CHAR(to_date( '" + Some_Value + "', 'DD-MM-YYYY')) this is in ORACLE,
ReplyDeleteCan you help me to write the same in SQL Server
create table customers (customerid char(10) primary key, name varchar(25), birthdate datetime, frequentflieron char(2) references airlines(airlineid));
ReplyDeletei need help in sql server question
Thanks for your information, now in this new version there are so many new feature and bugs fix.
ReplyDeletegclub online
goldenslot
สูตรบาคาร่า
I have a query is sql server that I need to convert a colomn from '1899/12/30 12:56:00 AM' to a column value of '00:56'. This dureation column reflect the duration of a delay in 'HH:MM'.Below the query.
ReplyDeleteselect
ms.SectorDate,
ms.PortFrom,
ms.Flight,
ms.Dep as ScheduleDeparture,
ms.Start as ActualDeparture,
ms.Arr as ScheduleArrival,
ms.Land as ActualArrival,
dc.AirlineIdentifier as DelayCode,
dc.Name,
dc.DelayGroup,
dc.Definition,
d.Duration,
d.Comment,
d.Port,
d.Type,
d.CausedByRego
from
Delays d,
DelayCodes dc,
MainSchd ms
where
d.SectorKey = ms.SectorKey and
ms.SectorDate >='2018-01-01 12:00:00 AM' and
d.DelayCode = dc.DelayCodeKey and
dc.AirlineIdentifier in('91','92','93','94','95','96','101','103')
order by ms.SectorDate, ms.Flight