CODEWITHSUNDEEP
sqlsql-servert-sqldate-formatting
Selva
Selva

Reputation: 1670

Convert DateTime into desired format in SQL Server

In my project we are migrating from an Oracle database to SQL Server.

In Oracle, to get the required date format, we will use the to_char function - it will return the desired format as we mentioned in the second param:

TO_CHAR(TXNDT,'dd/MM/yyyy hh24:mi:ss')

In SQL Server, the same thing can be achieved like below.

convert(varchar(255), TXNDT, 131)

My problem is: I want to get the date format dd-MM-yyyy hh24:mi:ss

I can do this in Oracle:

TO_CHAR(TXNDT,'dd-MM-yyyy hh24:mi:ss')

But how can I achieve the samething in SQL Server?

Any help will be greatly appreciated!

Upvotes: 1

Views: 3254

Answers (2)

Thom A
Thom A

Reputation: 95544

CONVERT(Transact-SQL) has all of the style codes for CONVERT. Note, also, that you need to CONVERT to a varchar, not a datetime. datetime has a fixed display format of yyyy-MM-dd HH:mm:ss.sss.

So, for you, it would be CONVERT(varchar(20),TXNDT,120). notice the use of varchar, rather than datetime.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Use format(). I think this does what you want:

format(TXNDT,'dd-MM-yyyy HH:mm:ss')

If you need this as a varchar():

convert(varchar(255), format(TXNDT,'dd-MM-yyyy HH:mm:ss')

Upvotes: 3

Related Questions