Dealing with custom date formats in T-SQL

Question

Dealing with custom date formats in T-SQL

Viewing 3 posts - 31 through 32 (of 32 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Jeff Moden (3/23/2012)
Gianluca, my ol' and trusted friend,
Very nice and well written article with lots of well fomatted, easy to read, documented code. Nicely done.

Thanks, you're very kind, as usual.

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?

1. Computer specs:

CPU: Intel i7 2820QM quad-core w/ hyperthreading @2.30 GHz (3.2 GHz with turbo boost)

RAM: 16 GB

DISK: 7200 RPM

And now the tricky part: actually it's a virtual machine with the following configuration:

CPU: 2 cores

RAM: 2 GB

DISK: Virtual dynamic disk on 7200 RPM physical disk

2. Editions:

OS: Windows 2008R2 x64 Enterprise Evaluation

RDBMS: SQL Server 2012 RC0

3. A throw-away variable

4. A trace

5. 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. 😉

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

L' Eomot Inversé (3/24/2012)
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.

Thank you, Tom.

Of course you like it: the last paragraph is yours!! 😉

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
select convert(varchar(32),CONVERT(datetime2,'2012-03-23 21:00:00.000'),131)
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.

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.

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

OK, test finished and results are the following:

The same behaviour seen on 10K rows seems to apply to 1M rows.

-- Gianluca Sartori