.net sql query

109 100+

Hi all,

Need your help again.

Expand|Select|Wrap|Line Numbers

 
string sql = " select ";

                sql += " q.ID, q.PALMNAME, q.CB_COMPLET, q.CMPLT_TIME, q.UNIT_NAME,";

                sql += " q.CB_CREW1, q.CRW1_NAME, q.CRW1_CERT, q.CB_CREW2, q.CRW2_NAME," ;

                sql += " q.CRW2_CERT, q.CREW_SIGN, q.SUPRV_NAME, q.SUPRV_SIGN, q.SUPRV_ASGN,";

                sql += " q.CB_01, q.CB_02, q.CB_03, q.CB_04, q.CB_05, q.CB_06, q.CB_07, q.CB_08,";

                sql += " q.CB_09,q.CB_10, q.CB_11, q.CB_12, q.CB_13, q.CB_14, q.CB_15, q.CB_16,"; 

                sql += " q.CB_17, q.CB_18, q.CB_19, q.CB_20, q.CB_21, q.CB_22, q.CB_23, q.CB_24,";

                sql += " q.CB_25, q.CB_26, q.CB_27, q.CB_28, q.CB_29, q.CB_30, ";

                sql += " q.DSCRPNCY01,q.DSCRPNCY02, q.DSCRPNCY03, q.DSCRPNCY04, q.DSCRPNCY05,";

                sql += " q.DSCRPNCY06, q.DSCRPNCY07, q.DSCRPNCY08, q.DSCRPNCY09, q.DSCRPNCY10,"; 

                sql += " q.DSCRPNCY11, q.DSCRPNCY12, q.DSCRPNCY13, q.DSCRPNCY14, q.DSCRPNCY15,"; 

                sql += " q.DSCRPNCY16, q.DSCRPNCY17, q.DSCRPNCY18, q.DSCRPNCY19, q.DSCRPNCY20,";

                sql += " q.DSCRPNCY21, q.DSCRPNCY22, q.DSCRPNCY23, q.DSCRPNCY24,"; 

                sql += " q.START_TIME, q.CB_DAY, q.CB_NIGHT, q.CB_24HR, q.CB_SPEV, q.E_ZOLL_FR,";

                sql += " q.E_PORSUN_F, q.CRT_DATE "; 

                /*sql += " sql_tapchrt1.crw1_name, sql_tapchrt1.crw2_name ";*/

                sql += " from Q_CHECK_GLASS q ";//, sql_tapchrt1 ";

                sql += " where q.unit_name like '"+strUnit.Text+"%'";

                sql  += " AND q.CRT_DATE = '"+StartDate.SelectedDate.ToShortDateString()+"'";

                sql += " And (q.CMPLT_TIME between '" + StartDate.SelectedDate.ToShortDateString() + " " + lsBeginTime + "' And '" + EndDate.SelectedDate.ToShortDateString() + " " + lsEndTime + "')";

                sql += " and q.CB_COMPLET = 'T'";

on my sql statement here, lsBegineTime and lsEndTime i need to change so that it can include the record created an hour before and an hour after.

so, I am looking for something like this, lsBeginTime - one hour and lsEndTime + one hour. How can I accomplish this?

please please help. It is very urgent for me.

Thank You,

Mar 24 '08 #1

Subscribe Post Reply

1666

dip_developer

648

Expert 512MB

Hi all,

Need your help again.

Expand|Select|Wrap|Line Numbers

string sql = " select ";

                sql += " q.ID, q.PALMNAME, q.CB_COMPLET, q.CMPLT_TIME, q.UNIT_NAME,";

                sql += " q.CB_CREW1, q.CRW1_NAME, q.CRW1_CERT, q.CB_CREW2, q.CRW2_NAME," ;

                sql += " q.CRW2_CERT, q.CREW_SIGN, q.SUPRV_NAME, q.SUPRV_SIGN, q.SUPRV_ASGN,";

                sql += " q.CB_01, q.CB_02, q.CB_03, q.CB_04, q.CB_05, q.CB_06, q.CB_07, q.CB_08,";

                sql += " q.CB_09,q.CB_10, q.CB_11, q.CB_12, q.CB_13, q.CB_14, q.CB_15, q.CB_16,";

                sql += " q.CB_17, q.CB_18, q.CB_19, q.CB_20, q.CB_21, q.CB_22, q.CB_23, q.CB_24,";

                sql += " q.CB_25, q.CB_26, q.CB_27, q.CB_28, q.CB_29, q.CB_30, ";

                sql += " q.DSCRPNCY01,q.DSCRPNCY02, q.DSCRPNCY03, q.DSCRPNCY04, q.DSCRPNCY05,";

                sql += " q.DSCRPNCY06, q.DSCRPNCY07, q.DSCRPNCY08, q.DSCRPNCY09, q.DSCRPNCY10,";

                sql += " q.DSCRPNCY11, q.DSCRPNCY12, q.DSCRPNCY13, q.DSCRPNCY14, q.DSCRPNCY15,";

                sql += " q.DSCRPNCY16, q.DSCRPNCY17, q.DSCRPNCY18, q.DSCRPNCY19, q.DSCRPNCY20,";

                sql += " q.DSCRPNCY21, q.DSCRPNCY22, q.DSCRPNCY23, q.DSCRPNCY24,";

                sql += " q.START_TIME, q.CB_DAY, q.CB_NIGHT, q.CB_24HR, q.CB_SPEV, q.E_ZOLL_FR,";

                sql += " q.E_PORSUN_F, q.CRT_DATE ";

                /*sql += " sql_tapchrt1.crw1_name, sql_tapchrt1.crw2_name ";*/

                sql += " from Q_CHECK_GLASS q ";//, sql_tapchrt1 ";

                sql += " where q.unit_name like '"+strUnit.Text+"%'";

                sql += " AND q.CRT_DATE = '"+StartDate.SelectedDate.ToShortDateString()+"'";

                sql += " And (q.CMPLT_TIME between '" + StartDate.SelectedDate.ToShortDateString() + " " + lsBeginTime + "' And '" + EndDate.SelectedDate.ToShortDateString() + " " + lsEndTime + "')";

                sql += " and q.CB_COMPLET = 'T'";

on my sql statement here, lsBegineTime and lsEndTime i need to change so that it can include the record created an hour before and an hour after.

so, I am looking for something like this, lsBeginTime - one hour and lsEndTime + one hour. How can I accomplish this?

please please help. It is very urgent for me.

Thank You,

just see the Date and Time function of sql-server[i dont know ur database though] and try to make out how to add time to a a date.....

see this.............

http://weblogs.sqlteam.com/jeffs/jef.../02/56079.aspx

Mar 25 '08 #2

kunal pawar

297

100+

u can used DateAddfunction of sql server for ur requirement

This link will help u
http://msdn2.microsoft.com/en-us/library/aa258863(SQL.80).aspx
http://www.sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

Mar 25 '08 #3

arial

109

100+

My database is MS Sql 2000.

Mar 27 '08 #4

balabaster

797

Expert 512MB

I think the following T-SQL statement demonstrates the concept you are looking to achieve:

Expand|Select|Wrap|Line Numbers

 Select *

From MyTable

Where SomeDateWeSupply Between DateAdd(h, 1, StartDate) And DateAdd(h, 1, EndDate)

This is standard T-SQL, so it should work on SQL Server 2000 (although I don't have a 2000 box to check it against).

The following page should be of assistance:

http://doc.ddart.net/mssql/sql70/da-db_5.htm

Mar 27 '08 #5

Similar topics