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)
😎
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho