problem with date / time - 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

SQL Server 2008 Forums

Transact-SQL (2008)

problem with date / time

Author

Topic

fsunolefan
Starting Member

10 Posts

Posted - 2010-07-09 : 11:34:34

Any help would be appreciated...

I'm getting a 'Conversion failed when converting date and/or time from character string.' message on the following piece of script:

UPDATE WH_StudentActivity
SET AppliedForOCIJobs =
(SELECT COUNT(distinct Banner.col001)
FROM (([Dev_VT_CareerNet].[dbo].[Adm_BannerExtract] as Banner
JOIN [All Student Data for SQL Server] as Students ON Students.[Student User Name] = Banner.col041)
JOIN csoPreselect on csoPreselect.Student_ID = Students.[Student Record ID])
JOIN csoSchedule on csoSchedule.Schedule_ID = csoPreselect.Schedule_ID
WHERE (ltrim(rtrim(col076)) > '0.00' OR Banner.col057 = 'C')
-- WHERE (ltrim(rtrim(col076)) > '0.00' OR Banner.col057 = 'C' OR Banner.col019 IN ('200609','200701'))
AND Banner.ReportMajor = WH_StudentActivity.Major
AND WH_StudentActivity.AcademicLevel = Banner.AcademicLevel
AND cast(csoSchedule.FreezeDateTime as Datetime ) > '08/01/2009')

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-09 : 11:46:01

instead of '08/01/2009' use 'YYYYMMDD' so it is '20090801' or '20090108' I don't know...

No, you're never too old to Yak'n'Roll if you're too young to die.

fsunolefan
Starting Member

10 Posts

Posted - 2010-07-09 : 11:58:38

I forgot to add... if i comment out the very last line and close the parenthesis in the line above, it works. Any idea why the last line is giving an error though? TIA

KrafDinner
Starting Member

34 Posts

Posted - 2010-07-09 : 13:03:46

I don't know if it would help or not, but have you tried converting/casting that date time ?

AND cast(csoSchedule.FreezeDateTime as Datetime) > cast('08/01/2009' as Datetime)

just a thought...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-09 : 13:08:31

quote:
Originally posted by fsunolefan

I forgot to add... if i comment out the very last line and close the parenthesis in the line above, it works. Any idea why the last line is giving an error though? TIA

Have you seen my last post?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-09 : 13:10:01

quote:
Originally posted by KrafDinner

I don't know if it would help or not, but have you tried converting/casting that date time ?

AND cast(csoSchedule.FreezeDateTime as Datetime) > cast('08/01/2009' as Datetime)

just a thought...

That would give the same error meassage because sql server is already trying to do this kind of convert implicit.

No, you're never too old to Yak'n'Roll if you're too young to die.

fsunolefan
Starting Member

10 Posts

Posted - 2010-07-09 : 13:36:04

Hey webfred, I just tried your suggestion and it had no effect. I also tried Krafdinner's and it had no effect either. Also, if i change it to
AND cast(csoSchedule.FreezeDateTime as Datetime ) > '')

it still fails

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-09 : 13:39:28

Then the convert on the FreezeDateTime column will be the problem.
Can you please show an example of the values in that column?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-09 : 13:44:31

AND convert(datetime,csoSchedule.FreezeDateTime,101) > '20090801'

should work if we actually talking about '08/01/2009' means MM/DD/YYY

No, you're never too old to Yak'n'Roll if you're too young to die.

fsunolefan
Starting Member

10 Posts

Posted - 2010-07-09 : 13:44:45

It appears to be either a 'yes' or 'no' answer all the way through that column

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-09 : 13:46:10

What????
You can't compare 'yes' or 'no' with a date...

No, you're never too old to Yak'n'Roll if you're too young to die.

KrafDinner
Starting Member

34 Posts

Posted - 2010-07-09 : 13:46:12

I figured it would be trying that conversion implicitly, but just to be sure it worked, I tried it. The conversion of '08/09/2009' to datetime works fine (or any other date in mm/dd/yyyy format)

It certainly seems (as webfred suggested) that the FreezeDateTime is the problem.

fsunolefan
Starting Member

10 Posts

Posted - 2010-07-09 : 13:59:47

About three days ago, i changed all instances of '08/01/2009' to '08/01/2010' in that script. For almost 2 years previous to that, the script ran without error. I changed every instance of the 2010 back to 2009 and now am getting this error. The FreezeDateTime column has been the same throughout.

thanks again

fsunolefan
Starting Member

10 Posts

Posted - 2010-07-09 : 14:31:56

Here is the entire section in question:

UPDATE WH_StudentActivity
SET AppliedForOCIJobs =
(SELECT COUNT(distinct Banner.col001)
FROM (([Dev_VT_CareerNet].[dbo].[Adm_BannerExtract] as Banner
JOIN [All Student Data for SQL Server] as Students ON Students.[Student User Name] = Banner.col041)
JOIN csoPreselect on csoPreselect.Student_ID = Students.[Student Record ID])
JOIN csoSchedule on csoSchedule.Schedule_ID = csoPreselect.Schedule_ID
WHERE (ltrim(rtrim(col076)) > '0.00' OR Banner.col057 = 'C')
-- WHERE (ltrim(rtrim(col076)) > '0.00' OR Banner.col057 = 'C' OR Banner.col019 IN ('200609','200701'))
AND Banner.ReportMajor = WH_StudentActivity.Major
AND WH_StudentActivity.AcademicLevel = Banner.AcademicLevel
AND cast(csoSchedule.FreezeDateTime as Datetime) > '08/01/2009')

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-07-09 : 14:35:00

Run this to see if it finds any values that do not convert to datetime.

select
	csoSchedule.FreezeDateTime,
from
	csoSchedule
where
	isdate(csoSchedule.FreezeDateTime) <> 1

CODO ERGO SUM

fsunolefan
Starting Member

10 Posts

Posted - 2010-07-09 : 14:42:58

hey Michael thanks for chiming in...

It returned every single row...

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-09 : 14:56:47

can show us datatype of FreezeDateTime and also do select distinct FreezeDateTime from csoSchedule. what do you see? can you post that please. it seems like FreezeDateTime is not datetime, somehow it got updated with wrong data by someone some process

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

fsunolefan
Starting Member

10 Posts

Posted - 2010-07-09 : 15:17:41

Hi yosiasz, it is VARCHAR(255) and every entry in that column is either 'yes' or 'no'

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-09 : 15:25:10

I always have a prolem with dates and times...I'm never where I should be at the right time...or Date...

'cept course if it's music....

When is Damian going to play in the States?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-09 : 15:41:10

uh?!?!? Yes or No? should it not be dates? like someone said earlier.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-07-09 : 16:09:53

quote:
Originally posted by fsunolefan

Hi yosiasz, it is VARCHAR(255) and every entry in that column is either 'yes' or 'no'

Those cannot convert to a datetime, so that looks like your problem.

CODO ERGO SUM

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources