Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

using the format() command Expand / Collapse
Author
Message
Posted Thursday, July 25, 2013 11:13 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:39 PM
Points: 247, Visits: 738
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.
Post #1477638
Posted Thursday, July 25, 2013 1:01 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 3:58 PM
Points: 646, Visits: 2,994
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.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't 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. " -- Itzek Ben-Gan 2001

My blog
Post #1477689
Posted Thursday, July 25, 2013 1:16 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:39 PM
Points: 247, Visits: 738
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.
Post #1477698
Posted Thursday, July 25, 2013 1:21 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:39 PM
Points: 247, Visits: 738
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.
Post #1477700
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse