Date Time Formating

Question

Hi i need to wirte sql query in such a way that it will retrive the date in the following format dd MM yyyy HH mm ss AM(or PM)

eg 31 12 2009 12 45 06 AN

Answer 1

Use DATE_FORMAT function. Here is the reference for formats: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

Answer 2

You could take a look at CAST and CONVERT on MSDN.

SELECT CONVERT(VARCHAR(30), datetime_field, 130) AS formatted_date

The 130 will convert the date to the following format: dd mon yyyy hh:mi:ss:mmmAM (or PM)

Answer 3

It would be hard to get it more inefficient as this but here goes

DECLARE @GetDate DATETIME
SET @GetDate = '01-01-2009 13:10:20'
SELECT 
  CAST(DATEPART(dd, @GETDATE) AS VARCHAR(2)) + ' '
  + CAST(DATEPART(mm, @GETDATE) AS VARCHAR(2)) + ' '
  + CAST(DATEPART(yyyy, @GETDATE) AS VARCHAR(4)) + ' '
  + CAST(DATEPART(hh, @GETDATE)%12 AS VARCHAR(2)) + ' '
  + CAST(DATEPART(mi, @GETDATE) AS VARCHAR(2)) + ' '
  + CAST(DATEPART(ss, @GETDATE) AS VARCHAR(2)) + ' '
  + CASE WHEN DATEPART(hh, @GETDATE) > 12 THEN 'AM' ELSE 'PM' END

Answer 4

If you're using SQL 2005 and 2008 a good way to go is to write a CLR procedure to do the formatting for you. The .NET framework has very powerful formatting routines for various data types and it makes sense to exploit them when you want to do formatting at a database level.

Although whether this should be the case if of course highly debatable. I can understand the SQL Server team's rationale in not bending over backwards to support flexible formatting given that a database's main job is to store and retrieve data.

Answer 5

I would recommend formatting the string on the middle layer/tier or the client side. It's so much easier with various format methods... This is a presentation requirement, why don't you format in in the presentation layer, then?