| Groups | Blog | Home |
sql server programming : converting from varchar to datetime
data mmddyy stored in a varchar column to datetime datatype? Any suggestions, statements are welcomed. Thanks!! Alex
DECLARE @dt VARCHAR(6)
SET @dt='053004' SELECT CONVERT(DATETIME,RIGHT(@dt,2)+LEFT(@dt,4),112) Because you have a two digit year number this conversion is sensitive to the two digit year cutoff setting. By default that is set to 2049 so if you have any dates earlier than 1950 or later than 2049 they won't be converted correctly unless you change that setting. -- David Portas SQL Server MVP --
Another way is to use stuff... assuming local settings are MDY:
SELECT CONVERT(SMALLDATETIME, STUFF(STUFF('053004', 3, 0, '-'), 6, 0, '-')) Same caveat as David mention applies, obviously... if you only have two digits representing the year, SQL Server must decide what century years like 49 and 50 belong to. If your dateformat is DMY, you will get this: Server: Msg 296, Level 16, State 3, Line 2 The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value. You should really, really, really consider storing your data correctly on the way in, instead of kludging it on the way out. -- Aaron Bertrand SQL Server MVP http://www.aspfaq.com/ (Reverse e-mail to reply.) [quoted text, click to view] "Alex Ivascu" <alexdivascu@yahoo.com> wrote in message news:ugssnVgREHA.2972@TK2MSFTNGP09.phx.gbl... > Does someone have any good pointers that can help me in converting > existing > data mmddyy stored in a varchar column to datetime datatype? Any > suggestions, statements are welcomed. > > Thanks!! > > Alex > >
As always, thanks David. But, I have another question...
Since the data is all garbage, and some of the data can consist of 04/23/00 or some 0400 format, can I specifically search for data that contains only 6-characters? Thanks again. [quoted text, click to view] "David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message news:QYCdnWGwWunM5STdRVn-jA@giganews.com... > DECLARE @dt VARCHAR(6) > SET @dt='053004' > > SELECT CONVERT(DATETIME,RIGHT(@dt,2)+LEFT(@dt,4),112) > > Because you have a two digit year number this conversion is sensitive to the > two digit year cutoff setting. By default that is set to 2049 so if you have > any dates earlier than 1950 or later than 2049 they won't be converted > correctly unless you change that setting. > > -- > David Portas > SQL Server MVP > -- > >
WHERE LEN(column_name) = 6 ?
-- Aaron Bertrand SQL Server MVP http://www.aspfaq.com/ (Reverse e-mail to reply.) [quoted text, click to view] "Alex Ivascu" <alexdivascu@yahoo.com> wrote in message news:eeuCSRsREHA.1348@TK2MSFTNGP12.phx.gbl... > As always, thanks David. But, I have another question... > > Since the data is all garbage, and some of the data can consist of > 04/23/00 > or some 0400 format, can I specifically search for data that contains only > 6-characters?
Thank you, Aaron.
[quoted text, click to view] "Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:#IdG5isREHA.3528@TK2MSFTNGP09.phx.gbl... > WHERE LEN(column_name) = 6 ? > > -- > Aaron Bertrand > SQL Server MVP > http://www.aspfaq.com/ > (Reverse e-mail to reply.) > > > > > "Alex Ivascu" <alexdivascu@yahoo.com> wrote in message > news:eeuCSRsREHA.1348@TK2MSFTNGP12.phx.gbl... > > As always, thanks David. But, I have another question... > > > > Since the data is all garbage, and some of the data can consist of > > 04/23/00 > > or some 0400 format, can I specifically search for data that contains only > > 6-characters? > >
Aaron or David;
I must be missing something, since these records aren't getting updated. Do I have to assign the convert function first? declare c1 cursor local fast_forward for select intId, dob from donor where len(dob)=6 begin declare @intId int, @dob nvarchar(50) open c1 fetch next from c1 into @intid, @dob while (@@fetch_status=0) begin update donor set dob = convert (datetime, right(@dob,2)+left(@dob,4),112) where intId= @intId fetch next from c1 into @intid, @dob end close c1 end [quoted text, click to view] "Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:#IdG5isREHA.3528@TK2MSFTNGP09.phx.gbl... > WHERE LEN(column_name) = 6 ? > > -- > Aaron Bertrand > SQL Server MVP > http://www.aspfaq.com/ > (Reverse e-mail to reply.) > > > > > "Alex Ivascu" <alexdivascu@yahoo.com> wrote in message > news:eeuCSRsREHA.1348@TK2MSFTNGP12.phx.gbl... > > As always, thanks David. But, I have another question... > > > > Since the data is all garbage, and some of the data can consist of > > 04/23/00 > > or some 0400 format, can I specifically search for data that contains only > > 6-characters? > >
Thanks for trying to help!
It seems that this data is REALLY bad. Unfortunately, some of the 6-len characters are strings. Can I bypass this? Only search and update 6-len int's? Server: Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type datetime. The statement has been terminated. [quoted text, click to view] "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:mp7nb0h1f4kstugm03e9oaho1d1ldsnn7u@4ax.com... > On Mon, 31 May 2004 07:17:44 -0700, Alex Ivascu wrote: > > >Aaron or David; > > > >I must be missing something, since these records aren't getting updated. Do > >I have to assign the convert function first? > > > >declare > > c1 cursor local fast_forward for > > select intId, dob > > from donor > > where len(dob)=6 > >begin > > declare @intId int, @dob nvarchar(50) > > open c1 > > fetch next from c1 into @intid, @dob > > while (@@fetch_status=0) > > begin > > update donor > > set dob = convert (datetime, right(@dob,2)+left(@dob,4),112) > > where intId= @intId > > fetch next from c1 into @intid, @dob > > end > >close c1 > >end > > Hi Alex, > > You don't need a cursor at all. The following worked fine for me: > > set nocount on > create table donor(intId int not null primary key, > dob varchar(50) not null) > go > insert donor (intId, dob) > values (1, '101564') > insert donor (intId, dob) > values (2, '10151964') > insert donor (intId, dob) > values (3, '053104') > select * from donor > update donor > set dob = convert (datetime, right(dob,2)+left(dob,4),112) > where len(dob)=6 > select * from donor > go > drop table donor > > (output) > > intId dob > ----------- -------------------------------------------------- > 1 101564 > 2 10151964 > 3 053104 > > intId dob > ----------- -------------------------------------------------- > 1 Oct 15 1964 12:00AM > 2 10151964 > 3 May 31 2004 12:00AM > > > I also tried your cursor-based version; this worked fine as well. > > To find out why this didn't work for you, we'll need more information. > Please send DDL for the table and sample data (as INSERT statements); > check if the DDL + inserts + running your script do indeed reproduce the > problem you experience when run against an empty database. > > Once you have a repro script, post it here. We can then run it on our > machines to see if we can find the problem. > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address)
I had nothing to do with the design. I'm trying to help them move on...
We have decided that the data that won't meet this criteria, will be set to null. Thanks for any further assistance. [quoted text, click to view] "Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:O9bFuw4REHA.1392@TK2MSFTNGP09.phx.gbl... > > It seems that this data is REALLY bad. Unfortunately, some of the 6-len > > characters are strings. Can I bypass this? Only search and update 6-len > > int's? > > Ugh. Then what are you going to do with all the rows that do NOT represent > valid dates? > > Hopefully you have learned something that will help you the next time you > design... > >
[quoted text, click to view]
On Mon, 31 May 2004 07:17:44 -0700, Alex Ivascu wrote: >Aaron or David; > >I must be missing something, since these records aren't getting updated. Do >I have to assign the convert function first? > >declare > c1 cursor local fast_forward for > select intId, dob > from donor > where len(dob)=6 >begin > declare @intId int, @dob nvarchar(50) > open c1 > fetch next from c1 into @intid, @dob > while (@@fetch_status=0) > begin > update donor > set dob = convert (datetime, right(@dob,2)+left(@dob,4),112) > where intId= @intId > fetch next from c1 into @intid, @dob > end >close c1 >end Hi Alex, You don't need a cursor at all. The following worked fine for me: set nocount on create table donor(intId int not null primary key, dob varchar(50) not null) go insert donor (intId, dob) values (1, '101564') insert donor (intId, dob) values (2, '10151964') insert donor (intId, dob) values (3, '053104') select * from donor update donor set dob = convert (datetime, right(dob,2)+left(dob,4),112) where len(dob)=6 select * from donor go drop table donor (output) intId dob ----------- -------------------------------------------------- 1 101564 2 10151964 3 053104 intId dob ----------- -------------------------------------------------- 1 Oct 15 1964 12:00AM 2 10151964 3 May 31 2004 12:00AM I also tried your cursor-based version; this worked fine as well. To find out why this didn't work for you, we'll need more information. Please send DDL for the table and sample data (as INSERT statements); check if the DDL + inserts + running your script do indeed reproduce the problem you experience when run against an empty database. Once you have a repro script, post it here. We can then run it on our machines to see if we can find the problem. Best, Hugo --
[quoted text, click to view]
> It seems that this data is REALLY bad. Unfortunately, some of the 6-len > characters are strings. Can I bypass this? Only search and update 6-len > int's? Ugh. Then what are you going to do with all the rows that do NOT represent valid dates? Hopefully you have learned something that will help you the next time you design...
[quoted text, click to view]
On Mon, 31 May 2004 20:27:59 -0700, Alex Ivascu wrote: (snip)>Thanks for trying to help! > >It seems that this data is REALLY bad. Unfortunately, some of the 6-len >characters are strings. Can I bypass this? Only search and update 6-len >int's? > >Server: Msg 8115, Level 16, State 2, Line 1 >Arithmetic overflow error converting expression to data type datetime. >The statement has been terminated. I suggest not updating the column with a new value (as I did in my previous suggestion, based on your cursor). This will still leave you with a varchar column that can't be easily used in date/time comparisons. Instead, add a new datetime column, convert dates that can be converted (and leave this column NULL for the remaining rows), then drop the old column. The following will try to convert to date only if the current value is exactly 6 numeric characters, the first two digits are in the range for month numbers (01 through 12) and the third and fourth in the range for possible day numbers (01 through 31); all other data is set to NULL. If this still fails there is data like 063104 or 022903. Pray that this is not the case.... set nocount on create table donor(intId int not null primary key, dob varchar(50) not null) go insert donor (intId, dob) values (1, '101564') insert donor (intId, dob) values (2, '10151964') insert donor (intId, dob) values (3, '053104') insert donor (intId, dob) values (4, '05A104') insert donor (intId, dob) values (5, '151064') select * from donor go alter table donor add dobNew datetime -- null if old date could not be converted go update donor set dobNew = convert (datetime, right(dob,2)+left(dob,4),112) where dob like '[0-1][0-9][0-3][0-9][0-9][0-9]' and left(dob,2) between '01' and '12' and substring(dob,3,2) between '01' and '31' go alter table donor drop column dob go alter table donor add dob datetime -- null if old date could not be converted go update donor set dob = dobNew go alter table donor drop column dobNew go select * from donor go drop table donor Best, Hugo --
Hugo... many thanks!
Alex Ivascu [quoted text, click to view] "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:uqdob09vb9m9g0655bc4c8sg0o3eb59joq@4ax.com... > On Mon, 31 May 2004 20:27:59 -0700, Alex Ivascu wrote: > > >Thanks for trying to help! > > > >It seems that this data is REALLY bad. Unfortunately, some of the 6-len > >characters are strings. Can I bypass this? Only search and update 6-len > >int's? > > > >Server: Msg 8115, Level 16, State 2, Line 1 > >Arithmetic overflow error converting expression to data type datetime. > >The statement has been terminated. > (snip) > > I suggest not updating the column with a new value (as I did in my > previous suggestion, based on your cursor). This will still leave you with > a varchar column that can't be easily used in date/time comparisons. > > Instead, add a new datetime column, convert dates that can be converted > (and leave this column NULL for the remaining rows), then drop the old > column. > > The following will try to convert to date only if the current value is > exactly 6 numeric characters, the first two digits are in the range for > month numbers (01 through 12) and the third and fourth in the range for > possible day numbers (01 through 31); all other data is set to NULL. If > this still fails there is data like 063104 or 022903. Pray that this is > not the case.... > > > set nocount on > create table donor(intId int not null primary key, > dob varchar(50) not null) > go > insert donor (intId, dob) > values (1, '101564') > insert donor (intId, dob) > values (2, '10151964') > insert donor (intId, dob) > values (3, '053104') > insert donor (intId, dob) > values (4, '05A104') > insert donor (intId, dob) > values (5, '151064') > select * from donor > go > alter table donor > add dobNew datetime -- null if old date could not be converted > go > update donor > set dobNew = convert (datetime, right(dob,2)+left(dob,4),112) > where dob like '[0-1][0-9][0-3][0-9][0-9][0-9]' > and left(dob,2) between '01' and '12' > and substring(dob,3,2) between '01' and '31' > go > alter table donor > drop column dob > go > alter table donor > add dob datetime -- null if old date could not be converted > go > update donor > set dob = dobNew > go > alter table donor > drop column dobNew > go > select * from donor > go > drop table donor > > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address)
I Have just ran into the same problem converting a nvarchar to
datetime.. The following may help.. -------------------------------- From: Joseph Dixon I don't know if this is still relevent to what you are working on, but I also recieved this message. This error message indicates that one of your date fields date is not consistant with date values, And the SQL server is having problems converting what should be in date format to a date. :::::::::PROBLEM::::::::: Example of incorrect information in your datebase: wrong: 2/200/4 wrong: 2/2/204 The SQL server will read these dates as Arithmatic operations, instead of dates, and will generate this error. Arithmetic overflow error converting expression to data type datetime. The statement has been terminated. ::::::::RESOLUTION::::::: If you understand this you know that you have some cleanup to do on your data before you convert to datetime. Look for fields that have incorrect formats. I exported to MS access and worked with the data from their thats how I discovered the problem. *** Sent via Devdex http://www.devdex.com ***
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |