CODEWITHSUNDEEP
sqlsql-server
user523650
user523650

Reputation:

Convert SQL DateTime format

How can I display a DATETIME value (2010-12-02 15:20:17.000) as 02/12-2010 15:20?

Upvotes: 2

Views: 12821

Answers (6)

Alfred Wallace
Alfred Wallace

Reputation: 23

Little example I use for Germany and Switzerland: dd.mm.yyyy hh:mm

SELECT CONVERT(varchar, GETDATE(), 104) + ' ' + LEFT(CONVERT(varchar, GETDATE(), 108), 5)

Upvotes: 0

eddy
eddy

Reputation: 11

Use MSSQL's build-in function to convert datetime to string with format, 

SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY] --2/5/12
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] --5/2/2012

You need to create custom function to get various format to use like this;

SELECT dbo.ufn_FormatDateTime(GETDATE(),'YYYY-MM-DD HH:mm:SS tt')
--Output : 2012-02-05 01:58:38 AM

SELECT dbo.ufn_FormatDateTime(GETDATE(),'(dddd) mmmm dd, yyyy hh:mm:ss.fff tt')
--Output : (Sunday) February 05, 2012 01:58:38.723 AM

SELECT dbo.ufn_FormatDateTime(GETDATE(),'dd/MM/yyyy')
--Output : 05/02/2012

SELECT dbo.ufn_FormatDateTime(GETDATE(),'yyyy MMM, dd (ddd) hh:mm:ss tt')
-- Output : 2012 Feb, 05 (Sun) 01:58:38 AM

Get the code snippet from this link. http://www.tainyan.com/codesnippets/entry-62/sql-server-date-time-format-function.html

Upvotes: 1

Will Marcouiller
Will Marcouiller

Reputation: 24152

Assuming Oracle:

select TO_CHAR(SYSDATE, "dd/mm-yyyy HH24:mi")
    from DUAL;

Assuming SQL Server:

select STR(DATEPART(DAY, GETDATE()), 2) 
        + '/' 
        + STR(DATEPART(MONTH, GETDATE()), 2) 
        + '-' 
        + STR(DATEPART(YEAR, GETDATE()), 4) 
        + ' ' 
        + STR(DATEPART(HOUR, GETDATE()), 2) 
        + ':' 
        + STR(DATEPART(MINUTE, GETDATE()), 2);

Upvotes: 0

marc_s
marc_s

Reputation: 755451

DateTime is a DateTime is a DateTime - it just holds a date and time and doesn't have any string representation, really.

See the CAST and CONVERT topic in the SQL Server Books Online for details - it shows all supported date formats that SQL Server supports.

For your source format (2010-12-02 15:20:17.000) you could probably use style no. 121

DECLARE @source VARCHAR(50)
SET @source = '2010-12-02 15:20:17.000'

DECLARE @Date DATETIME
SELECT @Date = CONVERT(DATETIME, @source, 121)

SELECT @Date

but your target format is a bit odd..... I don't see any "out of the box" style that would match your needs. You'll need to use some string manipulation code to get that exact format.

Upvotes: 2

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171579

For SQL Server:

select stuff(convert(varchar, getdate(), 105), 3, 1, '/') + ' ' + left(convert(varchar, getdate(), 8), 5)

Upvotes: 2

Truls Unstad
Truls Unstad

Reputation: 56

http://msdn.microsoft.com/en-us/library/ms189491.aspx

Is this what you're looking for?

Upvotes: 0

Related Questions