.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Where Clause Using Relative Dates

Posted By:      Posted Date: October 05, 2010    Points: 0   Category :Sql Server

I am trying to figure out how to write the 'where' part of a query. Basically we have a table with a record every day, and the column of type DATETIME.

The 'where' part of the query I want is:

All records from 3 months ago (starting on the first day of the month) ending at the last day of the previous month.

So example, assuming today: October 5th.

I want: WHERE datetime BETWEEN '07/01/2010' AND '09/30/2010'

Another example, assuming today is November 30th:

I want: WHERE datetime BETWEEN '08/01/2010' AND '10/31/2010'


View Complete Post

More Related Resource Links

Power of using Clause in SharePoint

The Using Clause will help to avoid memory leaks in SharePoint by automatically Disposing the MOSS objects.

For example when we create SPSite and SPWeb objects, if we don't dispose it explicitly it might create memory leaks,

How to position one control relative to another one even after resizing windows?


I used following Jquery code trying to make image close to a table. It worked fine after first load, but the position changed after window is resized. Any ideas? thanks

$(document).ready(function() {
            var pos = $("#mytable").offset();
            var width = $("#mytable").width();
            var left = pos.left + width + 20;
            var top = pos.top + 200;
                position: 'absolute',
                zIndex: 5000,
                left: left,
                top: top

Need function to find difference between two dates


Hi all

I need a function written in vb to find the exact month difference between two dates.

For example

    Start Date : 01/01/2010

    End Date   : 15/02/2010

 then the difference value should be like this 1.5  i.e i need the difference in decimal value.

Need help concatenate varible to a SQL WHERE clause


Hey guys,

I looked over the web and couldn't find much help. Can someone help me with my SELECT statement? What I want to do is concatenate a varible to my WHERE clause. This is what I have:


myDataAdapter = new SqlDataAdapter("SELECT * FROM details WHERE DetailsID = " + lstBoxCategory.SelectedValue.ToString(), connection);


I tried this also but it didn't work:

myDataAdapter = new SqlDataAdapter("SELECT * FROM details WHERE DetailsID = '" + lstBoxCategory.SelectedValue.ToString() + "' ", connection);



EntityDataSource Where Clause in ViewState?



I have ListView/DataPager on the page that I use together with an EntityDataSource, because I want to filter the results, I programmatically set the Where-property and the WhereParameters of the EntityDataSource.

But: After the Paging-Postback the Value for Where is gone (empty string) whereas the WhereParameters are still there. Isn't the where value saved in ViewState as well? Can I change this behaviour?

Thanx, S.

BC30506: Handles clause requires a WithEvents variable defined in the containing type or one of its


why do i get

BC30506: Handles clause requires a WithEvents variable defined in the containing type or one of its base types.

with the code

 below ..thanks


<form id="form1" runat="server">
               <asp:Button ID="Button1" runat="server" Text="Run Access" />
In your code behind:

SQL Query Where Clause



FROM dbo.SSD a , dbo.SSAAppEAL b
AND (a.AssocClaim = 1)
AND b.somedate = SELECT MAX (b1.somedate)
                   FROM dbo.SSAAppEAL b1
                   WHERE b1.SSN = b.SSN
                   AND b1.somedate < sysdate

When ever I add this Query to my Select Statement. SQL management studio is shutting down abruptly.

The mail fault is with this statement

b.somedate = SELECT MAX (b1.somedate)
                   FROM dbo.SSAAppEAL b1
                   WHERE b1.SSN = b.SSN
                   AND b1.somedate < sysdate

Can any one help me out


Function and Variable declaration with out an 'As' clause


I get a message - "Function without an 'As' clause" for HMAC_MD5 and "Variable declaration without an 'As' clause" for Key and Value.  But the code works.  But the messages still exists.  Is there a way to fix this to avoid the messages?

Function HMAC_MD5(ByVal Key, ByVal Value)


How to increment default dates in the subscription


I have a report that I want run on a monthly subscription, but the report has a default date parameter. Is there a way to have the default date parameter increment each month. So if I choose to receive the subscription monthly I would want that default date to increment.

LINQ to XML - Example on how to replicate the SQL "IN" clause in LINQ


Can anyone please show me the example as how to replicated the functionality same as SQL "IN" clause using LINQ to XML ?

I have an XML file that I want to filter by passing multiple values to attribute parameters.

Any help will be greately appreciated

Get Dates from Sunday thru Saturday


I'm using the date picker and I need to know how (or if it's even possible), to select a date, and based on the date, have the actual query use the week the date was in and return all the records from that week (Sunday through Saturday)

Like - if I pick 8/24/2010 - it returns a date range of 8/22 through 8/28
I'd also like to display the date range in the report, somehow

Any ideas on how I do this?

Using IN clause(string parameter) in Stored Procedure

Hi guys. I need to use IN clause in a selection query. From all the resources that I found from google, it's all about adding a function to split a integer parameter into a list. But I got a string parameter, for example 'A1,B1,C1' I tried to use the below where single quotes in @CustCode is double up. ___________________________________ Declare @SQL VarChar(1000)   SELECT @SQL = ' SELECT [something]' SELECT @SQL = @SQL + ' FROM [Table]' SELECT @SQL = @SQL + ' WHERE CustomerCode IN ('  + @CustCode + ')'   EXECUTE (@SQL) _____________ Executing the stored procedure in MSSQL works fine, but it didn't when executing from Visual Studio 2005. Please advice.

Multiple values in where clause

How do I use the same statement to retrieve 2 values ABCD and XYZ? Now I get only one value ABCD - now I want only 2 values ABCD and XYZ. I'm looking for syntax SELECT [Measures].MEMBERS ON COLUMNS, NON EMPTY [0VENDOR].[LEVEL01].MEMBERS DIMENSION PROPERTIES [0VENDOR].[20VENDOR] ON ROWS FROM [OPOMCHS0/ZQT_GPO_PO_CHNG_REP_SUMM_3]WHERE[0VENDOR].[ABCD] Any help is appreciated

how to pass column value in WHERE clause

Friends, my scanario is fully generic and i m implementing this in database level by your quick response and help... i m trying to demostrate my problem with some sample table and SP (please see comment in SP for more clarification and understanding) the question is which i also write in SP's bottom area, Question, HOW CAN I PASS VALUE FOR COLUMNS "WF_Company_ID" AND "WF_Employee_ID" FROM TABLE ##SAT IN ABOVE STATEMENT AND GET VALUE FOR COLUMN "UID" FROM TABLE "MAINTABLE Tables are, CREATE TABLE MAINTABLE(UID INT, WF_Company_ID INT, WF_Employee_ID INT, WF_Position VARCHAR(50), WF_TimeSheet_ID INT) INSERT INTO MAINTABLE VALUES(1, 100, 625, 'Clerk', 1234) INSERT INTO MAINTABLE VALUES(2, 101, 626, 'Clerk', 1235) --SELECT * FROM MAINTABLE CREATE TABLE SEARCHCOLUMNS(C_KEY INT, C_NAME VARCHAR(100)) INSERT INTO SEARCHCOLUMNS VALUES(1, 'WF_Company_ID') INSERT INTO SEARCHCOLUMNS VALUES(1, 'WF_Employee_ID') INSERT INTO SEARCHCOLUMNS VALUES(2, 'TOTAL_PRICE') INSERT INTO SEARCHCOLUMNS VALUES(2, 'TOTAL_AMOUNT') --SELECT * FROM SEARCHCOLUMNS SP is below, CREATE PROCEDURE [dbo].[Test2] @StrXML XML AS BEGIN TRY DECLARE @ColList NVARCHAR(MAX) DECLARE @SQL NVARCHAR(MAX) CREATE TABLE #tXML (InputXML XML) INSERT INTO #tXML (InputXML) SELECT @StrXML SET @ColList = STUFF ( (

How does the IN ( ) clause affect performance?

When you have a query like this... Select * from Table1 WHERE a=1 and b=2 and c IN (5,6,7,8,9,0,1,2,3,4) How does SQL process that? I am trying to figure out if there is any real performance difference between the IN ( ) clause and a join to a temp table. Thanks! Jim

QUERY filter dates by DAY

i have the following data : first column: dates and 2nd column: values i want to filter the dates per day and calculate the AVERAGE of their values per day i can filter them manually by setting day(datetimefield) = 12 and calculate the AVERGAE but i dont know how to do it for all in one query? each record resulting the average of that day   2010-07-12 00:00:00.000 900 2010-07-12 10:00:00.000 1000 2010-07-12 10:10:00.000 1200 2010-07-12 12:00:00.000 1100 2010-07-12 15:00:00.000 1300 2010-07-12 15:10:00.000 1500 2010-07-13 00:00:00.000 6500 2010-07-13 10:00:00.000 5500 2010-07-14 00:00:00.000 5500 2010-07-14 10:00:00.000 4400 2010-07-15 10:00:00.000 4500 2010-07-16 00:00:00.000 1000 2010-07-17 00:00:00.000 7000 2010-07-18 00:00:00.000 8500 2010-07-19 00:00:00.000 1500 2010-07-20 00:00:00.000 1700 2010-07-21 00:00:00.000 1900 2010-07-22 00:00:00.000 2000  

how to prepare WHERE clause in vertical columns data

here the sample table X with datatype againt each data..how to prepare below WHERE clause from table X, WHERE   S_NAME = 'AYAZ' AND S_ROLL = 10 AND S_MARKS = 99 AND S_SCHOOL = 'ZINQ'     and table X is, CREATE TABLE X (UID INT IDENTITY(1,1), COL_N VARCHAR(50), COL_V VARCHAR(50), COL_D VARCHAR(50)) INSERT INTO X VALUES ('S_NAME', 'AYAZ', 'VARCHAR(50)') INSERT INTO X VALUES ('S_ROLL', '10', 'DECIMAL(15, 0)') INSERT INTO X VALUES ('S_MARKS', '99', 'INT') INSERT INTO X VALUES ('S_SCHOOL', 'ZINQ', 'VARCHAR(100)') --SELECT * FROM X  
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend