Thread: using between with dates
I'm trying the following: ship_date between '04/30/2010' AND '04/30/2010' + 14 But this returns: ERROR: invalid input syntax for integer: "04/30/2010" Can I use between with dates? -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson
Geoffrey wrote: > I'm trying the following: > > ship_date between '04/30/2010' AND '04/30/2010' + 14 > > But this returns: > > ERROR: invalid input syntax for integer: "04/30/2010" > > Can I use between with dates? Got it: ship_date between '04/30/2010' and timestamp '04/30/2010' + interval '14 day' -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson
On Thursday 29 April 2010 6:58:26 am Geoffrey wrote: > I'm trying the following: > > ship_date between '04/30/2010' AND '04/30/2010' + 14 > > But this returns: > > ERROR: invalid input syntax for integer: "04/30/2010" > > Can I use between with dates? > > -- > Until later, Geoffrey > > "I predict future happiness for America if they can prevent > the government from wasting the labors of the people under > the pretense of taking care of them." > - Thomas Jefferson ship_date between '04/30/2010' AND '04/30/2010'::date + 14 -- Adrian Klaver adrian.klaver@gmail.com
Geoffrey <lists@serioustechnology.com> writes:
> ship_date between '04/30/2010' AND '04/30/2010' + 14
> ERROR: invalid input syntax for integer: "04/30/2010"
> Can I use between with dates?
The problem with that is the parser has no reason to treat the strings
as dates, at least not till it comes to consider the BETWEEN
comparisons, which is too late to help in resolving the addition
in the subexpression (data types are determined bottom-up).
This'd work:
ship_date between '04/30/2010' AND '04/30/2010'::date + 14
regards, tom lane
Tom Lane wrote: > Geoffrey <lists@serioustechnology.com> writes: >> ship_date between '04/30/2010' AND '04/30/2010' + 14 >> ERROR: invalid input syntax for integer: "04/30/2010" > >> Can I use between with dates? > > The problem with that is the parser has no reason to treat the strings > as dates, at least not till it comes to consider the BETWEEN > comparisons, which is too late to help in resolving the addition > in the subexpression (data types are determined bottom-up). > This'd work: > > ship_date between '04/30/2010' AND '04/30/2010'::date + 14 Thanks muchly, likely a better solution then my timestamp approach. -- Until later, Geoffrey "I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them." - Thomas Jefferson