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: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
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


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 1:58 PM
Points: 554, Visits: 2,578
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)
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: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
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: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
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