DateTime Woes - 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

Transact-SQL (2000)

DateTime Woes

Author

Topic

droberts
Starting Member

3 Posts

Posted - 2003-11-25 : 10:42:58

Ok, I'm a little lost here.
When I enter:
@startdate = 10/01/2003
@enddate = 10/31/2003

it works fine.

When I enter:
@startdate = 11/01/2003
@enddate = 11/31/2003

I get:

"Server: Msg 242, Level 16, State 3, Procedure _spGetDates, Line 8
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Stored Procedure: CCDotNet.dbo._spGetDates
Return Code = -6"

There are definitely November logins in the DB.

This is the SP:

----------------------------
CREATE PROCEDURE _spGetDates

(
@startdate char(10),
@enddate char (10)
)
AS

SELECT *
FROM (SELECT MemberID, LoginDate,
(SELECT COUNT(*)
FROM TBLLogins
WHERE d .MemberID = TBLLogins.MemberID AND d .LoginDate >= TBLLogins.LoginDate) AS LogOnNumber
FROM TBLLogins d) A
WHERE (LogOnNumber <= 5) AND CAST(FLOOR(CAST(LoginDate AS FLOAT))AS DATETIME) Between @startdate and @enddate
GO
----------------------------

Many, many thanks in advance for the help.

droberts
Starting Member

3 Posts

Posted - 2003-11-25 : 10:55:50

Yeah, (ahem) sorry folks. Only 30 days this month right?

*shakes head...walks away*

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-25 : 16:51:06

use datetime types as your parameters, not chars ... The "between" operator will not work correctly using Char's as dates unless you store them in the order of YYYYMMDD.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources