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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Comparing Dates

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

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

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

"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

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

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