SQL 2000 date problem in view

I have this view:
SELECT     *
FROM         dbo.v_Calendar
WHERE     (TrnDate = DATEADD([DAY], 0, DATEDIFF([DAY], 0, CURRENT_TIMESTAMP)))

And it returns the attached error, it links to our calendar database that I have filled in for this month to project sales figures on our intranet.
It was working all last year and now is not, does anyone know why?
SQL-2000-Error.bmp
HKFueyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Cenjoy100Commented:
SELECT     *
FROM         dbo.v_Calendar
WHERE     (cast ( TrnDate as datetime) = cast (DATEADD([DAY], 0, DATEDIFF([DAY], 0, CURRENT_TIMESTAMP)) as datetime))
0
Cenjoy100Commented:
Or try this

SELECT     *
FROM         dbo.v_Calendar
WHERE     (cast ( TrnDate as varchar(800)) = cast (DATEADD([DAY], 0, DATEDIFF([DAY], 0, CURRENT_TIMESTAMP)) as varchar(800)))
0
HKFueyAuthor Commented:
OK...
First one gives same error, second no records (there is a 5/1/2012 in the table)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

aflockhartCommented:
Looks from your code as if [TrnDate] is a CHAR or VARCHAR column which contains items that should be dates, but there is something in the column which can't be converted to a valid date. I would check the data in that column carefully for invalid dates (which may occur on any row,  not just the rows being found by the WHERE condition, but probably on something you have updated recently).

Forcing both sides of the conversion to be treated as varchar in the previous suggestion is preventing the error bacause it doesn't convert the char to a date, but the resulting text strings probably don't match exactly, so you don't get any data.  So another approach could be to look at the data ( without a WHERE clause) to see what format is being returned, then try to work out a suitable comparison that matches this.  I think when you cast the current_timestamp expression as a varchar, it will probably include the time portion.

The following will give you a sample of the date formats being returned, to help to see what is going on:
 

SELECT   TOP 10  
   cast ( TrnDate as varchar(800))  as DatabaseField,
   cast (DATEADD([DAY], 0, DATEDIFF([DAY], 0, CURRENT_TIMESTAMP)) as varchar(800)) as ComparisonValue
FROM   dbo.v_Calendar

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HKFueyAuthor Commented:
Thanks aflockhart this is the result: -

DatabaseField      ComparisonValue
4      Jan  5 2012 12:00AM
3      Jan  5 2012 12:00AM
2      Jan  5 2012 12:00AM
1      Jan  5 2012 12:00AM
7      Jan  5 2012 12:00AM
6      Jan  5 2012 12:00AM
5      Jan  5 2012 12:00AM
4      Jan  5 2012 12:00AM
3      Jan  5 2012 12:00AM
2      Jan  5 2012 12:00AM
0
Cenjoy100Commented:
Hi HKFuey,

Can you please let me know the actual data type of TrnDate column.In table.

 dbo.v_Calendar Looks like name of view..or is it a table?


Thanks,


0
Cenjoy100Commented:
From  result mentioned above it seems TrnDate  contains only date ..
i.e If date is 12/31/2011 then your TrnDate  column contains only 31.


Try this, this will work...Didn't what kind of logic you are trying to implement....

SELECT     *
FROM         dbo.v_Calendar
WHERE    TrnDate  =  DATEPART(d, cast(DATEADD([DAY],0,DATEDIFF([DAY], 0, CURRENT_TIMESTAMP))AS DATETIME))
0
HKFueyAuthor Commented:
Thanks very much!
My Access append data datetime field format was DD/MM/YYYY

Changed format to 'Short Date' and it works fine.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.