By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,962 Members | 1,776 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,962 IT Pros & Developers. It's quick & easy.

Problem: Retrieving Records based on the From & To Date

P: 12
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
-------------------------------
Expand|Select|Wrap|Line Numbers
  1. select id,name,to_char(dob,'mm/dd/yyyy hh:mi:ss') as dob from employee
  2.  
Expand|Select|Wrap|Line Numbers
  1. 1;"Arun";"08/20/2007 12:30:11"
  2. 2;"Priya";"08/20/2007 02:21:41"
  3. 3;"chitra";"08/20/2007 03:52:29"
  4.  
I used this Query
Expand|Select|Wrap|Line Numbers
  1. select id,name, to_char(dob,'mm/dd/yyyy') as dob from Employee
  2. where (dob >='08/20/2006' and dob <='08/20/2007')
  3.  
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
Sep 11 '07 #1
Share this Question
Share on Google+
7 Replies


amitpatel66
Expert 100+
P: 2,367
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
-------------------------------
Expand|Select|Wrap|Line Numbers
  1. select id,name,to_char(dob,'mm/dd/yyyy hh:mi:ss') as dob from employee
  2.  
Expand|Select|Wrap|Line Numbers
  1. 1;"Arun";"08/20/2007 12:30:11"
  2. 2;"Priya";"08/20/2007 02:21:41"
  3. 3;"chitra";"08/20/2007 03:52:29"
  4.  
I used this Query
Expand|Select|Wrap|Line Numbers
  1. select id,name, to_char(dob,'mm/dd/yyyy') as dob from Employee
  2. where (dob >='08/20/2006' and dob <='08/20/2007')
  3.  
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:

Expand|Select|Wrap|Line Numbers
  1. select id,name, to_char(dob,'mm/dd/yyyy') as dob from Employee
  2. where dob BETWEEN 20-AUG-2006' AND '20-AUG-2007'
  3.  
Expand|Select|Wrap|Line Numbers
  1. select id,name, to_char(dob,'mm/dd/yyyy') as dob from Employee
  2. where (trunc(dob) >='08/20/2006' and trunc(dob) <='08/20/2007')
  3.  
Sep 12 '07 #2

P: 12
Please try below queries:

Expand|Select|Wrap|Line Numbers
  1. select id,name, to_char(dob,'mm/dd/yyyy') as dob from Employee
  2. where dob BETWEEN '20-AUG-2006' AND '20-AUG-2007'
  3.  
Expand|Select|Wrap|Line Numbers
  1. select id,name, to_char(dob,'mm/dd/yyyy') as dob from Employee
  2. where (trunc(dob) >='08/20/2006' and trunc(dob) <='08/20/2007')
  3.  

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

Expand|Select|Wrap|Line Numbers
  1. select id,name, to_char(dob,'mm/dd/yyyy') as dob from Employee
  2. where dob BETWEEN '20-AUG-2006' AND '20-AUG-2007'
  3.  
records are not retrieving......
and for the next query

Expand|Select|Wrap|Line Numbers
  1. select id,name, to_char(dob,'mm/dd/yyyy') as dob from Employee
  2. where (trunc(dob) >='08/20/2006' and trunc(dob) <='08/20/2007')
  3.  
it is showing me the error as
Expand|Select|Wrap|Line Numbers
  1. ERROR: function trunc(timestamp without time zone) does not exist
  2. SQL state: 42883
  3. Hint: No function matches the given name and argument types. You may need to add explicit type casts.
  4. Character: 72
  5.  
Sep 12 '07 #3

amitpatel66
Expert 100+
P: 2,367
hi amitpatel
I tried both the above queries but when i use this query

Expand|Select|Wrap|Line Numbers
  1. select id,name, to_char(dob,'mm/dd/yyyy') as dob from Employee
  2. where dob BETWEEN '20-AUG-2006' AND '20-AUG-2007'
  3.  
records are not retrieving......
and for the next query

Expand|Select|Wrap|Line Numbers
  1. select id,name, to_char(dob,'mm/dd/yyyy') as dob from Employee
  2. where (trunc(dob) >='08/20/2006' and trunc(dob) <='08/20/2007')
  3.  
it is showing me the error as
Expand|Select|Wrap|Line Numbers
  1. ERROR: function trunc(timestamp without time zone) does not exist
  2. SQL state: 42883
  3. Hint: No function matches the given name and argument types. You may need to add explicit type casts.
  4. Character: 72
  5.  
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.
Sep 12 '07 #4

P: 12
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.
Sep 12 '07 #5

amitpatel66
Expert 100+
P: 2,367
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
Sep 12 '07 #6

P: 12
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.
Sep 12 '07 #7

amitpatel66
Expert 100+
P: 2,367
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')
Sep 12 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.