Fetch records from sql on basis of datetime.

Jeff C

16y4.7kSQL

Hello,
I am attempting to do a query with the following:
I have a table called callstats, it has 2 columns I'm interested in: OfferTime & AnswerTime. The data contained in them is in the format dd/mm/yyyy hh:mm:ss
Offertime always has a data in it, but sometimes AnswerTime can be empty.
The table itself has a year's worth of data.
I want a query that will give me whatever month's worth I query, I've tried several variations of the following to get a month's worth and nothing
"SELECT OfferTime, AnswerTime FROM callstats WHERE OfferTime LIKE '%Jan%' AND AnswerTime LIKE '%'"
This isn't returning any results. Basically I'm interested in entries for both columns, I want it to check the first column, if it's Jan, then read it and the 2nd column regardless if it's got data in it or not.

Thanks in advance.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

PJ Martins16y ago
what kind of database are you connecting to?
Venkatesan Jayakantham16y ago
Hope, the below query will be useful,
drop table sample_table
create table sample_table (id int, dat datetime)
insert into sample_table values(1,getdate())
select * from sample_table
select id,dat from sample_table where datename(month,dat)='June'

-----------------------------------------
id dat
-----------------------------------------
1 2010-06-07 10:54:52.607
-----------------------------------------
Cheers,
Venkatesan Prabu .J
http://venkattechnicalblog.blogspot.com/
Venkatesan Jayakantham16y ago
Hope, the below query will be useful,
drop table sample_table
create table sample_table (id int, dat datetime)
insert into sample_table values(1,getdate())
select * from sample_table
select id,dat from sample_table where datename(month,dat)='June'

-----------------------------------------
id dat
-----------------------------------------
1 2010-06-07 10:54:52.607
-----------------------------------------
Cheers,
Venkatesan Prabu .J
http://venkattechnicalblog.blogspot.com/
Jeff C16y ago
Yeah that's exactly what I'm trying to do, the log itself contains multiple years worth of data and I'm attempting to isolate it to get just 1 month at a time.

I'm trying to use the OledbCommand to do that query and it won't work...the best I can do is get it to retrieve every record in both columns by using "SELECT OfferTime,AnswerTime FROM callstats" and it works fine

I will try your suggestion and get back to you, thanks!!.
PJ Martins16y ago
Not clear what you're trying to get, but the following query will retrieve all rows where OfferTime is january and AnswerTime could be anything:
select OfferTime, AnswerTime FROM callstats where datepart(m, OfferTime) = 1