sql datetime field format

Question

question

Itay B asked Oct 5, '11

sql datetime field format

Hello, I have a smalldatetime field in my sql table. I would like to show it in a specific format in my queries. As much as i searched, i couldn't find this format in mssql 2005; dd/mm/yyyy hh:mm. this is the exact format i need. anything else is no good. Any idea? Thanks

sql-server-2005 sql tsql

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2011-10-05T06:12:18Z

Mrs_Fatherjack answered Oct 5, '11

This has been answered on another forum here: http://stackoverflow.com/questions/347659/dd-mm-yyyy-hhmm-format-in-sql-server but the answer is: SELECT CONVERT(varchar,GETDATE(),103) + ' ' + SUBSTRING(CONVERT(varchar,GETDATE(),108),1,5) where getdate is replaced by your datetime field.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD commented · Oct 05, 2011 at 06:42 AM

You need to change the first CONVERT() to style 103 instead of 104. This will use slashes between the dateparts instead of dots, otherwise, great! +1 The best overview for date styles can be found [here][1] [1]: http://msdn.microsoft.com/en-us/library/ms187928(v=SQL.90).aspx

0 ·

Mrs_Fatherjack commented · Oct 05, 2011 at 06:51 AM

Thanks for that, hadn't spotted the dots rather than slashes. Or at least hadn't realised the potential importance.

0 ·

Answer 2 · 2011-10-05T08:53:50Z

Grant Fritchey answered Oct 5, '11

But the most important thing is, don't count on SQL Server for formatting. That's what your front-end code is for. SQL Server should store the information in a useful manner. Your application, report, or spreadsheet can then format it appropriately.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question