Problem: Retrieving Records based on the From & To Date

[kavi2012]

Hi All

I want to Retrieve Records from the table based on the DOB. All the Dateformat in mm/dd/yyyy. I am using PostGreSql 8.2

Table: Employee
-------------------------
id Bigserial
name Character Varying
DOB TimeStamp without Timezone

Normal Select Query
-------------------------------

select id,name,to_char(dob,'mm/dd/yyyy hh:mi:ss') as dob from employee
 

1;"Arun";"08/20/2007 12:30:11"
2;"Priya";"08/20/2007 02:21:41"
3;"chitra";"08/20/2007 03:52:29"
 

I used this Query

select id,name, to_char(dob,'mm/dd/yyyy') as dob from Employee
where (dob >='08/20/2006' and dob <='08/20/2007')
 

By Executing this query I am not getting the Exact Records present in the Table.

can anybody suggest me how to rewrite the code. so that I can get the Exact Records.

Thanks
Kavitha

[amitpatel66]

Hi All

I want to Retrieve Records from the table based on the DOB. All the Dateformat in mm/dd/yyyy. I am using PostGreSql 8.2

Table: Employee
-------------------------
id Bigserial
name Character Varying
DOB TimeStamp without Timezone

Normal Select Query
-------------------------------

select id,name,to_char(dob,'mm/dd/yyyy hh:mi:ss') as dob from employee
 

1;"Arun";"08/20/2007 12:30:11"
2;"Priya";"08/20/2007 02:21:41"
3;"chitra";"08/20/2007 03:52:29"
 

I used this Query

select id,name, to_char(dob,'mm/dd/yyyy') as dob from Employee
where (dob >='08/20/2006' and dob <='08/20/2007')
 

By Executing this query I am not getting the Exact Records present in the Table.

can anybody suggest me how to rewrite the code. so that I can get the Exact Records.

Thanks
Kavitha

Please try below queries:

select id,name, to_char(dob,'mm/dd/yyyy') as dob from Employee
where dob BETWEEN 20-AUG-2006' AND '20-AUG-2007'
 

select id,name, to_char(dob,'mm/dd/yyyy') as dob from Employee
where (trunc(dob) >='08/20/2006' and trunc(dob) <='08/20/2007')
 

[kavi2012]

Please try below queries:

select id,name, to_char(dob,'mm/dd/yyyy') as dob from Employee
where dob BETWEEN '20-AUG-2006' AND '20-AUG-2007'
 

select id,name, to_char(dob,'mm/dd/yyyy') as dob from Employee
where (trunc(dob) >='08/20/2006' and trunc(dob) <='08/20/2007')
 

hi amitpatel
I tried both the above queries but when i use this query

select id,name, to_char(dob,'mm/dd/yyyy') as dob from Employee
where dob BETWEEN '20-AUG-2006' AND '20-AUG-2007'
 

records are not retrieving......
and for the next query

select id,name, to_char(dob,'mm/dd/yyyy') as dob from Employee
where (trunc(dob) >='08/20/2006' and trunc(dob) <='08/20/2007')
 

it is showing me the error as

ERROR: function trunc(timestamp without time zone) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You may need to add explicit type casts.
Character: 72
 

[amitpatel66]

hi amitpatel
I tried both the above queries but when i use this query

select id,name, to_char(dob,'mm/dd/yyyy') as dob from Employee
where dob BETWEEN '20-AUG-2006' AND '20-AUG-2007'
 

records are not retrieving......
and for the next query

select id,name, to_char(dob,'mm/dd/yyyy') as dob from Employee
where (trunc(dob) >='08/20/2006' and trunc(dob) <='08/20/2007')
 

it is showing me the error as

ERROR: function trunc(timestamp without time zone) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You may need to add explicit type casts.
Character: 72
 

For Second Query:

I think TRUNC function is not available in POSTGRESQL.

Coming to first Query:

Please check if you have any data between 20-AUG-2006 and 20-AUG-2007. If yes then those should be fetched else the Query will not return anything.

[kavi2012]

For Second Query:

I think TRUNC function is not available in POSTGRESQL.

Coming to first Query:

Please check if you have any data between 20-AUG-2006 and 20-AUG-2007. If yes then those should be fetched else the Query will not return anything.

hi amitpatel,
First query is working when i give 21 aug instead of 20 aug. Its ok but my need is that i want to get the result when i give either exact from date or to date.
ie., if i give between 20-aug-2006 and 21-aug-2007 i have to get the result having these two dates ie., my result retrieve rows having both 20-aug-06 as well as 21-aug-07 dates. for that what i have to do. Please try to solve this.
Then ,In second query you have used TRUNC is there any alternate for TRUNC in postgresql.

[amitpatel66]

hi amitpatel,
First query is working when i give 21 aug instead of 20 aug. Its ok but my need is that i want to get the result when i give either exact from date or to date.
ie., if i give between 20-aug-2006 and 21-aug-2007 i have to get the result having these two dates ie., my result retrieve rows having both 20-aug-06 as well as 21-aug-07 dates. for that what i have to do. Please try to solve this.
Then ,In second query you have used TRUNC is there any alternate for TRUNC in postgresql.

Why you need to use 21-AUG-2007 is because the data that you have in the table also contains TIME along with the date. So if the value is 20-AUG-2007 15:00:00 then this will not match with '20-AUG-2007' and you need to use the next day '21-AUG-2007' to get the values of '20-AUG-2007' as well.

Check for DATE functions in POSTGRESQL here

[kavi2012]

Why you need to use 21-AUG-2007 is because the data that you have in the table also contains TIME along with the date. So if the value is 20-AUG-2007 15:00:00 then this will not match with '20-AUG-2007' and you need to use the next day '21-AUG-2007' to get the values of '20-AUG-2007' as well.

Check for DATE functions in POSTGRESQL here

hi amitpatel
will u please give me an example of how to use date_trunc i can't understand. Please help me.

thanks
kavitha.

[amitpatel66]

hi amitpatel
will u please give me an example of how to use date_trunc i can't understand. Please help me.

thanks
kavitha.

Try using TO_CHAR in WHERE condition also as you have used in SELECT clause.

You can use DATE_TRUNC(hour,dob) to truncate the value till hour and then do comparison.

Eg:

WHERE (date_trunc(hour,dob) >= '08/20/2006' AND date_trunc(hour,dob) <= '08/20/2007')