Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Dealing with custom date formats in T-SQL Expand / Collapse
Author
Message
Posted Friday, March 23, 2012 3:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 13,251, Visits: 11,030
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1271485
Posted Friday, March 23, 2012 4:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 7, 2013 3:46 PM
Points: 6, 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
Post #1271492
Posted Friday, March 23, 2012 6:03 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:24 AM
Points: 4,388, Visits: 10,674
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
Post #1271544
Posted Friday, March 23, 2012 6:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 6:35 PM
Points: 3, Visits: 30
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...)

Post #1271551
Posted Friday, March 23, 2012 6:39 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:24 AM
Points: 4,388, Visits: 10,674
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
Post #1271566
Posted Friday, March 23, 2012 6:47 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 1,706, Visits: 4,847
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.
Post #1271575
Posted Friday, March 23, 2012 7:01 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:24 AM
Points: 4,388, Visits: 10,674
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
Post #1271593
Posted Friday, March 23, 2012 7:37 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 1,706, Visits: 4,847
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".


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.


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

(1 row(s) affected)
Post #1271642
Posted Friday, March 23, 2012 8:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 29, 2014 11:11 AM
Points: 5, Visits: 205
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.,
Post #1271685
Posted Friday, March 23, 2012 8:26 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:24 AM
Points: 4,388, Visits: 10,674
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
Post #1271690
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse