Обсуждение: Comparing Dates
Hey, I am trying to compare dates in a sql statement. this is what i have tried: SELECT * FROM transactions WHERE shippingdate>2004-06-08 AND transtype='Sale'; but it returns all rows. When i switch the > with a < it returns nothing. I have even tried with todays date and have got the same results. So i guess my question is how do i compare two dates? BTW the column by the name of shippingdate is a date data type with the same format as the dates show above. Thanks in advance. -Nick
On Thu, 18 Nov 2004 15:01:58 -0600, Nick Peters <nick.peters@candoltd.com> wrote: > Hey, > > I am trying to compare dates in a sql statement. this is what i have tried: > > SELECT * FROM transactions WHERE shippingdate>2004-06-08 AND > transtype='Sale'; SELECT * FROM transactions WHERE shippingdate> '2004-06-08' AND transtype='Sale'; Ian Barwick
Nick, You need to quote your date constant value: '2004-06-08' select '2004-06-08'::date > 2004-06-08; ?column? ---------- t (1 row) select 2004-06-08; ?column? ---------- 1990 I'm not exactly sure how the bare string is converted internally, but it's clearly not a complete date like you're expecting. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Nov 18, 2004, at 3:01 PM, Nick Peters wrote: > Hey, > > I am trying to compare dates in a sql statement. this is what i have > tried: > > SELECT * FROM transactions WHERE shippingdate>2004-06-08 AND > transtype='Sale'; > > but it returns all rows. When i switch the > with a < it returns > nothing. I have even tried with todays date and have got the same > results. So i guess my question is how do i compare two dates? BTW the > column by the name of shippingdate is a date data type with the same > format as the dates show above. > > Thanks in advance. > > -Nick > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org
Thanks, it turns out that the code that was executing the sql was flawed. Thanks to all that replied! -Nick Ian Barwick wrote: >On Thu, 18 Nov 2004 15:01:58 -0600, Nick Peters ><nick.peters@candoltd.com> wrote: > > >>Hey, >> >>I am trying to compare dates in a sql statement. this is what i have tried: >> >>SELECT * FROM transactions WHERE shippingdate>2004-06-08 AND >>transtype='Sale'; >> >> > >SELECT * FROM transactions WHERE shippingdate> '2004-06-08' AND >transtype='Sale'; > >Ian Barwick > >---------------------------(end of broadcast)--------------------------- >TIP 7: don't forget to increase your free space map settings > > > >
"Thomas F.O'Connell" <tfo@sitening.com> writes: > select 2004-06-08; > ?column? > ---------- > 1990 > > I'm not exactly sure how the bare string is converted internally, but it's > clearly not a complete date like you're expecting. What string? That's just integer arithmetic. -- greg
Ha. Why so it is. :) -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Nov 18, 2004, at 11:50 PM, Greg Stark wrote: > "Thomas F.O'Connell" <tfo@sitening.com> writes: > >> select 2004-06-08; >> ?column? >> ---------- >> 1990 >> >> I'm not exactly sure how the bare string is converted internally, but >> it's >> clearly not a complete date like you're expecting. > > What string? That's just integer arithmetic. > > -- > greg
You should use single quotes for all literals. Examples: select '2004-06-08' ; ?column? ------------2004-06-08 select 'user' ;?column? ----------user Failing to quote literals will cause unexpected results. Examples: select 2004-06-08 ;?column? ---------- 1990 select user ;current_user --------------guy "Thomas F.O'Connell" <tfo@sitening.com> writes: > select 2004-06-08; > ?column? > ---------- > 1990 > > I'm not exactly sure how the bare string is converted internally, but > it's > clearly not a complete date like you're expecting. -- Guy Fraser Network Administrator The Internet Centre 780-450-6787 , 1-888-450-6787 There is a fine line between genius and lunacy, fear not, walk the line with pride. Not all things will end up as you wanted, but you will certainly discover things the meek and timid will miss out on.