CODEWITHSUNDEEP
sqlsql-server-2008
dann.dev
dann.dev

Reputation: 2494

SQL date format: dd/mm/yyyy hh:ss

I have been trying to find a date format: dd/mm/yyyy hh:mm.

I am using convert (varchar, rc.CreatedDateTime, ???). 131 is not what i need either.

I seem to be able to find an awful lot that are very close, but not this particular one, am I missing something glaringly obvious or is there another function I can use??

Upvotes: 3

Views: 7992

Answers (3)

Taryn
Taryn

Reputation: 247710

You can use this, just replace the getdate() with your field:

select convert(char(10), getdate(), 103) + ' ' 
    + convert(char(8), getdate(), 108)

which gives you the results:

14/03/2012 17:05:47

If you just want the hour and seconds then:

select convert(char(10), getdate(), 103) + ' ' 
    + Left(convert(char(8), getdate(), 108), 2) + ':'
    + Right(convert(char(8), getdate(), 108), 2)

Here is a helpful link with date/time conversions:

http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/

Based on your comment that it should be dd/mm/yyyy hh:mm then the query would be:

select convert(char(10), getdate(), 103) + ' ' 
    + Left(convert(char(8), getdate(), 108), 5)

OR without the LEFT()

select convert(varchar(10), getdate(), 103) + ' ' 
    + convert(char(5), getdate(), 108)

Upvotes: 5

anon
anon

Reputation: 

SELECT CONVERT(CHAR(10), rc.CreatedDateTime, 103) + ' ' 
     + CONVERT(CHAR(5),  rc.CreatedDateTime, 108)
FROM dbo.[table_name];

Upvotes: 3

Kaf
Kaf

Reputation: 33819

Assuming that you need dd/mm/yyyy hh:mm not dd/mm/yyyy hh:ss

SELECT convert (varchar(10), rc.CreatedDateTime, 103) + ' ' + 
       convert(varchar(5), rc.CreatedDateTime, 108)

Upvotes: 3

Related Questions