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 (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23137 Visits: 13362
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Attachments
Assembly.sql.txt (20 views, 12.00 KB)
harness.sql.txt (23 views, 5.00 KB)
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23137 Visits: 13362
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

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

Group: General Forum Members
Points: 23137 Visits: 13362
OK, test finished and results are the following:





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

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Attachments
Table.png (85 views, 10.00 KB)
chart.png (85 views, 27.00 KB)
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