Qurey related

Question

This is my Table This is my table data. Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable{mso-style-name:Table Normal;mso-tstyle-rowband-size:0;mso-tstyle-colband-size:0;mso-style-noshow:yes;mso-style-priority:99;mso-style-qformat:yes;mso-style-parent:;mso-padding-alt:0in 5.4pt 0in 5.4pt;mso-para-margin-top:0in;mso-para-margin-right:0in;mso-para-margin-bottom:10.0pt;mso-para-margin-left:0in;line-height:115%;mso-pagination:widow-orphan;font-size:11.0pt;font-family:Calibrisans-serif;mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:Times New Roman;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;} 27 2011-05-30 00:00:00.000 2011-05-31 00:00:00.000 2011-05-30 00:00:00.000 meeting description Violet CornflowerBlue NULL NULL 1 6 PM 8 PM Guntur 28 2011-06-05 00:00:00.000 2011-06-07 00:00:00.000 2011-06-01 00:00:00.000 discussion meeting SteelBlue Beige 1 1 1 4 PM 5 PM Hyderabad Just concentrate these columns i.e EventStartDateEventEndDateStartTime and EndTime. I am selecting EventStartDateEventEndDateStartTime and EndTime. So two records are available in the table. Next time I am selecting EventStartDateEventEndDateStartTime and EndTime Suppose data present between the dates and timings It has to return the data otherwise doesn't return. I want that Query.

Dorababu Meka · Answer

Mahesh if my post helps you can you mark as answer

Dorababu Meka · Answer

Mahesh mark the appropriate one as answer means the query's i posted

Dorababu Meka · Answer

Mahesh ok good to hear that it is resolved

Mahesh Babu · Answer

Ya Thank u Dorababu.

Dorababu Meka · Answer

Mahesh does your solution got resolved

Dorababu Meka · Answer

Mahesh check the previous query i posted as you are trying to search using AND it will check for the given date exist or not so try as i said by using OR as i posted in my previous post As per your posted query try this select * from tblEvents where EventStartDate between '2011-05-31' and '2011-06-01' or EventEndDate between '2011-05-31' and '2011-06-01' And StartTime between '1 PM' and '9 PM' or EndTime between '1 PM' and'9 PM'

Mahesh Babu · Answer

No that snap shot is my table data of four columns. I want to check the condition. this is my query see select * from tbl_Events where EventStartDate between '2011-05-31' and '2011-06-01' and EventEndDate between '2011-05-31' and '2011-06-01' And StartTime between '1 PM' and '9 PM' and EndTime between '1 PM' and'9 PM' Actually the table contained Starttime and EndTime is 6 PM  8 PM. Now When i execute the query it has to return the data. But it didn't return the data.

Dorababu Meka · Answer

Mahesh as you are using conditional statement it will check for whether data exists in that particular dates given so try by using select * from tblEvents where EventStartDate between '2011-05-31' and '2011-06-05' or EventEndDate between '2011-05-31' and '2011-06-08' And StartTime between '1 PM' and '9 PM' or EndTime between '1 PM' and'9 PM'

Dorababu Meka · Answer

Is this the output you needed

Mahesh Babu · Answer

Dorababu Meka · Answer

Mahesh Can you post a snap shot of your data

Mahesh Babu · Answer

Try this one. select * from tbl_Events where EventStartDate between '2011-05-31' and '2011-06-01' and EventEndDate between '2011-05-31' and '2011-06-01' And StartTime between '1 PM' and '9 PM' and EndTime between '1 PM' and'9 PM' It doesn't return data. But I want return the data between the timings

Dorababu Meka · Answer

Hi mahesh i tested with a sample input but i did not find any issue may be check with your Start time and end time once My sample screens Initial Data After query

Dorababu Meka · Answer

Mahesh can u attach the table and a sample data so that i will try and let u know if i can

Suthish Nair · Answer

That's why i said you need to use time in date value WHEN TO FIND A DATE RANGE RECORDS.

Mahesh Babu · Answer

I alredy used that between operator. see this one. select * from tbl_Events where EventStartDate between '2011-05-31' and '2011-06-01' and EventEndDate between '2011-05-31' and '2011-06-01' And StartTime between '1 PM' and '8 PM' and EndTime between '1 PM' and'8 PM' When I am using 30th date I got the data. But When I am using 31 date I didnt get the data. In this case also I want data.

Muralidharan Deenathayalan · Answer

You can't use in clause here. Please try in between condition. select * from tbl_Events where EventStartDate in( '2011-05-31''2011-06-01' ) and EventEndDate in( '2011-05-31''2011-06-01 ') And StartTime between '2 PM' and '9 PM' and EndTime between '2 PM' and '9 PM' I haven't tested the quest. Just for syntax error.

Mahesh Babu · Answer

Actually I taken static timings in two dropdownlists. I writen query like thissee ALTER procedure [dbo].[sp_GetEventTimings](@EventStartTime varchar(50)@EventEndTime varchar(50)@EventStartDate datetime@EventEndDate datetime) as begin select * from tbl_Events where EventStartDate in(@EventStartDate@EventEndDate) and EventEndDate in(@EventStartDate@EventEndDate) And StartTime in (@EventStartTime@EventEndTime) and EndTime in(@EventStartTime@EventEndTime) end Ex: select * from tbl_Events where EventStartDate in('2011-05-30''2011-05-31') and EventEndDate in('2011-05-30''2011-05-31') And StartTime in ('6 PM''8 PM') and EndTime in('6 PM''8 PM') The above query returns data. Suppose the query is like this Ex: select * from tbl_Events where EventStartDate in( '2011-05-31''2011-06-01' ) and EventEndDate in( '2011-05-31''2011-06-01 ') And StartTime in ( '2 PM''9 PM' ) and EndTime in ( '2 PM''9 PM' ) The above query doesn't returns the data. But I want to return data . Because 6 Pm to 8 Pm already available.

Suthish Nair · Answer

You need to use Time also with Date. eg: select * from table1 where date1 > '01-01-1900 09:00' and date1 <= '01-01-1900 10:00'

Mahesh Babu · Answer

This is my UI . I alredy sent the table. In the first record already event is there on 3oth and 31th and timings also there. Now suppose I selected date from 2011-05-27 to 2011-05-02 time from 11 AM to 8 PM It should be return msg Event already Exist so select different dates and different timings . I want quert for that one.

Abhimanyu K Vatsa · Answer

You may use dropdown list boxes and execute some query. Recently I answered another question that needs same concept. Look at this thread http://www.c-sharpcorner.com/Forums/Thread/123218/get-specific-data-from-other-database-tables.aspx

Dorababu Meka · Answer

Hi mahesh can you clearly tell the desired output it is hard to understand or post the output that you want so we will try our best