Comparing Dates
am 18.11.2004 22:01:58 von nick.peters
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
Re: Comparing Dates
am 18.11.2004 22:10:23 von barwick
On Thu, 18 Nov 2004 15:01:58 -0600, 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';
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
Re: Comparing Dates
am 18.11.2004 22:15:30 von tfo
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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: Comparing Dates
am 18.11.2004 22:34:36 von nick.peters
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
> 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
>
>
>
>
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Re: Comparing Dates
am 19.11.2004 06:50:26 von gsstark
"Thomas F.O'Connell" 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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Re: Comparing Dates
am 19.11.2004 07:07:55 von tfo
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" 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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: Comparing Dates
am 19.11.2004 17:05:03 von Guy
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" 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.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html