Improve company productivity with a Business Account.Sign Up

x
?
Solved

Weird Error Msg 296: conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value

Posted on 2004-10-14
5
Medium Priority
?
5,122 Views
Last Modified: 2008-01-09
SELECT S.StudentID, S.FirstName+' '+S.LastName AS SName, S.EnrollDate , PRs.RecieptID, PRs.TutionFee AS AmountPaid, PRs.PaymentDate, PRs.PaymentMode, PRs.CheckNo, PRs.Comments , PRs.CreditCardName FROM PaymentReciepts AS PRs INNER JOIN Students AS S ON (PRs.LocationID = S.LocationID) AND (PRs.StudentID = S.StudentID) WHERE (((PRs.PaymentDate) Between '09/01/2004' And '09/31/2004' )) ORDER BY PaymentDate Desc

Gives error: Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

HOWEVER - this is not giving an error:
SELECT S.StudentID, S.FirstName+' '+S.LastName AS SName, S.EnrollDate , PRs.RecieptID, PRs.TutionFee AS AmountPaid, PRs.PaymentDate, PRs.PaymentMode, PRs.CheckNo, PRs.Comments , PRs.CreditCardName FROM PaymentReciepts AS PRs INNER JOIN Students AS S ON (PRs.LocationID = S.LocationID) AND (PRs.StudentID = S.StudentID) WHERE (((PRs.PaymentDate) Between '10/01/2004' And '09/10/2004' )) ORDER BY PaymentDate Desc
0
Comment
Question by:MargusLehiste
  • 2
  • 2
5 Comments
 
LVL 8

Expert Comment

by:SNilsson
ID: 12310880

Check the PRs.PaymentDate column is the data type smalldatetime ?

If not, check the dates between '09/01/2004' And '09/31/2004' some charactor that dont belong might have slipped in the wrong place.

0
 
LVL 8

Expert Comment

by:MartinCMS
ID: 12310885
try....

WHERE Convert(Varchar(10), PRs.PaymentDate,101) Between '09/01/2004' And '09/30/2004'
ORDER BY PaymentDate Desc
0
 
LVL 8

Accepted Solution

by:
MartinCMS earned 2000 total points
ID: 12310890
September only have 30 days.
0
 
LVL 8

Expert Comment

by:SNilsson
ID: 12310953

 "September only have 30 days."

Right on :)

I did not notice, we use the YYYY-MM-DD formate in my country, nice spotted.
0
 
LVL 1

Author Comment

by:MargusLehiste
ID: 12313159
Gee - what was I thinking ....

(Anyway - my application needs better error checking)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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.

Join & Write a Comment

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question