The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

This is the error message I get when trying to view a calendar built from ASP and MSSQL as the database

Microsoft OLE DB Provider for SQL Server error '80040e07'
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Here is my code : It works with ACCESS but with MSSQL I get thee problem.


dateSelect = Cdate(dayCounter & "/"  & thisMonth & "/"  & thisYear)


Set RSDATE = Server.CreateObject("ADODB.Recordset")

SQLDATE = "SELECT * FROM tbl_calendar_Events WHERE  (Day(Date) = Day('"&dateSelect&"')) AND (Month(Date) = Month('"&dateSelect&"')) AND (Year(Date) = Year('"&dateSelect&"'))"

'Response.Write SQLDate
'Response.End ()


RSDATE.Open SQLDATE, Conn, 1, 3
If NOT (RSDATE.BOF AND RSDATE.EOF) Then
      dateBack = "#DAE3F0"
Else
      dateBack = "#F0F0F0"
End If
RSDATE.close
set RSDATE = nothing


Please could I get a hand at sorting this.

i have tried dateSelect= Cdate(dayCounter & "/"  & thisMonth & "/"  & thisYear) but no joy.
LVL 1
souldjAsked:
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.

Aneesh RetnakaranDatabase AdministratorCommented:
first run this,
SET DATEFORMAT DMY
from your asp code then
SQLDATE = "SELECT * FROM tbl_calendar_Events WHERE  (Day(Date) = Day('"&dateSelect&"')) AND (Month(Date) = Month('"&dateSelect&"')) AND (Year(Date) = Year('"&dateSelect&"'))"

'Response.Write SQLDate
0
Brian CroweDatabase AdministratorCommented:
By default SQL expects dates in MM/dd/yyyy format.  The one format it will always expect regardless of regional settings is yyyyMMdd.

try this...

SQLDATE = "SELECT * FROM tbl_calendar_Events WHERE  Date = '" & dateselect.tostring("yyyyMMdd") & "'"
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
souldjAuthor Commented:
On trying

SET DATEFORMAT DMY

I got "missing =" so I added

SET DATEFORMAT = DMY and I got

Microsoft VBScript runtime (0x800A01A8)
Object required: 'DMY'

Thanks aneeshattingal , but stuck here.

with Bricowe, I tried

SQLDATE = "SELECT * FROM tbl_calendar_Events WHERE  Date = '" & dateselect.tostring("yyyyMMdd") & "'"


I got

Microsoft VBScript runtime (0x800A01A8)
Object required: 'dateSelect'

Stuck again.. Am I doing something wrong or neglecting something?
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.

Brian CroweDatabase AdministratorCommented:
I didn't know what language you were using so i posted my solution in vb.net.  I'm not sure how you format a date string in VBScript.

After doing a little research...

SQLDATE = "SELECT * FROM tbl_calendar_Events WHERE  Date = '" & formatdate(%Y%m$d, dateselect) & "'"
0
Aneesh RetnakaranDatabase AdministratorCommented:
SET DATEFORMAT DMY

the above is an  sql  statement, u need to execute it in ASP ..  I have no idea of ASP..

otherwise  u can make a try l

SQLDATE = "SET DATEFORMAT DMY ; SELECT * FROM tbl_calendar_Events WHERE  (Day(Date) = Day('"&dateSelect&"')) AND (Month(Date) = Month('"&dateSelect&"')) AND (Year(Date) = Year('"&dateSelect&"'))"
0
souldjAuthor Commented:
I tried this  
SQLDATE = "SELECT * FROM tbl_calendar_Events WHERE  Date = '" & formatdate(%Y%m$d, dateselect) & "' ;"

and got

Invalid character

SQLDATE = "SELECT * FROM tbl_calendar_Events WHERE Date = '" & formatdate(%Y%m$d, dateselect) & "' ;"
0
souldjAuthor Commented:
aneeshattingal ,

I tried your and got back to square 1 with this error message

Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

:-(
0
Aneesh RetnakaranDatabase AdministratorCommented:
can u run the sql profiler and watch the for the query after running your application? also post the query u r getting from the sql profiler
0
souldjAuthor Commented:
i went back to the code after the feedback you guys gave me and decided to change the arrangement for the date

dateSelect = Cdate(thisMonth  & "/"  & dayCounter & "/"  & thisYear) so that it was month/day/year and ran the ASP pages. It worked.

I will split the points between the both of you as you helped Greatly!!

Thanks Guys!
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.