Convert a varchar to a datetime field
Hello Experts!
I have a table that contains a colum with dates. This column also contains some random data that I have to remove. Once I've done this I need to insert this column into a table where the column is 'datetime'. I can't insert/update this column into the table where it is a datetime because the original column is a varchar.
Here is my insert statement:
Insert Into Partsonhand (partnum, [desc], uqoh, pack, list, ucost, [ext-val], o, qoh, qori, qavl, markup, entry, udate , lsdate, lrcvdte, iodate, whs, ytd, pyr, ytdls, f, partnum2)
select partsonhandtemp.partnum, partsonhandtemp.[desc], partsonhandtemp.uqoh, partsonhandtemp.pack, partsonhandtemp.list, partsonhandtemp.ucost, partsonhandtemp.[ext-val],
from partsonhandtemp
left outer join partsonhand on partsonhandtemp.partnum = partsonhand.partnum
where partsonhand.partnum is null
So the column is "udate". How do I use the convert function to fix this while inserting?
I've tried convert(datetime, udate) but it gives me an error.
Help!
I have a table that contains a colum with dates. This column also contains some random data that I have to remove. Once I've done this I need to insert this column into a table where the column is 'datetime'. I can't insert/update this column into the table where it is a datetime because the original column is a varchar.
Here is my insert statement:
Insert Into Partsonhand (partnum, [desc], uqoh, pack, list, ucost, [ext-val], o, qoh, qori, qavl, markup, entry, udate , lsdate, lrcvdte, iodate, whs, ytd, pyr, ytdls, f, partnum2)
select partsonhandtemp.partnum, partsonhandtemp.[desc], partsonhandtemp.uqoh, partsonhandtemp.pack, partsonhandtemp.list, partsonhandtemp.ucost, partsonhandtemp.[ext-val],
from partsonhandtemp
left outer join partsonhand on partsonhandtemp.partnum = partsonhand.partnum
where partsonhand.partnum is null
So the column is "udate". How do I use the convert function to fix this while inserting?
I've tried convert(datetime, udate) but it gives me an error.
Help!
ASKER
Works perfectly for the insert
How about the update?
update partsonhand
set partnum = partsonhandtemp.partnum, [desc] = partsonhandtemp.[desc],
uqoh = partsonhandtemp.uqoh, pack = partsonhandtemp.pack,
list = partsonhandtemp.list, ucost = partsonhandtemp.ucost,
[ext-val] = partsonhandtemp.[ext-val],
qoh = partsonhandtemp.qoh, qori = partsonhandtemp.qori,
qavl = partsonhandtemp.qavl, markup = partsonhandtemp.markup,
entry = partsonhandtemp.entry, udate = convert(datetime,partsonha
lsdate = partsonhandtemp.lsdate, lrcvdte = partsonhandtemp.lrcvdte,
iodate = partsonhandtemp.iodate, whs = partsonhandtemp.whs,
ytd = partsonhandtemp.ytd, pyr = partsonhandtemp.pyr,
ytdls = partsonhandtemp.ytdls, f = partsonhandtemp.f,
partnum2 = partsonhandtemp.partnum2
from partsonhand inner join partsonhandtemp on partsonhand.partnum = partsonhandtemp.partnum
where partsonhandtemp.partnum = partsonhand.partnum
How about the update?
update partsonhand
set partnum = partsonhandtemp.partnum, [desc] = partsonhandtemp.[desc],
uqoh = partsonhandtemp.uqoh, pack = partsonhandtemp.pack,
list = partsonhandtemp.list, ucost = partsonhandtemp.ucost,
[ext-val] = partsonhandtemp.[ext-val],
qoh = partsonhandtemp.qoh, qori = partsonhandtemp.qori,
qavl = partsonhandtemp.qavl, markup = partsonhandtemp.markup,
entry = partsonhandtemp.entry, udate = convert(datetime,partsonha
lsdate = partsonhandtemp.lsdate, lrcvdte = partsonhandtemp.lrcvdte,
iodate = partsonhandtemp.iodate, whs = partsonhandtemp.whs,
ytd = partsonhandtemp.ytd, pyr = partsonhandtemp.pyr,
ytdls = partsonhandtemp.ytdls, f = partsonhandtemp.f,
partnum2 = partsonhandtemp.partnum2
from partsonhand inner join partsonhandtemp on partsonhand.partnum = partsonhandtemp.partnum
where partsonhandtemp.partnum = partsonhand.partnum
ASKER
udate = convert(datetime,partsonha
That's what I've tried...doesn't work :(
That's what I've tried...doesn't work :(
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
udate = CAST(CASE WHEN (partsonhandtemp.udate) = 1 THEN partsonhandtemp.udate ELSE NULL END AS DATETIME),
ASKER
Yep, worked just fine. Guess I need to read up on "case"!
Thanks for your help.
Thanks for your help.
Insert Into Partsonhand (partnum, [desc], uqoh, pack, list, ucost, [ext-val], o, qoh, qori, qavl, markup, entry, udate , lsdate, lrcvdte, iodate, whs, ytd, pyr, ytdls, f, partnum2)
select partsonhandtemp.partnum, partsonhandtemp.[desc], partsonhandtemp.uqoh, partsonhandtemp.pack, partsonhandtemp.list, partsonhandtemp.ucost, partsonhandtemp.[ext-val],
case when isdate(partsonhandtemp.uda
partsonhandtemp.lsdate, partsonhandtemp.lrcvdte, partsonhandtemp.iodate, partsonhandtemp.whs, partsonhandtemp.ytd, partsonhandtemp.pyr, partsonhandtemp.ytdls, partsonhandtemp.f, partsonhandtemp.partnum2
from partsonhandtemp
left outer join partsonhand on partsonhandtemp.partnum = partsonhand.partnum
where partsonhand.partnum is null