Avatar of hyphenpipe
hyphenpipeFlag for United States of America

asked on 

How can I convert and update an [int] field to [datetime]?

I have a table in my database with multiple fields.  Two of those fields are alt_id set to int and birthdateset to datetime.  Right now all of the data for the birthday field is null.   The data in the alt_id field is listed like 09191982.

Obviously the alt_id fields are all birthdates.  I need to get this data in the birthdate field as well.

I am not sure as to how to proceed.

I tried something like:

update clients set birthdate = substring(alt_id, 5, 4) + '-' + substring(alt_id, 1, 2) + '-' + substring(alt_id, 3, 2)

But get an error:

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

I am assuming one of the dates being updated doesn't exist but there are 845 records and I am unsure as to how to check and see which one it is.  

I did do a couple of simple things, checking for 8 characters, checking to make sure the all comformed to '____19__'.  I am assuming it may be something like a date '1988-06-31' which doesn't exist, just need some help to rule that out.

Unless I am doing this wrong and there is an easier way to do it, I'm all ears.

Thanks!
Microsoft SQL Server 2005

Avatar of undefined
Last Comment
Anthony Perkins
Avatar of 60MXG
60MXG

how about
update clients set birthdate = substring(alt_id, 5, 4) + '/' + substring(alt_id, 1, 2) + '/' + substring(alt_id, 3, 2)
Avatar of hyphenpipe
hyphenpipe
Flag of United States of America image

ASKER

Yeah, I get the same error.
Avatar of hyphenpipe
hyphenpipe
Flag of United States of America image

ASKER

It may be I need to add single quotes around it but I don't know how to do that.

Avatar of 60MXG
60MXG

update clients set birthdate = (substring(alt_id, 5, 4) + '/' + substring(alt_id, 1, 2) + '/' + substring(alt_id, 3, 2))
how about that???/
Avatar of hyphenpipe
hyphenpipe
Flag of United States of America image

ASKER

Same error.
Avatar of 60MXG
60MXG

update clients set birthdate = (substring(alt_id, 5, 4) + '/' + substring(alt_id, 1, 2) + '/' + substring(alt_id, 3, 2))
where alt_id = 'something' ???

Did you put in where clause? or It doesn't mater?
Avatar of 60MXG
60MXG

update clients
         set birthdate = cast(substring(alt_id, 5, 4) + '/' + substring(alt_id, 1, 2) + '/' + substring(alt_id, 3, 2)) as datetime
???
Avatar of hyphenpipe
hyphenpipe
Flag of United States of America image

ASKER

I have confirmed that it is and invalid date because this works fine:

set rowcount 1
select cast(substring(alt_id, 5, 4) + '/' + substring(alt_id, 1, 2) + '/' + substring(alt_id, 3, 2) as datetime) from clients

But if I set rowcount to 0, it fails with the same error.

I just need a foolproof way to see which record out of 845 is out fo range.
Avatar of hyphenpipe
hyphenpipe
Flag of United States of America image

ASKER

I got it.  I just outputted my query along with the id field to text instead of a grid and it runs until it fails.  I was able to locate the id of the incorrect one and, remove it from my update statement and it worked fine.

Avatar of hyphenpipe
hyphenpipe
Flag of United States of America image

ASKER

Here were the culprits:

1982/14/05
 and
1966/01/44
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of hyphenpipe
hyphenpipe
Flag of United States of America image

ASKER

Thanks for the help.  But why 4 characters to the left, and four to the right?
>>But why 4 characters to the left, and four to the right?<<
That produces the ISO (CONVERT style = 112) date time format of YYYYMMDD
Microsoft SQL Server 2005
Microsoft SQL Server 2005

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo