CODEWITHSUNDEEP
sql-serveroracle-database
Platus
Platus

Reputation: 1507

SQL Server equivalent of TO_CHAR and format conversion

I would like to know how I can convert the oracle instruction TO_CHAR(MY_COLUMN_NAME, 'YYYYMMDDHH24MISSFF3') in SQL Server. (The column containing a date)

I think I can use CONVERT(VARCHAR(19), MY_COLUMN_NAME, 120); in SQL Server to convert a date to the YYYY-MM-DD format but what about the YYYYMMDDHH24MISSFF3 format?

Upvotes: 1

Views: 14231

Answers (4)

SqlZim
SqlZim

Reputation: 38063

Prior to SQL Server 2012, you could use convert() styles and replace()

select 
    iso_format = convert(varchar(23),getdate(),121)
  , numbers_only = replace(replace(replace(replace(
          convert(varchar(23),getdate(),121)
           ,'-',''),':',''),' ',''),'.','')

rextester demo: http://rextester.com/YMMG65247

returns:

+-------------------------+-------------------+
|       iso_format        |   numbers_only    |
+-------------------------+-------------------+
| 2017-04-26 18:15:42.590 | 20170426181542590 |
+-------------------------+-------------------+

Note that format() does have some performance issues, and this may be a reasonable alternative even on SQL Server 2012+. format() is nice and all, but… - Aaron Berrtand

Upvotes: 0

SMor
SMor

Reputation: 2882

You can use the format command (https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql) where you have access to all of the functionality that .net provides. Presumably you are using a relatively current version of sql server - which is information you should provide when posting questions btw. As an example (and I don't know what your oracle format generates): 

declare @x datetime; 
set @x = '20170415 23:55:01.003';
select @x, FORMAT(@x, 'yyyyMMdd HHmmss.fff');

Upvotes: 0

Mikhail Lobanov
Mikhail Lobanov

Reputation: 3026

In MS SQL 2012+ there are FORMAT function:

SELECT FORMAT(GETDATE(), 'yyyyMMddHHmmssfff')

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 82010

You can use Format(). I should note that it is NOT a performer, but does have some nice functionality

Select DateTime   = GetDate()
      ,Formatted = format(GetDate(),'yyyyMMddHHmmssfff')

Returns

DateTime                  Formatted
2017-04-26 12:04:01.363   20170426120401363

Upvotes: 3

Related Questions