Dealing with custom date formats in T-SQL

Question

Dealing with custom date formats in T-SQL

spaghettidba

SSC Guru

Points: 105732
More actions
March 23, 2012 at 12:04 am

#270543

Comments posted to this topic are about the item Dealing with custom date formats in T-SQL
-- Gianluca Sartori

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

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Very nice article. The FORMAT function seems very promising!

One small remark: maybe include a link to the BOL page of CONVERT you're referring to. (although it's pretty easy to find using Google/Bing :-D)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

Thank you, Koen.

The BOL page for CONVERT is linked in the "built-in method so far" section. unfortunately hyperlinks are not easy to spot both on the forums and on the articles. The font is not much different from the regular text and I decided to emphasizes it with bold.

-- Gianluca Sartori

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 3

Gianluca Sartori (3/23/2012)
Thank you, Koen.
The BOL page for CONVERT is linked in the "built-in method so far" section. unfortunately hyperlinks are not easy to spot both on the forums and on the articles. The font is not much different from the regular text and I decided to emphasizes it with bold.

Whoops, you're right :blush:

Apparently I totally missed it. Disregard my statement 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

surreydude. SSChasing Mays Points: 601 More actions · Answer 4

No mention of ISO date formats?

If the discussion is around how to present locale-specific date formats I would understand, but when converting dates an international standard must be one of the things that should be considered to detect malformed dates and prevent transposed dates.

Of course, it might just be easier to get the yanks to comply with the rest of the world in the first place!

David McKinney SSChampion Points: 10472 More actions · Answer 5

I think this is a great article, with a lot of tasty code to chew over.

Thanks for putting in the effort and sharing!

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

surreydude. (3/23/2012)
No mention of ISO date formats?
If the discussion is around how to present locale-specific date formats I would understand, but when converting dates an international standard must be one of the things that should be considered to detect malformed dates and prevent transposed dates.

ISO 8601 dates are handled by SQL Server using the CONVERT function, with styles 126 and 127.

See here for more detail: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Of course, it might just be easier to get the yanks to comply with the rest of the world in the first place!

Well, they did (sort of).

As a side note, I'm not a "yank". 😛

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

David McKinney (3/23/2012)
I think this is a great article, with a lot of tasty code to chew over.
Thanks for putting in the effort and sharing!

Thank you, David.

-- Gianluca Sartori

charles.southey SSC Enthusiast Points: 178 More actions · Answer 8

If you want a commercially-supported CLR-based utility for this our SQLUtilities product includes a wide range of date formatting & parsing functions, as well as other useful date/time, string handling and numeric functions (e.g. return a dynamically-generated sequence of dates and/or times as a table-valued function).

Charles Southey

www.totallysql.com

Charles Southey
www.totallysql.com

spaghettidba SSC Guru Points: 105732 More actions · Answer 9

charles.southey (3/23/2012)
If you want a commercially-supported CLR-based utility for this our SQLUtilities product includes a wide range of date formatting & parsing functions, as well as other useful date/time, string handling and numeric functions (e.g. return a dynamically-generated sequence of dates and/or times as a table-valued function).
Charles Southey
www.totallysql.com

Charles, I'm not sure this is a good way to promote your product.

I don't know if the editors are happy with ADs on these forums. Have you checked it with them before posting?

-- Gianluca Sartori

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 10

Gianluca Sartori (3/23/2012)
charles.southey (3/23/2012)
If you want a commercially-supported CLR-based utility for this our SQLUtilities product includes a wide range of date formatting & parsing functions, as well as other useful date/time, string handling and numeric functions (e.g. return a dynamically-generated sequence of dates and/or times as a table-valued function).
Charles Southey
www.totallysql.com
Charles, I'm not sure this is a good way to promote your product.
I don't know if the editors are happy with ADs on these forums. Have you checked it with them before posting?

CozyRoc does it all the time, and they're not banned...

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

charles.southey SSC Enthusiast Points: 178 More actions · Answer 11

Yes - it's relevant to the article and provides a possible solution for those grappling with the problem under discussion that they may be looking for. We would never post something that is not directly relevant. Not everyone wants a commercial solution - but I think it's worth knowing that it's there if you do. BTW the product is perpetually free for use on Developer Edition.

Charles Southey
www.totallysql.com

spaghettidba SSC Guru Points: 105732 More actions · Answer 12

For those who want to play with the code, you can download it from here:

SQL Server and Custom Date Formats

-- Gianluca Sartori

daviesj Grasshopper Points: 13 More actions · Answer 13

interesting article, thanks., but what about just using some variation on

select replace(convert(varchar(30), getdate(), 103, '/', '\')

( i don't have SQL server in front of me to check I've used the right format - I can't recall them all, for which reason i have a couple of UDFs like this at work...)

spaghettidba SSC Guru Points: 105732 More actions · Answer 14

daviesj (3/23/2012)
interesting article, thanks., but what about just using some variation on
select replace(convert(varchar(30), getdate(), 103, '/', '\')
( i don't have SQL server in front of me to check I've used the right format - I can't recall them all, for which reason i have a couple of UDFs like this at work...)

Using some string manipulation on built-in CONVERT styles is definitely one of the options, given that you wrap it inside a UDF (you don't want to write all that code every time you need to format, do you?).

Unfortunately, it could be quite easy to implement for a specific format and nearly impossible for others.

Moreover, from a performance standpoint, a custom CLR function outperforms all the other methods, REPLACE and variations included. If you really have to format dates, I would strongly recommend using that.

-- Gianluca Sartori