Date Query - 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 2000 Forums

SQL Server Development (2000)

Date Query

Author

Topic

Dilly
Starting Member

8 Posts

Posted - 2005-06-17 : 23:45:30

I have this query which selects all entries in the database in which the start date is before today's date, and the end date is after today's date. That is that today's date is between the two dates in the database.

The fields in the database are of type datetime.

This query works when I execute it in Enterprise Manager, but the exact same query returns this:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. when executed from an asp file like so:

sqlround = "SELECT top 1 * FROM main_round WHERE (normal = '1') AND (enddate > GETDATE()) AND (startdate < GETDATE()) ORDER BY normal DESC"
response.write sqlround
set rsround = conn.execute(sqlround)

The query i use in enterprise manager (that works) is like this:

SELECT     TOP 1 *
FROM         main_round
WHERE     (normal = '1') AND (enddate > GETDATE()) AND (startdate < GETDATE())
ORDER BY normal DESC

Can anyone tell me why this happens, and how to fix it?

Dilly
Starting Member

8 Posts

Posted - 2005-06-18 : 00:31:34

Something even weirder - sometimes the query does work on the asp page, but then I come back a bit later and refresh the page and get the error message again... :S

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-18 : 00:59:52

What is the result of response.write sqlround?

Madhivanan

Failing to plan is Planning to fail

Dilly
Starting Member

8 Posts

Posted - 2005-06-18 : 01:06:18

SELECT TOP 1 * FROM main_round WHERE (normal = '1') AND (enddate > GETDATE()) AND (startdate < GETDATE()) ORDER BY normal DESC

Exactly the same as the query I enter into Enterprise Manager

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-18 : 01:15:32

Try this


SELECT     TOP 1 *
FROM         main_round
WHERE     (normal = '1') AND (datediff(d,GETDATE(),enddate )>0) AND 
(datediff(d,startdate,GETDATE())>0) ORDER BY normal DESC

Madhivanan

Failing to plan is Planning to fail

Dilly
Starting Member

8 Posts

Posted - 2005-06-19 : 03:03:01

That seems to have worked - but then again so did the other solutions I tried, temporarily.

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-19 : 08:20:29

I don't see any CHAR datatypes which might need to be converted to DATETIME.

Are the columns StartDate and Enddate typed DATETIME or VARCHAR in your table?

Dilly
Starting Member

8 Posts

Posted - 2005-06-19 : 09:08:38

DATETIME

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-19 : 09:38:26

Hmm... well the error speaks of converting CHAR to DATETIME.

I see you don't specify the table owner. Is there any chance that SQL is resolving your ASP query to an old table under a different username?

Anyway, you should try coding dbo.Mytable to avoid any ambiguity.

What are the other columns in the table ? Can you post the DML?

Dilly
Starting Member

8 Posts

Posted - 2005-06-19 : 15:29:17

That was another thing that confused me too. Where does it's CHAR come from.

It's definately using the correct table, and trying it with dbo.Mytable has the same results. That said it's hard to test because as I said it works intermittently - and this could just be one of the times it works...

round_id - int - 4
title - varchar - 50
normal - int - 4
startdate - datetime - 8
enddtae - datetime - 8
games - int - 4

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-20 : 14:15:48

Post the ASP code that does the call...

Dilly
Starting Member

8 Posts

Posted - 2005-06-20 : 19:11:23

sqltempround = "SELECT TOP 1 * FROM "& table_prefix &"round WHERE (normal = '1') AND (datediff(d,GETDATE(),enddate )>0) AND (datediff(d,startdate,GETDATE())>0)"

set rsround = conn.execute(sqltempround)

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-20 : 19:18:25

You might want to get rid of the asterisk ( top 1 * ) and try specifying the columns... when nothing makes sense, change something till it works.

select top 1 round_id, title, normal, startdate, enddate, games

Dilly
Starting Member

8 Posts

Posted - 2005-06-20 : 19:24:43

I've tried that too. I've tried every possible combination and way of doing it I can think of.

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-20 : 19:30:30

This is a side route, but I'm sold on the benefits of coding stored procedures. They ain't hard 2 rite...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources