numeric to datetime problem - 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

numeric to datetime problem

Author

Topic

iamtopgeezer
Starting Member

11 Posts

Posted - 2007-07-27 : 10:39:00

Hi,

I have a tsql problem where I have two tables both with a field called DateTime. In one table the DateTime is numeric(13) and in the other table the DateTime is varchar(13).

First table has:- (processjobs)
ProductName DateTime
EAC1ST19 200707170700
EAC1ST26 200507241035
MDM1ST17 200707170308

Second Table has:- (productionplanlist)
ProductName DateTime
NFY1ST20 200707191448
NHS1ST18 200707171053
NHS1ST25 200707241036
EAC1ST26 200607241035

I need to compare the two tables matching on ProductName and then if the date of the productionplanlist is more recent then it needs to replace the date with the date from the processjobs table. So the newer date is always in the processjobs table.

I have tried the following but i think i am getting the cast into a bit of a twist somewhere.

UPDATE processjobs
SET proc.datetime = prod.datetime
WHERE (cast(prod.datetime as datetime) >= cast(proc.datetime as datetime) and (proc.productname = prod.productname)
FROM processjobs proc, productionplanlist prod

Any help would be greatly appreciated.

Thanks

iamtopgeezer

Ash

nr
SQLTeam MVY

12543 Posts

Posted - 2007-07-27 : 11:08:24

You need to change
200707170700
to
20070717 07:00

have a look at the stuff operator

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

iamtopgeezer
Starting Member

11 Posts

Posted - 2007-07-27 : 11:26:10

Thanks.

But can stuff and cast be used in the same line of code together, as I am changing one datetime field from numeric to datetime and another from varchar to datetime?

Ash

nr
SQLTeam MVY

12543 Posts

Posted - 2007-07-27 : 11:42:08

yep

select convert(datetime,stuff(stuff(convert(varchar(12),@d),11,0,':'),9,0,' '))

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

iamtopgeezer
Starting Member

11 Posts

Posted - 2007-07-27 : 11:56:42

Thanks again.

I think I am nearly there now but get another error in a more simpler area but cannot just see it for some reason. Here is my code

UPDATE processjobs
SET proc.datetime = prod.datetime
FROM processjobs proc, productionplanlist prod

WHERE cast (stuff(STUFF(prod.datetime,9, 0, ' ' ),12,0,':') as datetime) >=
castcast (stuff(STUFF(proc.datetime, 9, 0, ' ' ),12,0,':') as datetime)

and (proc.productname = prod.productname)

----

But the error I get is 'Incorrect syntax near the keyword proc?

Any thoughts?

Ash

nr
SQLTeam MVY

12543 Posts

Posted - 2007-07-27 : 11:58:27

UPDATE processjobs
SET datetime = prod.datetime
FROM processjobs proc, productionplanlist prod

WHERE cast (stuff(STUFF(prod.datetime,9, 0, ' ' ),12,0,':') as datetime) >=
castcast (stuff(STUFF(proc.datetime, 9, 0, ' ' ),12,0,':') as datetime)

and (proc.productname = prod.productname)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

iamtopgeezer
Starting Member

11 Posts

Posted - 2007-07-30 : 03:45:16

Thanks nr,

Its made the start to this week seem a lot better.

Ash

iamtopgeezer
Starting Member

11 Posts

Posted - 2007-07-30 : 03:52:53

Sorry nr, I think i spoke to soon. The error 'Incorrect syntax near the keyword 'proc'.. is still there.

I can't see why it would query it.

Ash

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-07-30 : 04:01:05

UPDATE proc
SET datetime = prod.datetime
FROM processjobs proc, productionplanlist prod
WHERE cast (stuff(STUFF(prod.datetime,9, 0, ' ' ),12,0,':') as datetime) >=
castcast (stuff(STUFF(proc.datetime, 9, 0, ' ' ),12,0,':') as datetime)
and (proc.productname = prod.productname)

iamtopgeezer
Starting Member

11 Posts

Posted - 2007-07-30 : 04:10:50

Thanks, but I'm afraid that didn't work either. It is still querying the 'proc' alias being used for some reason. But as far as checking the syntax goes, I am sure the syntax is ok. But obviously not.

Ash

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 04:23:30

You don't have to STUFF and CAST to DATETIME to do this.

UPDATE		proc
SET		proc.datetime = prod.datetime
FROM		processjobs as proc
INNER JOIN	productionplanlist as prod on prod.productname = proc.productname
WHERE		cast(prod.datetime as char(13)) >= cast(proc.datetime as char(13))

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-07-30 : 04:25:27

Might be because "datetime" is a reserved word?

SET [datetime] = prod.[datetime]

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 04:31:55

Here it is spelled out

-- Prepare sample data
DECLARE	@ProcessJobs TABLE (ProductName SYSNAME, DateTime DECIMAL(13, 0))

INSERT	@ProcessJobs
SELECT	'EAC1ST19', 200707170700 UNION ALL
SELECT	'EAC1ST26', 200507241035 UNION ALL
SELECT	'MDM1ST17', 200707170308

DECLARE	@ProductionPlanList TABLE (ProductName SYSNAME, DateTime VARCHAR(13))

INSERT	@ProductionPlanList
SELECT	'NFY1ST20', '200707191448' UNION ALL
SELECT	'NHS1ST18', '200707171053' UNION ALL
SELECT	'NHS1ST25', '200707241036' UNION ALL
SELECT	'EAC1ST26', '200607241035'

-- Before
SELECT	*
FROM	@ProcessJobs

-- Do the update
UPDATE		[proc]
SET		[proc].datetime = prod.datetime
FROM		@processjobs as [proc]
INNER JOIN	@productionplanlist as prod on prod.productname = [proc].productname
WHERE		prod.datetime >= [proc].datetime

-- After
SELECT	*
FROM	@ProcessJobs

E 12°55'05.25"
N 56°04'39.16"

iamtopgeezer
Starting Member

11 Posts

Posted - 2007-07-30 : 04:47:15

Peso/Kristen, thanks to both of you but but while Kristen your solution worked, I need to cast to datetime because the fields are named 'DateTime' but are not datetime field, one table is numeric and the other is varchar hence the need for a conversion to datetime in order to check one date is more recent to the other.

I have changed proc alias to proct incase proc is a reserved word to have the following:-

UPDATE processjobs
SET proct.[Datetime] = prod.[Datetime]

FROM processjobs proc, productionplanlist prod

WHERE cast (stuff(STUFF(prod.[datetime],9, 0, ' ' ),12,0,':') as datetime) >=
cast (stuff(STUFF(proct.[datetime], 9, 0, ' ' ),12,0,':') as datetime)

and (proct.ProductName = prod.ProductName)

But now I get error:-

Cannot use the column prefix 'proct'. This must match the object in the UPDATE clause 'processjobs'.

Help

Ash

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 04:51:49

OMG...

Did you even look at the post made to you at 07/30/2007 : 04:31:55 ???

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 04:53:11

And here it is again, just in case your browser is neglicting you to scroll up.

UPDATE		[proc]
SET		[proc].datetime = prod.datetime
FROM		processjobs as [proc]
INNER JOIN	productionplanlist as prod on prod.productname = [proc].productname
WHERE		prod.datetime >= [proc].datetime

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 04:53:54

SQL Server take care of the conversions for you...
And I bump my post count.

E 12°55'05.25"
N 56°04'39.16"

iamtopgeezer
Starting Member

11 Posts

Posted - 2007-07-30 : 04:54:51

Peso, sorry must have been typing at the same time. Will take a deep look now, before bothering anyone again. cheers.

Ash

Kristen
Test

22859 Posts

Posted - 2007-07-30 : 04:58:37

"Cannot use the column prefix 'proct'. This must match the object in the UPDATE clause 'processjobs'."

That's because you have a typo:


UPDATE processjobs 
SET proct.[Datetime] = prod.[Datetime]
FROM processjobs proc, productionplanlist prod 
...

Kristen

Kristen
Test

22859 Posts

Posted - 2007-07-30 : 04:59:43

"SQL Server take care of the conversions for you..."

I thought that too. The implicit conversion here would be fine - provided the data in the VARCHAR column is in fixed length format (no values with different numbers to leading spaces).

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-30 : 05:07:46

If that is the case, STUFF will fail too, right?

E 12°55'05.25"
N 56°04'39.16"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources