Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2803
  • Last Modified:

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.
0
souldj
Asked:
souldj
  • 4
  • 3
  • 2
2 Solutions
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now