FORMATting dates

Question

FORMATting dates

Steve Jones - SSC Editor

SSC Guru

Points: 734434
More actions
October 23, 2017 at 10:41 pm

#100960

Comments posted to this topic are about the item FORMATting dates

Viewing 15 posts - 1 through 15 (of 16 total)

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

Stewart "Arturius" Campbell SSC Guru Points: 72498 More actions · Answer 1

Nice one to start the day on, thanks Steve

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

allinadazework SSCarpal Tunnel Points: 4506 More actions · Answer 2

Another interesting article. Thanks.
I've not used this function before. I tend to use DATENAME for this sort of thing as I think it's easier to read and the arguments are easier to remember. There seem to be so many date format functions floating around all with slightly different letters/cases to get months and minutes and milliseconds etc. I always end up looking them up and it's easy to accidentally get a month instead of a minute or vice versa.

HappyGeek SSCoach Points: 18726 More actions · Answer 3

HappyGeek

SSCoach

Points: 18726

October 24, 2017 at 2:12 am

#1964913

Good question, thanks Steve.

...

sipas Hall of Fame Points: 3828 More actions · Answer 4

Never used this, but doesn't the result depend on MonthDayPattern (i.e. it will come out differently for different cultures)?

Plus you will end up with ambiguous results like 'October 17' (October 17th/October 2017).

m mcdonald Hall of Fame Points: 3264 More actions · Answer 5

After seeing the question in the newsletter I was expecting to see something like

select format(@date, 'MMMM dd')

Had to do some digging...

Japie Botma SSCrazy Points: 2990 More actions · Answer 6

m mcdonald - Tuesday, October 24, 2017 4:51 AM
After seeing the question in the newsletter I was expecting to see something like
select format(@date, 'MMMM dd')
Had to do some digging...

I prefer the format 'MMMM dd' for readability, or even better, 'MMM dd' to have the column always the same length.

5ilverFox
Consulting DBA / Developer
South Africa

Ed Wagner SSC Guru Points: 287024 More actions · Answer 7

A nice question, but I don't use FORMAT at all. I've seen some benchmarking of it against the equivalent CONVERT function and FORMAT is up to 44x slower.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 8

sipas - Tuesday, October 24, 2017 3:45 AM
Never used this, but doesn't the result depend on MonthDayPattern (i.e. it will come out differently for different cultures)?
Plus you will end up with ambiguous results like 'October 17' (October 17th/October 2017).

FORMAT is locale specific, which is what we typically want for functions. We don't want to depend on the developer accounting for regional differences.

This returns values that are what the developer wants. If month and day are returned, that's what this does. If you display the year, no one at this point should be showing 17 for 2017. That doesn't seem like good design. However, if that's what your users get confused on, include the year in the function.

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 9

FORMAT() is slower than CAST/CONVERT, but has some advantages in handling different regions and more flexibility. I like it, but I'd use it judiciously.

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 10

Heh... and they say that 2 part naming of the date parts is difficult and non-obvious. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TomThomson SSC Guru Points: 104773 More actions · Answer 11

Steve Jones - SSC Editor - Tuesday, October 24, 2017 7:42 AM
FORMAT is locale specific, which is what we typically want for functions. We don't want to depend on the developer accounting for regional differences.
This returns values that are what the developer wants. If month and day are returned, that's what this does. If you display the year, no one at this point should be showing 17 for 2017. That doesn't seem like good design. However, if that's what your users get confused on, include the year in the function.

In my experience, applications' users want their formats, not the developer's preferred format . When a service supports lots of languages the developer has to write code to enforce the correct locale according to which language the particular end-user is working in. Often a service which is designed for a multi-lingual environment will have a contractual requirement to get things like date formats right, and the developer has to deliver that requirement.

Tom

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 12

TomThomson - Wednesday, October 25, 2017 4:30 AM
Steve Jones - SSC Editor - Tuesday, October 24, 2017 7:42 AM
FORMAT is locale specific, which is what we typically want for functions. We don't want to depend on the developer accounting for regional differences.
This returns values that are what the developer wants. If month and day are returned, that's what this does. If you display the year, no one at this point should be showing 17 for 2017. That doesn't seem like good design. However, if that's what your users get confused on, include the year in the function.
In my experience, applications' users want their formats, not the developer's preferred format . When a service supports lots of languages the developer has to write code to enforce the correct locale according to which language the particular end-user is working in. Often a service which is designed for a multi-lingual environment will have a contractual requirement to get things like date formats right, and the developer has to deliver that requirement.

Heh... and that's just for front-end stuff. Creating files to meet the specs of various external targets is a whole 'nuther pain in addition to the pain you mention. Considering the incredible slothfulness of FORMAT function, I'd have to be really hard pressed to ever use it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 13

TomThomson - Wednesday, October 25, 2017 4:30 AM
In my experience, applications' users want their formats, not the developer's preferred format . When a service supports lots of languages the developer has to write code to enforce the correct locale according to which language the particular end-user is working in. Often a service which is designed for a multi-lingual environment will have a contractual requirement to get things like date formats right, and the developer has to deliver that requirement.

and developers spend lots of time doing this work, perhaps only testing certain locales or formats. The idea with functions like this is to reduce the chance the developer makes a mistake.

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 14

Heh... understood and appreciated but, IMHO, the best way to decrease the chances of a Developer making mistakes is to hire good Developers instead of using tools that can cripple performance. This reminds me of the people that want to scrap UPDATE because it "allows mistakes".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)