Hi., Can anyone please assist me in getting the following SQL Query to work. It returns no errors but also returns no records even though there is actually data in my database for that period. Dim DCriteria as StringDCriteria = "StartDate <= " & EDateDCriteria = DCriteria & " AND EndDate >= " & SDatestrSQL = "SELECT * FROM vwLeavePlan WHERE " & DCriteria This results in the follwoing SQL Statement: strSQL = SELECT * FROM vwLeavePlan WHERE StartDate <= 31/05/2005 AND EndDate >= 01/05/2005 Regards. Peter
I need to select out a list of distinct pkeys and skeys only when there was no meeting between the two in November 2005. In other words, this query would produce only one record - the starred record - when run against this sample table. This is because even though, for example, skey 124 / pkey 2 has an entry outside the desired range, it also has one inside the desired range. The same goes for the record outside our range for 123 and 2 - it also has a record inside our range.
At this point, I've come to the conclusion that I can first select all the distinct pkeys and skeys where the servicedate is not in 11/2005 then join it to a selection of distinct pkeys and skeys where the servicedate IS in the desired date range. Does that seem like the most straightforward way through this?
I don't get the impression that this is that complicated a problem, but it's one of those deals where I goofed up somewhere along the line, and now I think I'm really overthinking the problem, so I'd be much obliged if someone could give me a hearty slap to clear the ol' noggin.
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
HI friends, I have a problem with sql server. I know that i should not discuss my problem in this forum. But the problem is demanding me to place it anywhere. The problem is i want to retreive data from a table between two dates. I have written the query as below. "select * from employees where hiredate between 01-05-1992 and 15-11-1994" But it is returning 0 rows.If anybody helps me in this issue it willbe helpful me.. Note: I have tried by changing all the date formats like DMY,MDY,YMD..........
I have a large table (2+Million records, 2000+ new per day). If I want to count the records in one month, I could do :-
Select Count(*) from Table1 where Added_Date between '1999-12-01' and '1999-12-31'
Select Count(*) from Table1 where Added_Date >= '1999-12-01' and Added_Date <= '1999-12-31'
For these two above, I get the same result.
If I then do:-
Select Count(*) from Table1 where Added_Date between '1999-12-01 00:00:00' and '1999-12-31 23:59:59' or use the >= and <= operators, the result is larger.
If I then do :-
Select Count(*) from Table1 where Added_Date between '1999-12-01 00:00:00.000' and '1999-12-31 23:59:59.999' or use the >= and <= operators, the result is larger still but i believe this including more rows than there actually is?.
I can get over the problem programatically using Convert to truncate to a day, month or year however, I don't understand why the results are different.
Any Ideas as to why this is happening, A'm I being stupid!!!!!!!
(PS Added_Date has a non_clustered index which has been rebuilt twice and DBCC finds no errors)
I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?
I have a table which records employees'time-off records. There are 6 columns in this TimeOff table. They are RequestID, EmpName, StartDate, EndDate, Type, NumofDays. I have another table which has all the dates from 01/01/1950 to 01/01/2056.
I am trying write a query which lists all the dates between the timeoff startdate and enddate, including the the start and end dates, but my query so far only lists the start and end date in a timeoff record:
SELECT D.[Date], Datename(dw,D.[Date]) AS Weekday FROM Dates D LEFT JOIN TimeOff T ON D.[Date] = T.OffStartDate OR D.[Date] = T.OffEndDate WHERE (OffType = 'Sick Day' AND EmpName = 'Cat White') AND (D.[Date] BETWEEN T.StartDate AND T.EndDate)
Has anyone ever written a function to retrieve all individual dates between two given dates? Using DATEDIFF I can get the number of days between two dates. However I need to iterate through the days to identify weekend and holiday dates. Has anyone ever written a function to do this?
So, if select datediff(d,'07/01/2007','07/15/2007') as NumOfDays returns 14, I'd need to iterate through the 14 days and get the weekends and holidays. Would I have to use a cursor to iterate through the days?
I'm trying to generate this query, that displays Budget Current Year , Actual Current Year and Prior Year Revenue. When It comes to the Budget and Actual everything works fine, however when I try to add the query for the Prior Year I get an error, and I realized that the leap date is causing the error
Here is what I'm trying to generate
InnCodeID Quarterly Monthly Days Period Year BARmRev AARmRev PYRmRev
ADDIS Q1 Jan 1 1 2008 NULL NULL
ADDIS Q1 Jan 1 1 2008 3462.14 5107.65
ADDIS Q1 Jan 1 1 2008 NULL NULL
ADDIS Q1 Jan 1 1 2008 NULL NULL
Here is the error that I'm getting:
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
(4834 row(s) affected)
Here is my Transact-SQL Syntax (summarized because I Couldn't post it):
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01'
AND Year(dbo.Trans.TR_Date) = Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1'
THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
FROM dbo.Trans Trans1
WHERE Trans1.TR_Dept = '10' AND TR_Main = '5120' AND TR_Sub = '01' AND trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date)
AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Year(dbo.CurrentDate.CurrDate) AS varchar(4))) AS datetime)
AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND trans1.datatype = dbo.Trans.DataType) ELSE NULL END) * - 1 AS BARmRev,
--AA Script Here AS AARmRev,
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01' AND Year(dbo.Trans.TR_Date)
= Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1' THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
FROM dbo.Trans Trans1
WHERE RIGHT(RTRIM(Trans1.TR_Dept), 2) = '10' AND Trans1.TR_Main = '5120' AND Trans1.TR_Sub NOT BETWEEN '04' AND '05' AND
trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2))
+ '/' + CAST(Year(dbo.CurrentDate.CurrDate)-1 AS varchar(4))) AS datetime) AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND
Hello All, I am trying to Add certain number of days to a particular date. and my requirement is that it need to exclude all saturdays and sundays and then give me the resultant date in Sqlserver. Please can anyone help me in achieving it. thanks Shiva Kumar
I've got a table (TYPE, DAYMTH) where it is a compound key and theDAYMTH is a datetime but all years are 1900 ONLY THE day and month aresignificant.What I want to do is for each type, find the next n dates - so forexample:TYPE DAYMTH1 1900-02-201 1900-05-051 1900-09-141 1900-11-112 1900-03-032 1900-07-07if I want to get the next 3 dates after 22 June 2005 it should give me:1 2005-09-141 2005-11-111 2006-02-202 2005-07-072 2006-03-032 2006-07-07so, anyone got any bright ideas?I've started down this route:create table #year (yr int)insert into #year values(2005)insert into #year values(2006)insert into #year values(2007)insert into #year values(2008)select *,convert(datetime,convert(varchar,yr) +substring(convert(varchar,daymth,20),5,20),20)from DAYMTHtable,#yearwhere convert(datetime,convert(varchar,yr) +substring(convert(varchar,daymth,20),5,20),20) > getdate()order by type,convert(datetime,convert(varchar,yr) +substring(convert(varchar,daymth,20),5,20),20)and although this seems to be a good start I wonder if there are bettersolutions. Also any quick ideas on how to finish if I decide to followmy existing thought process?thanksPhil
For some reason guys my SQL string isn't working and I'm probably doingsomething stupid?SELECT *FROM sol_sessionSELECT *FROM SOL_SessionWHERE DateGiven '10/09/2006' AND <'13/09/2006'The 'DateGiven' field is a DateTime field, and I can't see quite whatswrong??I simply get a Syntax error!Cheers, Ash
To support an application, I am integrating it with with Sqlserver2000. The task is simple: get the data at regular intervals, andwrite it into a table dedicated for that application. One of thefields I receive is a date, and it is formatted as an ISO date. Asample value looks like this: "2004-07-09-15.37.52.077258".SQLServer doesn't support this date format, though. The closest Ihave found is the style code "21" or "121" which accepts input as"yyyy-mm-dd hh:mi:ss.mmm". I have looked at using several replace()and convert() calls but no solution yet.Is there a way to transform the data to conform to this style acceptedby Sqlserver? I would like to do this in the INSERT statement itselfand not have to code application logic (i.e., user-defined function,etc.) for it.
HiI would like to create a SP where it will populate TableA based from TableB.TableB will be populated on a monthly basis using a DTS and within that Iwould like to run the SP to populate TableA.Can someone here please help me create the sql statements as a startingpoint.TIA!BobTableB (source)from_date to_date curr_code ex_rate1/1/2004 1/10/2004 CAD .750001/11/2004 1/16/2004 CAD .743211/17/2004 2/4/2004 CAD .722222/5/2004 2/20/2004 CAD .711112/21/2004 2/28/2004 CAD .778883/1/2004 3/3/2004 CAD .790023/4/2004 3/14/2004 CAD .768033/15/2004 3/23/2004 CAD .700223/24/2004 4/2/2004 CAD .73365etc...TableA (destination):date curr_code ex_rate1/2004 CAD 0.738477 calculation:(.75000+..74321+.72222) / 32/2004 CAD 0.737403(.72222+.71111+.77888) / 33/2004 CAD 0.74798(.79002+.76803+.70022+.73365) / 4etc..
We are deploying a system in the UK in SQL Server 6.5. They want the date to default to dd/mm/yy. Is there a way to set this on a permanent basis? Is there a way to set the server so any time a date is display, including getdate(), the result is dd/mm/yy?
Hi can anyone help me , or am I on the wrong track.
Is there any easy way to create a stored procedure that inserts into a table the relevant months dates into a table based on the month and year as parameters. I.e say the parameters passed are 01/2001 hence based on this all of the month of January 2001 dates are inserted into a table in this format : 'Jan 01 2001 12:00:00'
I am trying to calculate our job value and product costs over a given time period. But if I run my query, the numbers just dont look right at all. Please can you have a look at this query and see if anything stands out?
-- FINISHED PRODUCTS PRODUCTION REPORTS DATA -- gets current WIP Job information. -- requires item details from this. USE Staging -- declare start and end variables DECLARE @Start DATETIME DECLARE @End DATETIME SET DATEFORMAT DMY SET @Start = '01/01/2008' SET @End = '01/02/2008' SELECT SUM(jbs.Value) AS ProducionValue, SUM(prd.Cost) AS Cost, CONVERT(CHAR(10), @Start, 103) AS [From], CONVERT(CHAR(10), @End, 103) AS [End] FROM ( SELECT Job.[Job No], Job.[Cust Name], Job.[Req'd Date], Job.ProdStartActual, Job.Dept, [Job].[Value], Job.Loan FROM Job WHERE Job.[ProdStartActual] BETWEEN @start AND @End AND Job.Cancel = 0 AND Job.Sales = 1 AND Job.Invoiced = 1 OR Job.[ProdStartActual] BETWEEN @start AND @End AND Job.Cancel = 0 AND Job.Sales = 1 AND Job.Invoiced = 2 ) AS jbs INNER JOIN ( SELECT Item.[Job No], Item.[Code] FROM Item ) AS itm ON jbs.[Job No] = itm.[Job No] INNER JOIN ( SELECT Product.[Product Code], Product.[Cost] FROM Product ) AS prd ON itm.Code = prd.[Product Code]