using the format() command

Question

using the format() command

Tobar

SSCarpal Tunnel

Points: 4876
More actions
July 25, 2013 at 11:13 am

#277943

Anyone point me to the documentation, or explain why you would expect these results?
DECLARE @d DATETIME = '07/25/2013 10:11:12.345';
SELECT FORMAT ( @d, 'yyyy dd mm hh mm ss')
2013 25 11 10 11 12
(I am passing it the modifier "mm" twice. The first time it is interpretted as month the second as minute.)
<><
Livin' down on the cube farm. Left, left, then a right.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

Tobar (7/25/2013)
Anyone point me to the documentation, or explain why you would expect these results?
DECLARE @d DATETIME = '07/25/2013 10:11:12.345';
SELECT FORMAT ( @d, 'yyyy dd mm hh mm ss')
2013 25 11 10 11 12
(I am passing it the modifier "mm" twice. The first time it is interpretted as month the second as minute.)

There's not a lot out there on FORMAT. There's this: http://msdn.microsoft.com/en-us/library/hh213505.aspx. It's not very good IMHO but that's what Microsoft has out there. If you have Microsoft SQL Server 2012 T-SQL Fundamentals (Itzek Ben Gan) he mentions it briefly and refers you to: http://msdn.microsoft.com/library/26etazsy.aspx.

Anyhow, I didn't completely get what you are asking here but... MM=month; mm=minute

This:

DECLARE @d DATETIME = '07/25/2013 10:11:12.345';

SELECT FORMAT ( @d, 'yyyy dd MM hh mm ss')

...will get you this:

2013 25 07 10 11 12

This is also a good article about FORMAT: http://sqlsafety.blogspot.com/2012/11/t-sql-format-built-in-function.html

EDIT: Typo, added link.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Tobar SSCarpal Tunnel Points: 4876 More actions · Answer 2

Thanks Alan. If nothing else it is nice to hear that "there isn't a lot out there" from someone else.

<><
Livin' down on the cube farm. Left, left, then a right.

Tobar SSCarpal Tunnel Points: 4876 More actions · Answer 3

Sorry Alan, didn't read your response close enough first time.

Alan.B (7/25/2013)
Tobar (7/25/2013)
Anyone point me to the documentation, or explain why you would expect these results?
DECLARE @d DATETIME = '07/25/2013 10:11:12.345';
SELECT FORMAT ( @d, 'yyyy dd mm hh mm ss')
2013 25 11 10 11 12
(I am passing it the modifier "mm" twice. The first time it is interpretted as month the second as minute.)
Anyhow, I didn't completely get what you are asking here but... MM=month; mm=minute
This:
DECLARE @d DATETIME = '07/25/2013 10:11:12.345';
SELECT FORMAT ( @d, 'yyyy dd MM hh mm ss')
...will get you this:
2013 25 07 10 11 12

That is just it. I us mm twice in my string. The first time it interprets it as MM (supposedly "hour") the second time as mm (supposedly minute).

<><
Livin' down on the cube farm. Left, left, then a right.