Date frustration

I screwed up when building my application, now I need to try to fix it...
Initially I setup my dates in a varchar field, now I need them in a datetime
field...
I have the following query that is acting funny,
UPDATE tblEmpPersonalInfo SET
SepDate = EmpSWVTCSepDate
This query will not successfully run, giving me this error,
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.
But, if I edit the query like this,
UPDATE tblEmpPersonalInfo SET
SepDate = EmpSWVTCSepDate
WHERE EmpID = 'aa381234'
then the query will work fine... I don't want to have to run the query for
every employee in the database, so how can I do this?
Thanks,
DrewUse ISDATE() function to identify if the data in the column are truly date
values. Check out SQL Server Books Online for more details on this function.
Anith|||Hi
Check that all the values in the EmpSWVTCSepDate column are actually valid
dates.
SELECT * FROM tblEmpPersonalInfo
WHERE ISDATE(EmpSWVTCSepDate) = 0
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:e6aUZElAFHA.2792@.TK2MSFTNGP15.phx.gbl...
> I screwed up when building my application, now I need to try to fix it...
> Initially I setup my dates in a varchar field, now I need them in a
datetime
> field...
> I have the following query that is acting funny,
> UPDATE tblEmpPersonalInfo SET
> SepDate = EmpSWVTCSepDate
> This query will not successfully run, giving me this error,
> 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.
> But, if I edit the query like this,
> UPDATE tblEmpPersonalInfo SET
> SepDate = EmpSWVTCSepDate
> WHERE EmpID = 'aa381234'
> then the query will work fine... I don't want to have to run the query for
> every employee in the database, so how can I do this?
> Thanks,
> Drew
>|||Thanks... There was 1 invalid date in there, 10-10--2003
Drew
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:u4k2MPlAFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Hi
> Check that all the values in the EmpSWVTCSepDate column are actually valid
> dates.
> SELECT * FROM tblEmpPersonalInfo
> WHERE ISDATE(EmpSWVTCSepDate) = 0
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:e6aUZElAFHA.2792@.TK2MSFTNGP15.phx.gbl...
> datetime
>|||Drew,
1 - Update those without problem
UPDATE tblEmpPersonalInfo SET
SepDate = EmpSWVTCSepDate
where isdate(EmpSWVTCSepDate) = 1
2 - Identify those with problem, correct the problem and update them again.
select * from tblEmpPersonalInfo
where isdate(EmpSWVTCSepDate) = 0
-- fix the problem
...
UPDATE tblEmpPersonalInfo SET
SepDate = EmpSWVTCSepDate
where isdate(EmpSWVTCSepDate) = 1 and SepDate is null
AMB
"Drew" wrote:

> I screwed up when building my application, now I need to try to fix it...
> Initially I setup my dates in a varchar field, now I need them in a dateti
me
> field...
> I have the following query that is acting funny,
> UPDATE tblEmpPersonalInfo SET
> SepDate = EmpSWVTCSepDate
> This query will not successfully run, giving me this error,
> 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.
> But, if I edit the query like this,
> UPDATE tblEmpPersonalInfo SET
> SepDate = EmpSWVTCSepDate
> WHERE EmpID = 'aa381234'
> then the query will work fine... I don't want to have to run the query for
> every employee in the database, so how can I do this?
> Thanks,
> Drew
>
>|||Too bad we cannot set some sort of flashing attribute on this message :)
Glad you found your problem, and even more important, glad you are cutting
future problems off at the knee caps. The way a value displays can be dealt
with at runtime, the most important thing is that the value is true!
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:ewv0xWlAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Thanks... There was 1 invalid date in there, 10-10--2003
> Drew
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:u4k2MPlAFHA.2604@.TK2MSFTNGP10.phx.gbl...
>