Inner Join / Update / Stuck - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Inner Join / Update / Stuck

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-04-24 : 13:46:11

First off, i've read the available documentation on cast and convert and am not making headway on the following.

I'm using this code to bring in a "time" value from one table to another based on a common value in ReceiptNumber between the two tables. The source data for GCSORECEIVETIME is a varchar(20) and the column it needs to get in to is a datetime field.



UPDATE a
SET a.RECEIVETIME = cast(GCSORECEIVETIME as datetime)
FROM GadsdenCivil.dbo.GCSORECEIPTS m
INNER JOIN GCSOCIVILCONVERSION.dbo.GCSORECEIPTS a
ON a.RECEIVETIME = GCSORECEIVETIME
WHERE a.RECEIPTNUMBER = m.RECEIPTNUMBER

I'm getting the following error:

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

The formats in the source data look like:
0827
0830
0836
0933
etc.

thanks for any help.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-04-24 : 17:07:35

Ok, the reason you haven't gotten any answers yet is that your post is way to cluttered. Next time remove all the stuff that's not relevant to the question at hand..we really don't need to see all the extended properties of all the columns in your tables.

You are basically trying to convert the varchar value '0827' to a datatype that has a very strict format; 'yyyy-mm-dd hh:mm:ss'. Obviously they are not even remotely compatible. Are you on SQL Server 2008? In that case you can use the "date" datatype...if not you're stuck with datetime. Your join is also messed up...you are trying to join on the columns you are trying to update and that would not work.

This should get you closer I think:

UPDATE a
SET a.RECEIVETIME = CAST('1900-01-01 ' + STUFF(GCSORECEIVETIME, 3, 0, ':') AS datetime)
FROM GadsdenCivil.dbo.GCSORECEIPTS m
INNER JOIN GCSOCIVILCONVERSION.dbo.GCSORECEIPTS a
ON a.RECEIPTNUMBER = m.RECEIPTNUMBER

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-04-24 : 17:16:08

Thanks. Will do next time. in the past i've been scolded for NOT posting the full DDL. At any rate, the following is what actually got me there. Figured it out just after your solution was posted:


UPDATE a
SET a.RECEIVETIME = convert(varchar(12), cast(SUBSTRING(GCSORECEIVETIME, 1, 2)+':'+SUBSTRING(GCSORECEIVETIME, 3, 2)+':00' as datetime) , 108)
FROM GadsdenCivil.dbo.GCSORECEIPTS m
INNER JOIN GCSOCIVILCONVERSION.dbo.GCSORECEIPTS a
ON a.RECEIVETIME = convert(varchar(12), cast(SUBSTRING(GCSORECEIVETIME, 1, 2)+':'+SUBSTRING(GCSORECEIVETIME, 3, 2)+':00' as datetime) , 108)
WHERE a.RECEIPTNUMBER = m.RECEIPTNUMBER

thanks again

Xiez
Starting Member

13 Posts

Posted - 2012-04-24 : 17:32:08

Ehhh... You might be in a pickle...

a 4 digit number is not a valid value for a DateTime field. A DateTime must contain both a date and a time. You can try concatenating a string to the front of your time which just contains the default date (1/1/1900) and just parse that date out when displaying the data. Even this won't be possible, because '1/1/1900 0630' is still invalid. You must have the colon in, like '1/1/1900 06:30'

One solution I can think of now is write a simple application that will modify the string to a datetime format and do the updates, but depending on the amount of data, this might take a long time.

A better solution is if you can find some string manipulation sql commands, but a couple quick searches didn't get me anything usable.

Xiez
Starting Member

13 Posts

Posted - 2012-04-24 : 17:33:57

Ah ha! I was too late, but you found SUBSTRING. Good job! :D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources