473,857 Members | 2,187 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

.net sql query

109 New Member
Hi all,

Need your help again.
Expand|Select|Wrap|Line Numbers
  1. string sql = " select ";
  2.                 sql += " q.ID, q.PALMNAME, q.CB_COMPLET, q.CMPLT_TIME, q.UNIT_NAME,";
  3.                 sql += " q.CB_CREW1, q.CRW1_NAME, q.CRW1_CERT, q.CB_CREW2, q.CRW2_NAME," ;
  4.                 sql += " q.CRW2_CERT, q.CREW_SIGN, q.SUPRV_NAME, q.SUPRV_SIGN, q.SUPRV_ASGN,";
  5.                 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,";
  6.                 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,"; 
  7.                 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,";
  8.                 sql += " q.CB_25, q.CB_26, q.CB_27, q.CB_28, q.CB_29, q.CB_30, ";
  9.                 sql += " q.DSCRPNCY01,q.DSCRPNCY02, q.DSCRPNCY03, q.DSCRPNCY04, q.DSCRPNCY05,";
  10.                 sql += " q.DSCRPNCY06, q.DSCRPNCY07, q.DSCRPNCY08, q.DSCRPNCY09, q.DSCRPNCY10,"; 
  11.                 sql += " q.DSCRPNCY11, q.DSCRPNCY12, q.DSCRPNCY13, q.DSCRPNCY14, q.DSCRPNCY15,"; 
  12.                 sql += " q.DSCRPNCY16, q.DSCRPNCY17, q.DSCRPNCY18, q.DSCRPNCY19, q.DSCRPNCY20,";
  13.                 sql += " q.DSCRPNCY21, q.DSCRPNCY22, q.DSCRPNCY23, q.DSCRPNCY24,"; 
  14.                 sql += " q.START_TIME, q.CB_DAY, q.CB_NIGHT, q.CB_24HR, q.CB_SPEV, q.E_ZOLL_FR,";
  15.                 sql += " q.E_PORSUN_F, q.CRT_DATE "; 
  16.                 /*sql += " sql_tapchrt1.crw1_name, sql_tapchrt1.crw2_name ";*/
  17.                 sql += " from Q_CHECK_GLASS q ";//, sql_tapchrt1 ";
  18.                 sql += " where q.unit_name like '"+strUnit.Text+"%'";
  19.                 sql  += " AND q.CRT_DATE = '"+StartDate.SelectedDate.ToShortDateString()+"'";
  20.                 sql += " And (q.CMPLT_TIME between '" + StartDate.SelectedDate.ToShortDateString() + " " + lsBeginTime + "' And '" + EndDate.SelectedDate.ToShortDateString() + " " + lsEndTime + "')";
  21.                 sql += " and q.CB_COMPLET = 'T'";
  22.  
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
4 1698
dip_developer
648 Recognized Expert Contributor
Hi all,

Need your help again.
Expand|Select|Wrap|Line Numbers
  1. string sql = " select ";
  2.                 sql += " q.ID, q.PALMNAME, q.CB_COMPLET, q.CMPLT_TIME, q.UNIT_NAME,";
  3.                 sql += " q.CB_CREW1, q.CRW1_NAME, q.CRW1_CERT, q.CB_CREW2, q.CRW2_NAME," ;
  4.                 sql += " q.CRW2_CERT, q.CREW_SIGN, q.SUPRV_NAME, q.SUPRV_SIGN, q.SUPRV_ASGN,";
  5.                 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,";
  6.                 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,"; 
  7.                 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,";
  8.                 sql += " q.CB_25, q.CB_26, q.CB_27, q.CB_28, q.CB_29, q.CB_30, ";
  9.                 sql += " q.DSCRPNCY01,q.DSCRPNCY02, q.DSCRPNCY03, q.DSCRPNCY04, q.DSCRPNCY05,";
  10.                 sql += " q.DSCRPNCY06, q.DSCRPNCY07, q.DSCRPNCY08, q.DSCRPNCY09, q.DSCRPNCY10,"; 
  11.                 sql += " q.DSCRPNCY11, q.DSCRPNCY12, q.DSCRPNCY13, q.DSCRPNCY14, q.DSCRPNCY15,"; 
  12.                 sql += " q.DSCRPNCY16, q.DSCRPNCY17, q.DSCRPNCY18, q.DSCRPNCY19, q.DSCRPNCY20,";
  13.                 sql += " q.DSCRPNCY21, q.DSCRPNCY22, q.DSCRPNCY23, q.DSCRPNCY24,"; 
  14.                 sql += " q.START_TIME, q.CB_DAY, q.CB_NIGHT, q.CB_24HR, q.CB_SPEV, q.E_ZOLL_FR,";
  15.                 sql += " q.E_PORSUN_F, q.CRT_DATE "; 
  16.                 /*sql += " sql_tapchrt1.crw1_name, sql_tapchrt1.crw2_name ";*/
  17.                 sql += " from Q_CHECK_GLASS q ";//, sql_tapchrt1 ";
  18.                 sql += " where q.unit_name like '"+strUnit.Text+"%'";
  19.                 sql += " AND q.CRT_DATE = '"+StartDate.SelectedDate.ToShortDateString()+"'";
  20.                 sql += " And (q.CMPLT_TIME between '" + StartDate.SelectedDate.ToShortDateString() + " " + lsBeginTime + "' And '" + EndDate.SelectedDate.ToShortDateString() + " " + lsEndTime + "')";
  21.                 sql += " and q.CB_COMPLET = 'T'";
  22.  
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 Contributor
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.sc uk
Mar 25 '08 #3
arial
109 New Member
My database is MS Sql 2000.
Mar 27 '08 #4
balabaster
797 Recognized Expert Contributor
I think the following T-SQL statement demonstrates the concept you are looking to achieve:

Expand|Select|Wrap|Line Numbers
  1. Select *
  2. From MyTable
  3. 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

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

Similar topics

2
3440
by: jaysonsch | last post by:
Hello! I am having some problems with a database query that I am trying to do. I am trying to develop a way to search a database for an entry and then edit the existing values. Upon submit, the new values are updated in all corresponding tables (the function of the pages in question). However, on the page that does the DB update, I also want to do some checks on the data before performing the update. Now, the problem that I am...
29
2477
by: shank | last post by:
1) I'm getting this error: Syntax error (missing operator) in query expression on the below statement. Can I get some advice. 2) I searched ASPFAQ and came up blank. Where can find the "rules" for when and how to use single quotes and double quotes in ASP? thanks! ---------------------- SQL = SQL & "WHERE '" & REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE("GenKTitles.
9
3143
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use SUBSTRING(ProductName, 1, CHARINDEX('(', ProductName)-2). I can get this result, but I had to use several views (totally inefficient). I think this can be done in one efficient/fast query, but I can't think of one. In the case that one query is not...
3
5397
by: Harvey | last post by:
Hi, I try to write an asp query form that lets client search any text-string and display all pages in my web server that contain the text. I have IIS 6.0 on a server 2003. The MSDN site says there is a sample file called Ixtrasp.asp, but I could not find it in my system although I installed indexing service. I followed the steps in MSDN site to create a basic .asp query form (too long to post it here), but it always displays: No...
4
2157
by: Diamondback | last post by:
I have two tables, WIDGETS and VERSIONS. The WIDGETS table has descriptive information about the widgets while the VERSIONS table contains IDs relating to different iterations of those widgets over the years. The idea is that from any widget in the database you can look forward and backward to see different versions of the widget through the years. Here are the tables: WIDGETS widget_id name
14
3909
by: Dave Thomas | last post by:
If I have a table set up like this: Name | VARCHAR Email | VARCHAR Age | TINYINT | NULL (Default: NULL) And I want the user to enter his or her name, email, and age - but AGE is optional. My insert would look something like:
0
3521
by: starace | last post by:
I have designed a form that has 5 different list boxes where the selections within each are used as criteria in building a dynamic query. Some boxes are set for multiple selections but these list boxes do not necessarily need to have a selection made to be used in the dynamic query. In essence the form can have selections made in all or none of its list boxes to form the dynamic query I am looking to get some feedback in reference to...
6
4858
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID SalesManName AT Alan Time
4
3141
by: Stan | last post by:
I am using MS Office Access 2003 (11.5614). My basic question is can I run a query of a query datasheet. I want to use more that one criteria and can not get that query to work. I thought I might be able to accomplish the results in two steps by using two queries. If this is possible how can I do it? Thank you, Stan Hanna
6
4415
by: jsacrey | last post by:
Hey everybody, got a secnario for ya that I need a bit of help with. Access 97 using linked tables from an SQL Server 2000 machine. I've created a simple query using two tables joined by one field between them. The join field in both tables are indexed and I'm selecting 1 field from each table to lookup. The Access query is taking more than 60 second to retrieve 1 record and if I execute the same query within the Query Analyzer, it...
0
9923
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9767
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10711
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10807
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10394
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7939
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5774
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4592
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4190
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.