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

sql, how do i run select command where issuedate is between range of date?

i would like to run select sql command with where condition which is from type of datetime and the condition "where" will be date and not datetime
issuedate is look like: 2007-03-22 00:00:00.000
what i have to change in the below sql command that it will run?

select * from cutheader where issuedate between '2007-0-22' and '2008-02-27'
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
0
gvilbis
Asked:
gvilbis
  • 7
  • 4
  • 2
  • +1
6 Solutions
 
FrozeniceCommented:
in your mysql table issuedate should be a timestamp type.

so from this..
select * from cutheader where issuedate between '2007-0-22' and '2008-02-27'


to this most probable..
select * from cutheader where issuedate >= 'strtotime(2007-0-22)' and issuedate <= 'strtotime(2008-02-27)'


please refer to http://php.net/manual/en/function.strtotime.php


BR
Frozenice
0
 
gvilbisAuthor Commented:
when i run the following:
select * from cutheader where issuedate >= 'strtotime(2007-0-22)' and issuedate <= 'strtotime(2008-02-27)'

the below error received:
Server: Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

please advice
0
 
qasim_mdCommented:
Try this and let me know if it helped:::

select * from cutheader where date(issuedate) between '2007-0-22' and '2008-02-27'
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

 
gvilbisAuthor Commented:
receiving the below error:

Server: Msg 195, Level 15, State 10, Line 1
'date' is not a recognized built-in function name.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you put the quotes on the wrong place

select * from cutheader where issuedate >= strtotime('2007-0-22') and issuedate <= strtotime('2008-02-27')

Open in new window

0
 
gvilbisAuthor Commented:
i tried this also but still receiving an error:
Server: Msg 195, Level 15, State 10, Line 1
'strtotime' is not a recognized built-in function name.
0
 
qasim_mdCommented:
try this:::

select * from cutheader where convert(date,issuedate) between '2007-0-22' and '2008-02-27'
0
 
gvilbisAuthor Commented:
Server: Msg 243, Level 16, State 1, Line 1
Type date is not a defined system type.
Server: Msg 243, Level 16, State 1, Line 1
Type date is not a defined system type.
0
 
gvilbisAuthor Commented:
still receiving error
0
 
qasim_mdCommented:
what version of MySQL are you using ???

This should work in MySQL :::

select * from cutheader where date(issuedate) between '2007-0-22' and '2008-02-27'
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
actually, if issuedate is already datetime:
select * from cutheader where issuedate >= '2007-02-22' and issuedate < '2008-02-28'

Open in new window

0
 
gvilbisAuthor Commented:
how do i check the version?
0
 
qasim_mdCommented:
run this to check the version:

select @@version;
0
 
gvilbisAuthor Commented:
anqelll, its working thanks all
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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