CODEWITHSUNDEEP
sqlsql-serversql-server-2008
vijayk
vijayk

Reputation: 2753

How to get date format (dd-MMM-yy HH:mm:ss) in SQL Server?

I want to convert getdate() to this format (10-Feb-15 18:25:57) in SQL Server.

select getdate()

gets me 

2015-02-10 18:25:37.567

but I want date in 10-Feb-15 18:25:37 format.

How to covert this date format into my required format?

I am new to SQL Server.

Thanks ....

Upvotes: 2

Views: 24952

Answers (6)

James Z
James Z

Reputation: 12318

It's not exactly that, but closest to that is:

select convert(varchar, getdate(), 113)

10 Feb 2015 15:00:30:513

To get without milliseconds you can shorten the string:

select convert(varchar(20), getdate(), 113)

You can find the formats here: https://msdn.microsoft.com/en-us/library/ms187928%28v=sql.100%29.aspx

If you really need that exact format, I think you'll have to work with datepart / datename.

Upvotes: 4

t-clausen.dk
t-clausen.dk

Reputation: 44316

This will get you the exact format:

SELECT 
  STUFF(STUFF(CONVERT(char(20), getdate(), 113),3,1, '-'),7,1,'-')

Upvotes: 4

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131219

The date types do not have any format, they are binary types. Formats apply only when you want to convert a date value to a string.

In SQL Server 2012+ you can use the FORMAT function to format a date to any of the standard format strings supported by .NET, or a custom format string, eg:

DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @d, 'dd-MMM-yy HH:mm:ss', 'en-US' ) 
------------------
10-Feb-15 15:35:55

In previous versions of SQL Server you are limited to using a few predefined formats using CONVERT, or constructing the final string by concatenating the date part values.

Upvotes: 2

Komal
Komal

Reputation: 2736

we can convert date into many formats
SELECT convert(NVARCHAR, getdate(), 106)
This may help you Click

Upvotes: 0

mohan111
mohan111

Reputation: 8865

SELECT REPLACE(CONVERT(varchar(11),SYSDATETIME(),13),' ','-')+' '+ CONVERT(VARCHAR(8), SYSDATETIME(), 8)

Upvotes: 1

Svein Fidjestøl
Svein Fidjestøl

Reputation: 3206

In your case there is no regular datetime format that fits your need. You will have to format date and time separately, and also replace spaces with dashes.

The following will generate a datetime in your requested format:

select replace(convert(varchar(9), getdate(), 6), ' ', '-')
       + ' ' + convert(varchar(8), getdate(), 8)

Result:

10-Feb-15 14:08:54

MSDN documentation on formatting datetime: CAST and CONVERT (Transact-SQL)

Upvotes: 0

Related Questions