• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

sql query between two dates

 
indika prasad kularathne
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,
I am using sql Server 2000 and i have a table called UserData and it
contain a data field called RegDate.
Data type use for RegDate is varchar(10).It contians data as the format dd.mm.yyyy.
Now i want to write a query to get data on the table between two deferent days.

Eg:
SELECT Name,Address,Age,GPA FROM UserData WHERE RegDate BETWEEN '01.01.2004' AND '12.01.2004';

how i write a query for this task without changing the data type of the RegDate.

please help.
 
Srinivasa Raghavan
Ranch Hand
Posts: 1228
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by indika prasad kularathne:

Eg:
SELECT Name,Address,Age,GPA FROM UserData WHERE RegDate BETWEEN '01.01.2004' AND '12.01.2004';

how i write a query for this task without changing the data type of the RegDate.

please help.


Since this column is going to have a date why it's data type is varchar seems there some flaw in db design ( may be some reason behind it ).

I dont know about sql server but in case of oracle we can use to_date() function.
 
indika prasad kularathne
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks friend,

I know it is ok if i change the column type as date.but really
i want to do this without changing the database further.becouse
it contains number of data for several year as dd.mm.yyyy format
in a varchar type.

Indika
[ January 03, 2005: Message edited by: indika prasad kularathne ]
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just inverse the date like YYYY.MM.DD, then convert it into VARCHAR using TO_CHAR() kind of method, then use between operator.

Replyed without applying any thoughts. Just try it out.
 
Nitin Jawarkar
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

As you have given ex.

SELECT Name,Address,Age,GPA FROM UserData WHERE RegDate BETWEEN '01.01.2004' AND '12.01.2004';

definatly it will not work in SQL, so how can calculate date diff. of
this two value. So from my point of view, whatever front-end you use then you should parse that value like in java StringTokenizer and then make a String which look like date and then pass this string to your SQL query.
It may be long but should work.
Thanx....
 
Saso Jordanoski
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you use SQL Server 2000, you can use Convert function, like this:

SELECT Name,Address,Age,GPA FROM UserData WHERE
CONVERT(DATETIME,RegDate,104)
BETWEEN CONVERT(DATETIME,'01.01.2004',104)
AND CONVERT(DATETIME,'12.01.2004',104)

The only problem you will have is that the index on the Regdate field will not be used, but test youself if the performance is satisfying.
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
if ORACLE, you can use TO_DATE in place of CONVERT() mentioned in previous post.

cheers.
 
indika prasad kularathne
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks all for your reply.Saso Jordanoski's
reply is working.

thanks again...
[ January 04, 2005: Message edited by: indika prasad kularathne ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic