April 3, 2015 at 7:44 am
I have a table that has a date of birth column. Unfortunately its a varchar data type. I would like to convert this column in a view to a date time data type.
Any ideas on how to do this ?
Thanks
April 3, 2015 at 7:49 am
Marv2011 (4/3/2015)
I have a table that has a date of birth column. Unfortunately its a varchar data type. I would like to convert this column in a view to a date time data type.Any ideas on how to do this ?
Thanks
Quick answer is that it depends on the format and the consistency of the data, post a DDL (create table) and some sample data as an insert statement and we'll take it from there.
😎
April 3, 2015 at 11:17 am
You should be able to simply run CAST against the data to put it into the right data type in the view. But, if the data is not formatted properly, that can fail. I'd test it using ISDATE
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 3, 2015 at 11:38 am
Grant Fritchey (4/3/2015)
You should be able to simply run CAST against the data to put it into the right data type in the view. But, if the data is not formatted properly, that can fail. I'd test it using ISDATE
Simple solution, just force the ISO date format and reject anything else. May not be the solution expected but it's a lot less painful on the db side.
😎
On a more serious note, the absence of standards or at least guidelines is a huge problem. Huge amount of errors on all levels of data processing can be avoided by simply taking a little time to define simple guidelines such as which format to use.
Quick question Grant, how many times have you seen systems/processes fail on the 13th of the month? I can count a good handful.
April 3, 2015 at 11:39 am
As a side note, without a constraint on this VarChar "date" column it will be a constant struggle to get users to enter data consistently and any conversion functions you use in the view will sporatically fail. Also, localization settings can effect how strings convert to date, which can be an issue if your exchanging data outside your organization.
The ISO standard for formatting date strings is YYYYMMDD, and the following check constaint will not only require that the string be formatted correctly, but it will also require that the value be a valid calendar date.
create table foo
(
foo_date varchar(30) not null
constraint ck_foo_date_yyyymmdd
check (foo_date = convert(char(8),cast(foo_date as date),112))
);
insert into foo (foo_date) values ('2011/02/28');
The INSERT statement conflicted with the CHECK constraint "ck_foo_date_yyyymmdd".
Feb 29, 2011 is an invalid date.
insert into foo (foo_date) values ('20110229');
Conversion failed when converting date and/or time from character string.
However, 2012 is a leap year, so Feb 29, 2012 is valid. By leveraging the CAST() function within the check constraint, you don't have to mess with a table of valid dates to do this.
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
April 3, 2015 at 12:16 pm
Quick question Grant, how many times have you seen systems/processes fail on the 13th of the month? I can count a good handful.
My favorite was coming across a system that would generate leap year dates in february for non leap year years. Presumably it was just adding 1 to the month in january 🙂
April 3, 2015 at 1:03 pm
ZZartin (4/3/2015)
Quick question Grant, how many times have you seen systems/processes fail on the 13th of the month? I can count a good handful.
My favorite was coming across a system that would generate leap year dates in february for non leap year years. Presumably it was just adding 1 to the month in january 🙂
Good point, often out measures which are meant to deal with "anomalies" throw off our normal logic, this is a good example!
😎
April 3, 2015 at 3:23 pm
Eirikur Eiriksson (4/3/2015)
Grant Fritchey (4/3/2015)
You should be able to simply run CAST against the data to put it into the right data type in the view. But, if the data is not formatted properly, that can fail. I'd test it using ISDATESimple solution, just force the ISO date format and reject anything else. May not be the solution expected but it's a lot less painful on the db side.
😎
On a more serious note, the absence of standards or at least guidelines is a huge problem. Huge amount of errors on all levels of data processing can be avoided by simply taking a little time to define simple guidelines such as which format to use.
Quick question Grant, how many times have you seen systems/processes fail on the 13th of the month? I can count a good handful.
Yeah, not getting the simple stuff like data types right really does mess with stuff. I've seen so many different issues around this.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 9, 2015 at 1:49 pm
ZZartin (4/3/2015)
Quick question Grant, how many times have you seen systems/processes fail on the 13th of the month? I can count a good handful.
In my last job I managed a legal case management system that automatically number its new cases as: [YYYY] plus a sequential integer masked with zeroes: '201400001', 201400345', etc.
Once a year we get to the point where the integer portion was the same as the year ('201502015'), and SQL Server would lock up and require a restart. Then I'd remove the bad row and increment the 'next row' counter and all would be well. Never could figure out why. Almost had me believing in poltergeists.
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
April 9, 2015 at 1:58 pm
Sigerson (4/9/2015)
ZZartin (4/3/2015)
Quick question Grant, how many times have you seen systems/processes fail on the 13th of the month? I can count a good handful.
In my last job I managed a legal case management system that automatically number its new cases as: [YYYY] plus a sequential integer masked with zeroes: '201400001', 201400345', etc.
Once a year we get to the point where the integer portion was the same as the year ('201502015'), and SQL Server would lock up and require a restart. Then I'd remove the bad row and increment the 'next row' counter and all would be well. Never could figure out why. Almost had me believing in poltergeists.
He he, implicit conversion gone as sour as a collapsed souffle 😉
😎
April 10, 2015 at 8:16 am
Sigerson (4/9/2015)
ZZartin (4/3/2015)
Quick question Grant, how many times have you seen systems/processes fail on the 13th of the month? I can count a good handful.
In my last job I managed a legal case management system that automatically number its new cases as: [YYYY] plus a sequential integer masked with zeroes: '201400001', 201400345', etc.
Once a year we get to the point where the integer portion was the same as the year ('201502015'), and SQL Server would lock up and require a restart. Then I'd remove the bad row and increment the 'next row' counter and all would be well. Never could figure out why. Almost had me believing in poltergeists.
I suspect it had something to do with the expression you were using to calculate the next ID and implicit data type conversion, and I understand the stored proceure throwing an error, but SQL Server itself locking up? From what you recall, basically what was the DDL; was it essentially just an integer column?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 13, 2015 at 9:27 am
Eric M Russell (4/10/2015)
From what you recall, basically what was the DDL; was it essentially just an integer column?
It was a VARCHAR built from:
1 - 4 = DATEPART(yyyy,GETDATE)
5 - 9 = VARCHAR(5) composed of
a) the last 5 chars of the previous matter number CAST as an INT '02014' --> 2014
b) formatted back into a right-justified, zero-masked VARCHAR --> '02014'
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply