SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dealing with custom date formats in T-SQL


Dealing with custom date formats in T-SQL

Author
Message
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22763 Visits: 13361
Comments posted to this topic are about the item Dealing with custom date formats in T-SQL

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59283 Visits: 13297
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)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22763 Visits: 13361
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59283 Visits: 13297
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 :-)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
surreydude.
surreydude.
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

Group: General Forum Members
Points: 373 Visits: 179
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
David McKinney
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2637 Visits: 2090
I think this is a great article, with a lot of tasty code to chew over.

Thanks for putting in the effort and sharing!
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22763 Visits: 13361
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". :-P

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22763 Visits: 13361
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
charles.southey
charles.southey
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 34
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
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22763 Visits: 13361
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search