• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3328
  • Last Modified:

select error:syntax error during explicit conversion of varchar value '11-13-200723:59:59' to a datetime field

This a datawindow sql for a report.It looks like somehow the date conversion code we have in the query is concatenating the date/time correctly in some cases and others it doesnt work and we get no space between them.  It happens when we enter dates greater than 11/10/2007 for the to-date
I was thinking of  padding a space to the beginning of the time fields hardcoded in the SQL.
What you guys think.
select .....
where
 (itemhst.crdtstmp >= convert(datetime,convert(varchar(10),:a_da_fromdate) + '00:00:00')) and
         (itemhst.crdtstmp <= convert(datetime,convert(varchar(10),:a_da_todate) + '23:59:59')) 
 
crdtstmp  is a datetime field. The database is Sybase ASE 12.5. Powerbuilder ver 9

Open in new window

datawinerror.bmp
0
HARLIBRA
Asked:
HARLIBRA
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Yes have to add a space in between the values along with proper style definition so that Month and day wont be confused out.

Try out this query:

 (itemhst.crdtstmp >= convert(datetime,convert(varchar(10),:a_da_fromdate, 101) + ' 00:00:00',101)) and
         (itemhst.crdtstmp <= convert(datetime,convert(varchar(10),:a_da_todate,101) + ' 23:59:59',101))
0
 
grant300Commented:
Test the SQL in an interactive tool first so you don't get bound up with the reporting facility.  It will make things much easier.

Regards,
Bill
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now