SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Gianluca Sartori / Dealing with custom date formats in T-SQL / Latest Posts

RE: Dealing with custom date formats in T-SQL

spaghettidba — Mon, 26 Mar 2012 10:31:02 GMT

OK, test finished and results are the following:[img]http://www.sqlservercentral.com/Forums/Attachment11161.aspx[/img][img]http://www.sqlservercentral.com/Forums/Attachment11162.aspx[/img]The same behaviour seen on 10K rows seems to apply to 1M rows.

RE: Dealing with custom date formats in T-SQL

spaghettidba — Mon, 26 Mar 2012 09:57:43 GMT

[quote][b]L' Eomot Inversé (3/24/2012)[/b][hr]Really good article, I like it very much. So please don't take the following as suggesting that it isn't, because that would be utterly wrong.[/quote]Thank you, Tom.Of course you like it: the last paragraph is yours!! ;-)[quote]There are a couple of things that perhaps should have been mentioned (to warn people that they need to be considered when relevant, not to offer solutions in the article because covering everyting would make the article too long and complicated and anyway most people don't need to worry about either of these things).1) Everything is varchar. But I may want to be able to handle languages that don't work with varchar (256 is quite a small number, there are non-alphabetic languages that have to be coped with and 256 characters is sometimes far too few). I don't think there's any need to cope with more than one language at a time, but if there were that would make it even harder to fit into varchar.2) None of your functions will work if you are outside the Gregorian calendar. We can use T-SQL cast/convert to get Kuwaiti Hijri, as for example[code]select convert(varchar(32),CONVERT(datetime2,'2012-03-23 21:00:00.000'),131)[/code]using SQL Server's capability to handle Kuwaiti dates (but I wouldn't bet on it delivering correct dates in the future, as one form or another of fundamentalist may succede in forcing a change in Kuwaiti dates). But this will not work for Saudi format, or Iranian format, or.... (the year numbers will be different hardly ever, the month numbers will be different a little of the time, the day numbers are likely to be different a lot of the time). So the argument that built in date handling by CONVERT solves the Hijri problem doesn't work, because only Kuwaiti dates can be handled. Islamic Hijri isn't the only calendar in current use whose months and years don't match up with the Gregorian calendar, even the Julian calendar would be a trivial example if anyone actually used it any more.So people need to watch out for alphabets which don't have 256 or fewer characters and for calendars which have different (possibly variable) year lengths and different (possibly variable) month lengths from our Western culture.[/quote]You're right, Tom. I think that both problems should not be handled in T-SQL directly, as it's not the right tool for this task IMHO. The code is already complicated enough and also suffers from other limitations that I didn't mention. For instance, I don't know if any language around the world has month names made of multiple words: that would not parse correctly. The same can be said for weekday names.T-SQL is good for a lot of other things, but I would definitely avoid it for this task.

RE: Dealing with custom date formats in T-SQL

spaghettidba — Mon, 26 Mar 2012 09:51:29 GMT

[quote][b]Jeff Moden (3/23/2012)[/b][hr]Gianluca, my ol' and trusted friend,Very nice and well written article with lots of well fomatted, easy to read, documented code. Nicely done.[/quote]Thanks, you're very kind, as usual.[quote]Shifting gears, I have a couple of questions, please.1. What is the configuration and speed of the computer you did the testing on?2. Which edition and version of SQL Server and Windows did you run your tests on?3. When you did your duration testing, where did you have the result set return to? A table? Grid mode on the screen? A throw-away variable? or ???4. When you did your duration testing, how did you measure the duration? STATISTICS TIME ON? SQL Profiler? or ???5. When you did your duration testing, what was the configuration of your test table? Single column of dates? Indexes or not?6. Is there any chance of you attaching your test data generator and your test harness to this thread so I can make sure that my testing is done the same way you did it?7. Since I don't even know how to spell C#, would it be possible for you to script the CLR assembly out in a "Paul White" fashion so that I could install it on my machine for a little testing of my own?[/quote]1. Computer specs:CPU: Intel i7 2820QM quad-core w/ hyperthreading @2.30 GHz (3.2 GHz with turbo boost)RAM: 16 GBDISK: 7200 RPMAnd now the tricky part: actually it's a virtual machine with the following configuration:CPU: 2 coresRAM: 2 GBDISK: Virtual dynamic disk on 7200 RPM physical disk2. Editions:OS: Windows 2008R2 x64 Enterprise Evaluation RDBMS: SQL Server 2012 RC03. A throw-away variable4. A trace5. Single column table, with no indexes.6. Attached to this post.7. Attached as well.I'm repeating the tests using this test harness, but it definitely takes time. The code repeats the tests on 10K, 100K and 1M rows for each method and that takes about 2 hours on my machine (excluding the recursive ITVF that would take waaaaay too long).I'll post the results as soon as I am ready.Thanks again, Jeff! I hope you find something I overlooked. ;-)

RE: Dealing with custom date formats in T-SQL

spaghettidba — Mon, 26 Mar 2012 09:18:22 GMT

[quote][b]Steven Willis (3/23/2012)[/b][hr]Excellent functions. I've already tested them on my own server (2008) and added them to my toolbox of functions. In case you are interested, I added a tiny modification to both functions to handle Day Name Suffixes such as "1st," "2nd," "3rd," and "4th." This code piece can just be added right at the end of the "formattedDate" SELECT statement....Now a mask like this can be used 'DDDD, MMMM DDx, YYYY' to produce 'Friday, March 23rd, 2012'.[/quote]Thank you, Steven. This could be a valuable addition to the code.However, I tried to stick to the date format placeholders you can find on [b][url=http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx]this page[/url][/b] on MSDN. I tried parsing a date containing the day name suffix with the built-in PARSE function and it did not work.[quote]Also, the difference between 'hh' and 'h' wasn't clear and didn't seem to produce different results no matter what dates I used for input. Since the 'MM' vs 'M', 'DD' vs 'D', 'HH' vs 'H' all produced the month or day with or without a leading zero respectively, I would assume the same would be the case for 'hh' and 'h'. So I modified the code as:[code="sql"]WHEN 'h' THEN CASE WHEN LEFT( RIGHT('00' + CAST( CASE DATEPART(hour,@date) WHEN 12 THEN 12 ELSE DATEPART(hour,@date) % 12 END AS NVARCHAR(2)),2),1) = 0 THEN RIGHT('00' + CAST( CASE DATEPART(hour,@date) WHEN 12 THEN 12 ELSE DATEPART(hour,@date) % 12 END AS NVARCHAR(2)),1) ELSE RIGHT('00' + CAST( CASE DATEPART(hour,@date) WHEN 12 THEN 12 ELSE DATEPART(hour,@date) % 12 END AS NVARCHAR(2)),2) END[/code]Perhaps there is a more efficient way of doing this, but I basically just took the code for 'hh' formatting and turned it into a case statement. [/quote]Nice catch, thank you.

RE: Dealing with custom date formats in T-SQL

L' Eomot Inversé — Sat, 24 Mar 2012 20:13:53 GMT

Really good article, I like it very much. So please don't take the following as suggesting that it isn't, because that would be utterly wrong.There are a couple of things that perhaps should have been mentioned (to warn people that they need to be considered when relevant, not to offer solutions in the article because covering everyting would make the article too long and complicated and anyway most people don't need to worry about either of these things).1) Everything is varchar. But I may want to be able to handle languages that don't work with varchar (256 is quite a small number, there are non-alphabetic languages that have to be coped with and 256 characters is sometimes far too few). I don't think there's any need to cope with more than one language at a time, but if there were that would make it even harder to fit into varchar.2) None of your functions will work if you are outside the Gregorian calendar. We can use T-SQL cast/convert to get Kuwaiti Hijri, as for example[code]select convert(varchar(32),CONVERT(datetime2,'2012-03-23 21:00:00.000'),131)[/code]using SQL Server's capability to handle Kuwaiti dates (but I wouldn't bet on it delivering correct dates in the future, as one form or another of fundamentalist may succede in forcing a change in Kuwaiti dates). But this will not work for Saudi format, or Iranian format, or.... (the year numbers will be different hardly ever, the month numbers will be different a little of the time, the day numbers are likely to be different a lot of the time). So the argument that built in date handling by CONVERT solves the Hijri problem doesn't work, because only Kuwaiti dates can be handled. Islamic Hijri isn't the only calendar in current use whose months and years don't match up with the Gregorian calendar, even the Julian calendar would be a trivial example if anyone actually used it any more.So people need to watch out for alphabets which don't have 256 or fewer characters and for calendars which have different (possibly variable) year lengths and different (possibly variable) month lengths from our Western culture.

RE: Dealing with custom date formats in T-SQL

Lisa Slater Nicholls — Fri, 23 Mar 2012 22:14:14 GMT

[quote]The only bad part about Charles doing the testing is having the proverbial mouse guard the cheese. [/quote]Actually, Jeff, in this case, I look at it as "put up or shut up" ;-). Regardless of the author's original computer specs (although that made me curious too, thanks for asking hiim), I'd like to see the vendor run the code in this article, on any machine or set of machines, plus his own version, and provide all stats on a level playing field. Here's a case where we *know* the test was not vendor-designed. If Charles runs exactly that code, plus his, in like conditions (and I invite you to add conditions: start sql server up cold? anything?), we should really learn something.>L<

RE: Dealing with custom date formats in T-SQL

Jeff Moden — Fri, 23 Mar 2012 19:29:36 GMT

Gianluca, my ol' and trusted friend,Very nice and well written article with lots of well fomatted, easy to read, documented code. Nicely done.Shifting gears, I have a couple of questions, please.1. What is the configuration and speed of the computer you did the testing on?2. Which edition and version of SQL Server and Windows did you run your tests on?3. When you did your duration testing, where did you have the result set return to? A table? Grid mode on the screen? A throw-away variable? or ???4. When you did your duration testing, how did you measure the duration? STATISTICS TIME ON? SQL Profiler? or ???5. When you did your duration testing, what was the configuration of your test table? Single column of dates? Indexes or not?6. Is there any chance of you attaching your test data generator and your test harness to this thread so I can make sure that my testing is done the same way you did it?7. Since I don't even know how to spell C#, would it be possible for you to script the CLR assembly out in a "Paul White" fashion so that I could install it on my machine for a little testing of my own?

RE: Dealing with custom date formats in T-SQL

Jeff Moden — Fri, 23 Mar 2012 18:37:11 GMT

[quote][b]Lisa Slater Nicholls (3/23/2012)[/b][hr][quote][b]charles.southey (3/23/2012)[/b][hr]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.[/quote]OK, Charles, since you're here with your potential solution... how does your product stack up against the author's custom CLR function and the rest? When doing exactly the task(s) described in the article, how long does it take?I'm sure there are other potential comparisons (flexibility, ease of use, etc), and if you discussed those here I'd consider it an advertisement for sure. But speed is really a comparison that would add to this conversation, IMHO, without being an "ad". The perf disparity between FORMAT() and the custom function is very interesting and has made me very curious. Is there some general pattern here, what can we learn?Thanks in advance,>L<[/quote]Oh My! :-) There's another "me" out there! I was going to ask Charles for his performance stats on 10,000, 100,000, and 1 million rows of dates and even offer to build the test table for him. Well done, Lisa!The only bad part about Charles doing the testing is having the proverbial mouse guard the cheese. ;-)

RE: Dealing with custom date formats in T-SQL

Lisa Slater Nicholls — Fri, 23 Mar 2012 16:09:03 GMT

[quote][b]charles.southey (3/23/2012)[/b][hr]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.[/quote]OK, Charles, since you're here with your potential solution... how does your product stack up against the author's custom CLR function and the rest? When doing exactly the task(s) described in the article, how long does it take?I'm sure there are other potential comparisons (flexibility, ease of use, etc), and if you discussed those here I'd consider it an advertisement for sure. But speed is really a comparison that would add to this conversation, IMHO, without being an "ad". The perf disparity between FORMAT() and the custom function is very interesting and has made me very curious. Is there some general pattern here, what can we learn?Thanks in advance,>L<

RE: Dealing with custom date formats in T-SQL

Steven Willis — Fri, 23 Mar 2012 12:04:42 GMT

Excellent functions. I've already tested them on my own server (2008) and added them to my toolbox of functions. In case you are interested, I added a tiny modification to both functions to handle Day Name Suffixes such as "1st," "2nd," "3rd," and "4th." This code piece can just be added right at the end of the "formattedDate" SELECT statement.[code="sql"]WHEN 'x' THEN CASE WHEN CAST(DAY(@date) AS INT) IN (1,21,31) THEN N'st' WHEN CAST(DAY(@date) AS INT) IN (2,22) THEN N'nd' WHEN CAST(DAY(@date) AS INT) IN (3,23) THEN N'rd' ELSE N'th' END[/code]Now a mask like this can be used 'DDDD, MMMM DDx, YYYY' to produce 'Friday, March 23rd, 2012'.Also, the difference between 'hh' and 'h' wasn't clear and didn't seem to produce different results no matter what dates I used for input. Since the 'MM' vs 'M', 'DD' vs 'D', 'HH' vs 'H' all produced the month or day with or without a leading zero respectively, I would assume the same would be the case for 'hh' and 'h'. So I modified the code as:[code="sql"]WHEN 'h' THEN CASE WHEN LEFT( RIGHT('00' + CAST( CASE DATEPART(hour,@date) WHEN 12 THEN 12 ELSE DATEPART(hour,@date) % 12 END AS NVARCHAR(2)),2),1) = 0 THEN RIGHT('00' + CAST( CASE DATEPART(hour,@date) WHEN 12 THEN 12 ELSE DATEPART(hour,@date) % 12 END AS NVARCHAR(2)),1) ELSE RIGHT('00' + CAST( CASE DATEPART(hour,@date) WHEN 12 THEN 12 ELSE DATEPART(hour,@date) % 12 END AS NVARCHAR(2)),2) END[/code]Perhaps there is a more efficient way of doing this, but I basically just took the code for 'hh' formatting and turned it into a case statement.Now, to be thorough, I also added to the masking function of the ParseString function to allow for filtering out Day Name suffixes:Added to the 'allowedTokens'[code="sql"]UNION ALL SELECT 'x' ,'DAYSUFFIX'[/code]Added to the 'pivotedTokens'[code="sql"],daysuffix_value = ''[/code]and[code="sql"],[DAYSUFFIX][/code]Added to the 'verifiedTokens' pivot list[code="sql"],[DAYSUFFIX][/code]WITHOUT these changes this: 'October 26th 2011', 'MMMM DD YYYY' will cause an errorWITH these changes this: 'October 26th 2011', 'MMMM DDx YYYY' parses the date successfully

RE: Dealing with custom date formats in T-SQL

spaghettidba — Fri, 23 Mar 2012 08:51:53 GMT

[quote][b]sknox (3/23/2012)[/b][hr]Very nice article. It provides a set of useful solutions to a common issue, while also highlighting the fact that some common "mantras" (i.e, Inline TVFs are more efficient than scalar UDFs) are not always true.I'd have liked to see some charts showing how the different solutions scale from 1 to millions of rows, but that's really not something I feel should be required of a short article like this, and you've provided enough information for me to test this on my own if I want to take the time.Finally one question: did you do your testing on a preview or RTM version of SQL Server 2012? If it's a preview version, there [b]might[/b] be some debugging code slowing down the new function.[/quote]Ha! Nice catch.No, I tested the code with CTP3 and RC0, because RTM was not ready yet at the time. As far as the chart is concerned, 20 seconds of Excel produced this:[img]http://www.sqlservercentral.com/Forums/Attachment11139.aspx[/img]

RE: Dealing with custom date formats in T-SQL

sknox — Fri, 23 Mar 2012 08:38:48 GMT

Very nice article. It provides a set of useful solutions to a common issue, while also highlighting the fact that some common "mantras" (i.e, Inline TVFs are more efficient than scalar UDFs) are not always true.I'd have liked to see some charts showing how the different solutions scale from 1 to millions of rows, but that's really not something I feel should be required of a short article like this, and you've provided enough information for me to test this on my own if I want to take the time.Finally one question: did you do your testing on a preview or RTM version of SQL Server 2012? If it's a preview version, there [b]might[/b] be some debugging code slowing down the new function.

RE: Dealing with custom date formats in T-SQL

gclausen — Fri, 23 Mar 2012 08:28:48 GMT

Agree - it just depends on the task at hand.

RE: Dealing with custom date formats in T-SQL

spaghettidba — Fri, 23 Mar 2012 08:26:52 GMT

[quote][b]gclausen (3/23/2012)[/b][hr]Very nice article. I was especially interested where you showed that CLR methods performed much better. [b]I've heard this about CLR performing better in most cases[/b].,[/quote]Well, it depends on the task you have to accomplish.When it comes down to complex string manipulations, CLR is nearly always the best choice.The same can't be said for data manipulation.

RE: Dealing with custom date formats in T-SQL

gclausen — Fri, 23 Mar 2012 08:20:27 GMT

Very nice article. I was especially interested where you showed that CLR methods performed much better. I've heard this about CLR performing better in most cases.,

RE: Dealing with custom date formats in T-SQL

Eric M Russell — Fri, 23 Mar 2012 07:37:45 GMT

[quote][b]Gianluca Sartori (3/23/2012)[/b][hr][quote][b]Eric M Russell (3/23/2012)[/b][hr]Formatting dates in the presentation layer is best, because how the application wants dates formatted and how reporting wants dates formatted will be different, even if you get them to initially agree on a standard. Still, formatting dates within the SELECT statement is tolerable and sometimes necessary.However, containing date/time values in VARCHAR columns is perhaps one of the worst database anti-patterns ever. Not only does it require more effort to insure that all applications INSERT using a consistent format, but VARCHAR "dates" consume more storage space, and depending on the format, it often requires the use non-indexable conversion expressions in the WHERE clause.[/quote]I completely agree.Not to mention the inability to sort properly, perform date/time specific calculations, check against invalid values (february 29 on non-leap years etc.)... and I could go on forever!Another date/time anti-pattern that I saw implemented in some shops is a separate column for each date part: one (int!!!!!!!) column for the year, one column for the month etc... Fits well with a DWH calendar table, but is a horrible choice for OLTP.[/quote]When confronted with a VARCHAR date, it is still possible for us to impose our will upon it at the DDL level. The following check constraint not only requires the value to be coded in the format YYYYMMDD, but it will require that the value be a valid calendar date. For example, it rejects an insert of February 29, 2011 but accepts February 29, 2012.[code="sql"]create table foo( foo_date varchar(30) not null constraint ck_foo_date_yyyymmdd check (foo_date = convert(char(8),cast(foo_date as datetime),112)));[/code][code="sql"]insert into foo (foo_date) values ('2011/02/28');[/code][b]Error 547: The INSERT statement conflicted with the CHECK constraint "ck_foo_date_yyyymmdd".[/b]:satisfied:[code="sql"]insert into foo (foo_date) values ('20110229');[/code][b]Error 242: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.[/b]:satisfied:[code="sql"]insert into foo (foo_date) values ('20120229');[/code][b](1 row(s) affected)[/b]:cool:

RE: Dealing with custom date formats in T-SQL

spaghettidba — Fri, 23 Mar 2012 07:01:42 GMT

[quote][b]Eric M Russell (3/23/2012)[/b][hr]Formatting dates in the presentation layer is best, because how the application wants dates formatted and how reporting wants dates formatted will be different, even if you get them to initially agree on a standard. Still, formatting dates within the SELECT statement is tolerable and sometimes necessary.However, containing date/time values in VARCHAR columns is perhaps one of the worst database anti-patterns ever. Not only does it require more effort to insure that all applications INSERT using a consistent format, but VARCHAR "dates" consume more storage space, and depending on the format, it often requires the use non-indexable conversion expressions in the WHERE clause.[/quote]I completely agree.Not to mention the inability to sort properly, perform date/time specific calculations, check against invalid values (february 29 on non-leap years etc.)... and I could go on forever!Another date/time anti-pattern that I saw implemented in some shops is a separate column for each date part: one (int!!!!!!!) column for the year, one column for the month etc... Fits well with a DWH calendar table, but is a horrible choice for OLTP.

RE: Dealing with custom date formats in T-SQL

Eric M Russell — Fri, 23 Mar 2012 06:47:00 GMT

Formatting dates in the presentation layer is best, because how the application wants dates formatted and how reporting wants dates formatted will be different, even if you get them to initially agree on a standard. Still, formatting dates within the SELECT statement is tolerable and sometimes necessary.However, containing date/time values in VARCHAR columns is perhaps one of the worst database anti-patterns ever. Not only does it require more effort to insure that all applications INSERT using a consistent format, but VARCHAR "dates" consume more storage space, and depending on the format, it often requires the use non-indexable conversion expressions in the WHERE clause.

RE: Dealing with custom date formats in T-SQL

spaghettidba — Fri, 23 Mar 2012 06:39:31 GMT

[quote][b]daviesj (3/23/2012)[/b][hr]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...)[/quote]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.

RE: Dealing with custom date formats in T-SQL

daviesj — Fri, 23 Mar 2012 06:23:00 GMT

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...)

RE: Dealing with custom date formats in T-SQL

spaghettidba — Fri, 23 Mar 2012 06:03:45 GMT

For those who want to play with the code, you can download it from here:[b][url=http://t.co/ZdGzSD0s]SQL Server and Custom Date Formats[/url][/b]

RE: Dealing with custom date formats in T-SQL

charles.southey — Fri, 23 Mar 2012 04:19:36 GMT

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.

RE: Dealing with custom date formats in T-SQL

Koen Verbeeck — Fri, 23 Mar 2012 03:55:36 GMT

[quote][b]Gianluca Sartori (3/23/2012)[/b][hr][quote][b]charles.southey (3/23/2012)[/b][hr]If you want a commercially-supported CLR-based utility for this our [url=http://www.totallysql.com/products/sqlutilities]SQLUtilities[/url] 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[url=http://www.totallysql.com]www.totallysql.com[/url][/quote]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?[/quote]CozyRoc does it all the time, and they're not banned...

RE: Dealing with custom date formats in T-SQL

spaghettidba — Fri, 23 Mar 2012 03:50:22 GMT

[quote][b]charles.southey (3/23/2012)[/b][hr]If you want a commercially-supported CLR-based utility for this our [url=http://www.totallysql.com/products/sqlutilities]SQLUtilities[/url] 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[url=http://www.totallysql.com]www.totallysql.com[/url][/quote]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?

RE: Dealing with custom date formats in T-SQL

charles.southey — Fri, 23 Mar 2012 03:31:59 GMT

If you want a commercially-supported CLR-based utility for this our [url=http://www.totallysql.com/products/sqlutilities]SQLUtilities[/url] 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[url=http://www.totallysql.com]www.totallysql.com[/url]

RE: Dealing with custom date formats in T-SQL

spaghettidba — Fri, 23 Mar 2012 03:14:36 GMT

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

RE: Dealing with custom date formats in T-SQL

spaghettidba — Fri, 23 Mar 2012 03:13:56 GMT

[quote][b]surreydude. (3/23/2012)[/b][hr]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.[/quote]ISO 8601 dates are handled by SQL Server using the CONVERT function, with styles 126 and 127. See here for more detail: [url]http://msdn.microsoft.com/en-us/library/ms187928.aspx[/url][quote]Of course, it might just be easier to get the yanks to comply with the rest of the world in the first place![/quote]Well, they did (sort of).As a side note, I'm not a "yank". :-P

RE: Dealing with custom date formats in T-SQL

David McKinney — Fri, 23 Mar 2012 03:10:14 GMT

I think this is a great article, with a lot of tasty code to chew over.Thanks for putting in the effort and sharing!

RE: Dealing with custom date formats in T-SQL

surreydude. — Fri, 23 Mar 2012 01:51:04 GMT

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!

RE: Dealing with custom date formats in T-SQL

Koen Verbeeck — Fri, 23 Mar 2012 01:27:33 GMT

[quote][b]Gianluca Sartori (3/23/2012)[/b][hr]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.[/quote]Whoops, you're right :blush:Apparently I totally missed it. Disregard my statement :-)

RE: Dealing with custom date formats in T-SQL

spaghettidba — Fri, 23 Mar 2012 01:25:15 GMT

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.

RE: Dealing with custom date formats in T-SQL

Koen Verbeeck — Fri, 23 Mar 2012 00:30:58 GMT

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)

Dealing with custom date formats in T-SQL

spaghettidba — Fri, 23 Mar 2012 00:04:50 GMT

Comments posted to this topic are about the item [B]Dealing with custom date formats in T-SQL[/B]