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
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99775 Visits: 13323
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...


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
charles.southey
charles.southey
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 34
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
Gianluca Sartori
Gianluca Sartori
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38819 Visits: 13367
For those who want to play with the code, you can download it from here:

SQL Server and Custom Date Formats

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
daviesj
daviesj
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 31
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...)
Gianluca Sartori
Gianluca Sartori
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38819 Visits: 13367
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Eric M Russell
Eric M Russell
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43499 Visits: 12043
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.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Gianluca Sartori
Gianluca Sartori
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38819 Visits: 13367
Eric M Russell (3/23/2012)
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.


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.

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Eric M Russell
Eric M Russell
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43499 Visits: 12043
Gianluca Sartori (3/23/2012)
Eric M Russell (3/23/2012)
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.


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.

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.

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



insert into foo (foo_date) values ('2011/02/28');


Error 547: The INSERT statement conflicted with the CHECK constraint "ck_foo_date_yyyymmdd".
Satisfied

insert into foo (foo_date) values ('20110229');


Error 242: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Satisfied

insert into foo (foo_date) values ('20120229');


(1 row(s) affected)
Cool


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
gclausen
gclausen
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: 264
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.,
Gianluca Sartori
Gianluca Sartori
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38819 Visits: 13367
gclausen (3/23/2012)
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.,


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.

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