SEARCH YOUR SOLUTION HERE  

SQL statement to search and sort by month?

I have a field in my table on my MSSQL server with dates, formatted like:

09/09/2004

Is there a way I can just pull the items for the current month

Something like

Select *
FROM table
Where date_field LIKE "today"

I will be using asp to query the DB if that helps

Posted On: Monday 19th of November 2012 11:56:17 PM Total Views:  135
View Complete with Replies




Related Messages:

SQL - expected end of statement in asp page   (64 Views)
I am getting the following error when trying to run a query, in an asp page. Microsoft VBScript compilation error '800a0401' Expected end of statement /isp/yr0607/bn3369/Group-10/asp/outstanding_debt.asp, line 58 The query is: strSQL = "SELECT Orders.[Order ID], Customers.Organisaton, Customers.[First name], Customers.Surname, Orders.[Customer email], Customers.[Telephone Number], Orders.[Outstanding Debt], Orders.[Total Cost], Orders.[Order Status], Orders.Date, Orders.[Staff ID] FROM (Customers INNER JOIN Orders ON Customers.[Customer email] = Orders.[Customer email]) INNER JOIN Staff ON Orders.[Staff ID] = Staff.[Staff ID] WHERE (((Orders.[Outstanding Debt])>0) AND ((Orders.[Order Status])="Complete") AND ((Orders.[Staff ID])= '" & strID & "' )) ORDER BY Orders.[Outstanding Debt] DESC;" I have tried removing the exclamation mark, putting spaces in and removing the semi-colon but it still complains. I copied this code from Access (which has the results ok) bar the staff ID part which looks at the session ID of the StaffID. The query has worked in Access, which is where I have copied it from. Any ideas would be much appreciated
=IIf statement help   (75 Views)
I know what Iwant to do but I am not sure how to do it exactly. Sub On Command60 Press =IIf text58
Paradox -> SQL statement   (70 Views)
hi, I've a problem that i expect you can help me... I want to do a search in a table by the year of a date on asp page.. the statement on paradox is: select extensao, numtelef,custochmd from registos where extract (year from data)= '2003' .. How can i do the same thing on sql statement on asp page.
Where's my error in this statement?   (105 Views)
SELECT table1.*, table2.Name, table3.MaintenanceID FROM table1INNER JOIN table2 ON table1.MachineID = table2.ID LEFT JOIN table3 ON table1.SheetID = table3.ID ORDER BY table2.Name It works fine without the LEFT JOIN, but as soon as I add that clause in I get the following error:Microsoft JET Database Engine error '80040e14' Syntax error (missing operator) in query expression 'table1.MachineID = table2.ID LEFT JOIN table3 ON table1.SheetID = table3.ID'. If someone could point out my mistake I'd appreciate it. Spara
is that such sql statement   (165 Views)
Code: strSQL = "Select * FROM "Session("datetime")" WHERE (studentID = '" & Session("StudID") & "')" objRst.Open strSQL, objConn
Struggling w/ SQL statement...not sure if its possible   (84 Views)
I have 3 tables: employees, skills and employee_skills The employees table has the following columns: emp_name emp_id the skills table has the following columns: skill_id skill_name the emp_skills table has the following columns: emp_id skill_id skill_level This is from a database that keeps info about call types (skills) for analyst in a center. I am trying to create view that has a column for the emp_name and a column for each skill name. I cant for the life of me get this to work...anyone provide any help Example of what I want EMP NAME skill1 skill2 skill3 Marcus 2 3 7 Jim 9 4 3
Help w/ SQL statement syntax error   (85 Views)
Here is my SQL statement: rs_totals.Source = "SELECT SUM (missed_sla_calls) as "total_calls" FROM missed_sla WHERE date_submit BETWEEN #" & request.form("from_date") & "# AND #" & request.form("to_date") & "#" And just in case its way off, here is what I am TRYING to get the sum of my missed_sla_calls column from my missed_sla table where the dates are between the two dates submitted by a user's input on a form. I have the statement working just fine if i replace SUM (missed_sla_calls) as "total_calls" with *, it will get all the data, and work correctly. When I put the SUM statement in, I received a syntax error when loading the page: Expected end of statement /psc/reports/missed_sla_log.asp, line 23, column 54 line 23 is my SQL statement.
SQL statement help with joins   (97 Views)
I have a massive database that I inherited so Im still having some trouble getting around it. I have 4 sql statements that I need to merge into one. I have a sql statement that is the basis for a report in MS Access (the tables are all linked to an odbc host) Here is the idea, I have a sql statement that pulls and joins data from five different tables and it works as it should. I think it is pretty complicated with all the inner joins and so forth, but anyway here it goes: SELECT DB2ADMIN_LOAD_HEADER.LOAD_ID, DB2ADMIN_LOAD_HEADER.LOAD_DATE, DB2ADMIN_LOAD_HEADER.EXTRA_DROPS, DB2ADMIN_LOAD_HEADER.EXTRA_PICKS, DB2ADMIN_LOAD_HEADER.CARRIER_ID, DB2ADMIN_REGION.REGION_RATE, DB2ADMIN_LOAD_ITEMS.ORDER_NO, DB2ADMIN_LOAD_ITEMS.STOP_NO, DB2ADMIN_LOAD_ITEMS.MILES, DB2ADMIN_ORDER_MASTER.NAMES, DB2ADMIN_ORDER_MASTER.ADDRESS_1, DB2ADMIN_ORDER_MASTER.ADDRESS_2, DB2ADMIN_ORDER_MASTER.CITY, DB2ADMIN_ORDER_MASTER.STATE, DB2ADMIN_ORDER_MASTER.ZIP, DB2ADMIN_ORDER_MASTER.CARRIER_MSG, DB2ADMIN_ORDER_MASTER.CUSTOMER_PO_NUMBER, Sum((CLng([DB2ADMIN_ORDER_DETAIL].[CASES_ORDERED]))) AS CASE_COUNT, Right([DB2ADMIN_ORDER_MASTER].[REQ_DEL_DATE],2) AS reqmonth, Mid([DB2ADMIN_ORDER_MASTER.REQ_DEL_DATE],5,2) AS reqday, Left([DB2ADMIN_ORDER_MASTER.REQ_DEL_DATE],4) AS reqyear FROM ((DB2ADMIN_LOAD_ITEMS INNER JOIN (DB2ADMIN_REGION INNER JOIN DB2ADMIN_LOAD_HEADER ON DB2ADMIN_REGION.REGION_NAME = DB2ADMIN_LOAD_HEADER.REGION_NAME) ON DB2ADMIN_LOAD_ITEMS.LOAD_ID = DB2ADMIN_LOAD_HEADER.LOAD_ID) INNER JOIN DB2ADMIN_ORDER_MASTER ON DB2ADMIN_LOAD_ITEMS.ORDER_NO = DB2ADMIN_ORDER_MASTER.ORDER_NUMBER) INNER JOIN DB2ADMIN_ORDER_DETAIL ON DB2ADMIN_ORDER_MASTER.ORDER_NUMBER = DB2ADMIN_ORDER_DETAIL.ORDER_NUMBER GROUP BY DB2ADMIN_LOAD_HEADER.LOAD_ID, DB2ADMIN_LOAD_HEADER.LOAD_DATE, DB2ADMIN_LOAD_HEADER.EXTRA_DROPS, DB2ADMIN_LOAD_HEADER.EXTRA_PICKS, DB2ADMIN_LOAD_HEADER.CARRIER_ID, DB2ADMIN_REGION.REGION_RATE, DB2ADMIN_LOAD_ITEMS.ORDER_NO, DB2ADMIN_LOAD_ITEMS.STOP_NO, DB2ADMIN_LOAD_ITEMS.MILES, DB2ADMIN_ORDER_MASTER.NAMES, DB2ADMIN_ORDER_MASTER.ADDRESS_1, DB2ADMIN_ORDER_MASTER.ADDRESS_2, DB2ADMIN_ORDER_MASTER.CITY, DB2ADMIN_ORDER_MASTER.STATE, DB2ADMIN_ORDER_MASTER.ZIP, DB2ADMIN_ORDER_MASTER.CARRIER_MSG, DB2ADMIN_ORDER_MASTER.CUSTOMER_PO_NUMBER, Right([DB2ADMIN_ORDER_MASTER].[REQ_DEL_DATE],2), Mid([DB2ADMIN_ORDER_MASTER.REQ_DEL_DATE],5,2), Left([DB2ADMIN_ORDER_MASTER.REQ_DEL_DATE],4), DB2ADMIN_LOAD_HEADER.NORBEST_LOAD_DATE, DB2ADMIN_LOAD_HEADER.NORBEST_LOAD_NO HAVING (((DB2ADMIN_LOAD_HEADER.NORBEST_LOAD_DATE)=[Norbest Load Date]) AND ((DB2ADMIN_LOAD_HEADER.NORBEST_LOAD_NO)=[Norbest Load No])); Its already so long I'm having trouble navigating through it, but as I said, it works its for a shipping system, you create a truck load, and you put orders on the load, but most of the order detail is in the orders tables, so that where all the joins come from. the load_header table has room for 3 extra charges that may or may not be on a particular load. those columns are extra_charge1, extra_charge2, and extra_charge3 there is a charges table with three columns: charge_code, charge_desc, and charge_rate Now, the following sql statement was a test... SELECT DB2ADMIN_LOAD_HEADER.LOAD_ID, DB2ADMIN_CHARGES.CHARGE_DESC, DB2ADMIN_CHARGES.CHARGE_RATE FROM DB2ADMIN_LOAD_HEADER LEFT JOIN DB2ADMIN_CHARGES ON DB2ADMIN_LOAD_HEADER.EXTRA_CHARGE1 = DB2ADMIN_CHARGES.CHARGE_CODE; I will need two more of this same select statement only difference would be that the join would be on extra_charge2, and then again on extra_charge3 I should be able to pick up then all of my extra charges that are for a given load. I need to merge these left join charge table selects into the big master one above. I realize that im going to need some table aliasing to get all three charge types as charge_desc1, and charge_desc2 for example
Sql query statement help   (90 Views)
In access, I have a query ("today's sales summary") which summarizes the total sales of 5 product categories for that given day. The data is being pulled from "table a". I need to add to this, I guess maybe through an SQL statement. The current one line summary displays as follows: total prod a - total prod b - total prod c - total prod d - total prod e. Underneath those field names are the total unit sales for each of those categories. What SQL language would be best to add the following 2 rows of info: The average selling price for each product category which is a fixed amount located in a separate table ("table b"). Then I need another row inserted to calculate total unit sales for each category multiplied by their average selling prices. Any suggestions on how to make this work would be greatly appreciated.
Where Statement with mulitiple or statements   (96 Views)
I am having problems with a where statement. I need to have the query bring back results if any of these cases are true. but it only seems to be bringing back instances of where the first statement is true. where ((t.enrolleddate is null and t.withdrawldate is null)or(t.enrolleddate =t.classdate) or (t.enrolleddate =t.classdate)) If i run each individual where clause it works ... but when i combine them it does not. can someone please help me
AND/OR select statement   (151 Views)
Hi I have a sql statement that tries to find values from a table but I want to use AND & OR in the same statement. I will give you an example so you know what I am talking about: my sql string at the moment is Code: strSQL = "select S_FIRSTNAME, S_SURNAME, S_GENDER, S_UNI from STUDENTS where S_SURNAME like ('" & strSname & "') OR S_FIRSTNAME like ('" & strFname & "') OR S_GENDER like ('" & strGender & "') OR S_UNI like ('" & strUni & "') order by S_SURNAME" This works and shows the results in a nice little table. However, if I want to search for Females at Manchester Uni (e.g.), the results that are shown are all females in the table and all people who attend Manchester Uni. Is there a way to say something like this: Code: strSQL = "select S_FIRSTNAME, S_SURNAME, S_GENDER, S_UNI from STUDENTS where S_SURNAME like ('" & strSname & "') AND OR S_FIRSTNAME like ('" & strFname & "') AND OR S_GENDER like ('" & strGender & "') AND OR S_UNI like ('" & strUni & "') order by S_SURNAME" I would really appreciate some help on this, I have been hunting for answers online all day and cannot find anything!
Query question for a Crystal report ( case or iif statement)   (88 Views)
hi, I have a Crystal report based on a query. There should be one field in the report- "setup type". This field is coming from 7 different fields. The user can pick one field. I want to use the CASE statement, I believe to get the values from these fields ( all booleans ) and then put the description on the report. My problem is I have 7 fields and I want to resolve to one. How do I do that in a query [code] case setup1 when setup1 = true then 'setup 1 ' end, case setup2 when setup2 = true then 'setup 2' end, etc..... [code] I wanted to define a field at the top of the query( in the Crystal report database expert area, but it won't let me ) ..
PLEASE HELP!!! Problem with extended SELECT statement and END IF's.   (54 Views)
. I'm trying to search on 3 database fields, but am trying to cater for every logical occurence of people leaving one or more search fields blank. This is the only way (very long winded) that I can see of doing this, and it still doesn't work. Could someone please tell me a) how I can get this script working b) if there is an easier and shorter way to do this.
ok.. I keep getting the same error over a different SQL select statements...Whats up!   (110 Views)
Ok, the error I get is --- ADODB.Recordset error '800a0bb9' Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. /vendorlist.asp, line 68 This has happened now on two different statements.. and I can't figure it out! This particular statement, i ran in QA and it goes perfect..lemme show ya statement and point out line 68 Code: Dim vendorRS, SQL set vendorRS = Server.CreateObject("ADODB.Recordset") SQL = "SELECT ttVendors.company, ttVendors.website, ttVendors.phone, ttVendors.phone800,ttVendors.vendorID," &_ SQL = SQL & "ttInventory.newprice, ttInventory.refurbprice, ttInventory.oosprice, ttInventory.venddescrip" &_ SQL = SQL & "FROM ttVendors INNER JOIN ttInventory on ttVendors.vendorID = ttInventory.vendorID" &_ SQL = SQL & "WHERE ttInventory.prodID = '" &prodID& "'" vendorRS.Open SQL ,objCn, 1, 3 'line 68 DIM company company = vendorRs("company") vendorRs.close set vendorRs = nothing why is this happening.. I can figure out not a thing! Normally i open rs like... vendorRs.open "Select * from wherever", objCn, 1, 3 but since this is a long one, the method above is the only one I know to get it on a bunch of lines.. The ONLY thing I can think of.. is that prodID is passed to the page from a querystring.. could the it be an STR in the page, but the statement need it to be an INT... I dunno, Im lost.. please help, did i do something silly
How do you execute a DTS package from a SQL statement.   (58 Views)
Originally posted by : Scott Gach (gach.scott@principal.com)Is there any way to do this.Execute a DTS package from a SQL statement.
Setting multiple variables in one statement   (77 Views)
Here is the issue. I have a statements that SUMs values as a variable, but i also want to set a variable to a column called "Comments". Here is my code: Code: sql = "SELECT Comments AS CMT," sql = sql & "SUM(CASE WHEN DispositionID = 1 THEN 1 ELSE 0 END) AS T1, " sql = sql & "SUM(CASE WHEN DispositionID = 2 THEN 1 ELSE 0 END) AS T2, " sql = sql & "SUM(CASE WHEN DispositionID = 3 THEN 1 ELSE 0 END) AS T3, " sql = sql & "SUM(CASE WHEN DispositionID = 4 THEN 1 ELSE 0 END) AS T4, " sql = sql & "SUM(CASE WHEN DispositionID = 5 THEN 1 ELSE 0 END) AS T5, " sql = sql & "SUM(CASE WHEN DispositionID = 6 THEN 1 ELSE 0 END) AS T6, " sql = sql & "SUM(CASE WHEN DispositionID = 7 THEN 1 ELSE 0 END) AS T7, " sql = sql & "SUM(CASE WHEN DispositionID = 8 THEN 1 ELSE 0 END) AS T8, " sql = sql & "SUM(CASE WHEN DispositionID = 9 THEN 1 ELSE 0 END) AS T9, " sql = sql & "SUM(CASE WHEN OutboundCall = 'First Call' THEN 1 ELSE 0 END) AS T10, " sql = sql & "SUM(CASE WHEN OutboundCall = 'Second Call' THEN 1 ELSE 0 END) AS T11, " sql = sql & "SUM(CASE WHEN OutboundCall = 'Third Call' THEN 1 ELSE 0 END) AS T12, " sql = sql & "CONVERT(varchar, dt, 101) AS do " It won't set the CMT variable.
SQL statement IF   (50 Views)
I am creating a simple form to send details of email addresses into a database. I have created 5 SQL strings to use infomation from a form twice. The code is working great but I have a flaw to it. If a parent has more than 1 child they enter the details of the second child and so on and I reuse their email address and parents name within for that SQL statement. But I have found if I do not enter a name for the 2nd to 5th student it still adds the email address and parent name into that field and leaves the other infomation blank. Is there a way I can create an IF statement to not insert into the database any string from 2 to 5 that doesnt have a student name Here is the code I am using: Code: If you could help me construct and IF statement I would be so greatful. I hope that all makes some kind of sense. Many
Excluding Records from a select statement   (95 Views)
Hi Just want to know if it possible to exclude records from a select statement. I have two tables one with news stories and one contains the stories the user has read. i want to produce a list of stories that the user has not read. tblRead = UserID, StoryID tblNews = StoryID, Headline, Summary etc. can anyone suggest a way i can generate a recordset containing all the stories in tblNews that are not listed for the UserID in tblRead. if that makes sense.
Add together 2 sql statements   (62 Views)
Hi all, I have three database tables, one (tbl_recruiter) has the companies information that is joined to tbl_jobs which will link all the jobs associated with that individual company. I have a third table (tbl_jobhits) which simply tracks how many times a job has been applied for. I was hoping to output all this information in one datalist. I have two sql statements which both work fine. The first counts how many entries are in tbl_jobhits for the specific job. The second Joins tbl_jobs with tbl_recruiters. I need to now join both these sql statement so that i can output the results in on Record set. Here are the two statements (which both work): PHP Code: SELECTtbl_jobhits.jobid,Count(*)AStotalCount FROMtbl_jobs,tbl_jobHits WHEREtbl_jobhits.jobid=tbl_jobs.id GROUPBYtbl_jobhits.jobid PHP Code: SELECTtbl_jobs.title,tbl_jobs.companyid,tbl_jobs.id,tbl_recruiter.id FROMtbl_recruiter,tbl_jobs WHEREtbl_recruiter.id=1ANDtbl_recruiter.id=tbl_jobs.companyid"
Help with SQL statement!   (106 Views)
Hi im trying to write a SQL statement. I have attached a copy of the relationhips windows so you will understand. In the InterComp table I want to add up all InterComp_Quantity fields and also the InterComp_Cost. Which is easy. The problem is I want to group them by month i.e show the total Quantity and cost for each month and obvously I need to be able to seperate the years (In other words I dont want Jan 05 and Jan 06 added together I need Jan 05 & 06 totals shown seperately!). Is this possibly