when i write this sentence in textbox in reporting service 2005 :
= iif(DateAdd( "h",Parameters! t.Value,Fields! completionTime. Value),"" ,Fields!completi onTime.Value)
SELECT expense_id, CAST(expense_id AS char(10)) + ' - ' + CAST(trip_km AS char(5))+ ' - ' + CAST(expense_amount AS char(5)) + ' - ' + charge_centre AS ExpenseDesc
If charge center is null, I need to ignore this field. How can I achieve this? The reason is that if any of the field is null, it will return ExpenseDesc as null.
Using SQL2000. According to Books Online, the avg aggregrate functionignores null values. ((3+3+3+3+Null)/5) predictably returns Null. Isthere a function to ignore the Null entry, adjust the divisor, andreturn a value of 3? For example:((3+3+3+3)/4) after ignoring Nullentry.If there's more than one null value, then adjust divisor accordingly.For example: ((5+5+5+4+Null+5+5+Null)/8) would be ((5+5+5+4+5+5)/6)after nulls ignored.Thanks for any help or advice.
Is there a way to ignore a column/variable when the whole result set for the applied filter (where) is null?
Not all clients have data for every variable, i.e. some variables are client specific. There are too many variables and clients to amend the select query every time so I just want to ignore a col if its null.
I hope that makes sense (my inability to describe it might explain my inability to find anything related to it!)
The next step would be to run all clients' data in one go using, I believe, a cursor, but one step at a time!
Hi, I do have a stored procedure with parameters inside. I just want to ignore those parameters that are NULL so that my WHERE clause will not execute them anymore. Here's my code:
CREATE PROCEDURE SEARCHPATIENT @patnCode varchar(10) = NULL, @patnSurname varchar(20) = NULL, @patnGivenName1 varchar(20) = NULL AS SELECT.... FROM ... WHERE patnCode=@patncode AND patnSurname =@patnSurname AND patnGivenName1 =@patnGivenName1
BUT... If the user passes @patnSurname as NULL, I got an SQL error message: "There was an error executing the query.. Timeout expired... ". I was hoping I could write an WHERE clause where IT WILL ONLY EXECUTE those parameters WITH VALUE and IGNORE those NULLs.
Can someone help me on this? Can I use IF THEN ELSE statement inside the WHERE clause? Can I use CASE STATEMENT inside the WHERE clause?
I have a stored procedure which accepts 3 arguments that are used in the WHERE clause of a SELECT Statement
CREATE PROCEDURE [dbo].[MyProcedure] @argYear INT, @argMonth INT, @argDay INT
AS
SELECT *
FROM MyData
WHERE MyData.Year = @argYear AND MyData.Month = @argMonth AND MyData.Day = @argDay
The problem that I am having is @argDay is an "optional" argument. If @argDay is NULL then I want to basically ignore the "AND MyData.Day = @argDay" condition.
Is there an easier way to do this than:
IF @argDay is NULL
SELECT *
FROM MyData
WHERE
MyData.Year = @argYear AND MyData.Month = @argMonthELSE
SELECT *
FROM MyData
WHERE
MyData.Year = @argYear AND MyData.Month = @argMonth AND MyData.Day = @argDay
I'm running the following test query on a single table:
SELECT sph.datestamp, sph.stocksymbol, sph.closing, DATENAME(dw, sph.datestamp), CASE DATENAME(dw, sph.datestamp) WHEN 'Monday' then 'Monday' ELSE (SELECT CAST(sph2.datestamp AS nvarchar) FROM BI_Test.dbo.StockDB AS sph2 WHERE sph2.DateStamp = DATEADD(d, -1, sph.datestamp) AND sph2.StockSymbol = 'NYA') END AS TestCase,
[Code] ....
And here's an example of the output I'm getting:
Why the exact same subquery in the THEN of the second CASE statement is returning NULL when the first one completes as expected?
Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you
set ansi_nulls off
go
declare
@inFileName VARCHAR (100),
@inFileSize INT,
@Id int,
@inlanguageid INT,
@inFileVersion VARCHAR (100),
@ExeState int
set @inFileName = 'A0006337.EXE'
set @inFileSize = 28796
set @Id= 1
set @inlanguageid =null
set @inFileVersion =NULL
set @ExeState =0
select Dr.StateID from table1 dR
where
DR.[FileName] = @inFileName
AND DR.FileSize =@inFileSize
AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)
Just want to double check this.To add 30 days to the current date in a stored procedure using SQL Server should be this: DATEADD(day, 30, GETDATE())Right?Thanks,Zath
I am trying to calcuate the finalsuit date for every 2 years becasue that when employees have to get their gaming license renewed, so I'm trying to add the renewal year by 2 because they have to get it done every two years
If I am running a scheduled job at the first of the month and I want to run an update sequence for the previous month, i was essentially using this sql:
What is the correct format for getting the last three months of data. in sql its DATEADD(mm-3,getdate()) but for some reason the report doesnt want to take this? what am i doing wrong?
I am using Derived Column Transformation Task to format my data as I am converting my DTS to SSIS. One of the columns requires to use DATEADD where I add 1 month and substruct one day from the date that I get in the flat file.(note that I always hardcode the day to "01" regardless of what I get in my flat file)
in managed code I get the right answer 11/1/2006. Between 1753 and 2007, there should have been 2007 - 1753 = 254 yrs x approx 365 = 92,710 days. Where is the year 2153 coming from?
Hi, I have a problem with working out some dates. I have a query that has a start date field and a number of days field. I know i can create another field that could provide the return date (DATEADD function) by adding the number of days to the start date. However the problem I have is that i need to discount the weekends from the return date. For example if the start date was a wednesday and the number of days was 3 the datadd sum would give a return date of saturday when in reality it should be monday. I am not sure if i am making sense but if anyone out there has any ideas it would be more than welcome. Andrew
hello, i have a Pictures table: PictureID, Name, Description, DateAdded (GETDATE() when insert), IsActive... i need to make some stored procedures to show me the pictures added in last 24hours, in last 3 days, last 2 weeks and so on the pictures added in database are active (available to be seen by users) only 1yaer after the date added I tryied to make a stored procedure (in fact i maked a lots of them, for 1day 3 days 1 week 1 month), but i have a problem with that DateDiff and DateAdd Here is what i tryied CREATE PROCEDURE LastAdded_2monthsAgo
AS
SELECT Pictures.ProductID, Pictures.Name, Pictures.Description, Pictures.DateAdded FROM Pictures WHERE (DATEDIFF(month, Pictures.DateAdded, GETDATE()) >= 0) AND (DATEDIFF(month, Pictures.DateAdded, GETDATE()) <= 2) ORDER BY DateAdded DescI have a feeling that is wrong, please make your own version and show me what i should write...I don't know what should be first the today date or the DateAdded...i need to select the last added products from a specific interval of time...Should i do something with that "1 year available" like WHERE (DATEDIFF(month, GETDATE(), DATEADD(year, 1, Products.DateAdded)) >= 0) AND (DATEDIFF(month, GETDATE(), DATEADD(year, 1, Products.DateAdded)) <= 2) I am sure is a stupid thig up there...if you can, make your own version how you would do it and show me..please help me
Hi ALL!anyone can help me resolve this problem.I create SQL sentence and using DATEADD to Update DATETIME FIELDand i need Increase 1 Year and Month is 04 and day is 01.example: origin datetime field : 2007/06/26result after update is : 2008/04/01so by DATEADD(yy,1, datefield)?Have any expression for datefield to set month and day like what i need?Thanks .
:eek: Hi, I'm getting problem while using DATEADD Function.
When I use DATEADD(MONTH, 1 '01/31/2005'), it returns 02/28/2005 and when I use DATEADD(MONTH, 1 '02/28/2005'), it returns 03/28/2005 but I want the result as 03/31/2005 ie last day of the month.
hi, can something tell me what function do i need if i want to know what happen the last three hours? for example: dateadd (hh, -3, getdate()) it seems not working?!!
Hi I'm trying to break down some code to work out how it's working. I've encountered DateDiff and DateAdd and I think this adds a new date of midnight today after reading up on the syntax (still dont fully understand how it works to be honest)
SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
However, this is slightly different because where there is a 0 above there is a 13 in this one so I'm wondering what the 13 is actually doing.
I am trying to find a group of clients that were entered last week. I am getting all the dates to go with the Monday of the week they were entered. i.e. If they were entered on 8-2 or 8-3, they go with 8-1. If they were entered, 8-9, they will be under the 8-8 Monday.
No problem with finding the Mondays using either the nice 'weekcommence' function I found here or using numerous t-sql examples.
One thing I don't understand is- how come I can't use an equal sign in my syntax?
For instance,
SELECT dbo.weekcommence(date_added) FROM clients where dbo.weekcommence(date_added) = dateadd(wk, -2, GETDATE() )
won't return any results. If I use a greater than sign, I get this week, 8-8 and last week 8-1. I only want to see the week of 8-1.
Now- look at what I have below: Shouldn't I really be able to see ONLY last week's Mondays with this?
To me- the way this reads is: show me 2 weeks ago, from these dates (first Mondays function). The results are the last two MONTHS, not weeks. What am I misinterpreting?
I can also use this:
SELECT dbo.weekcommence(date_added) FROM clients WHERE (dbo.weekcommence(date_added) >= dateadd("d",-7,DATEADD(wk, DATEDIFF(wk,1,getdate()), 0)))
and get the week's of 8-1 and 8-8. But I can't get JUST 8-1 if I take out the greater than sign.
To me- this one reads: Select all my Mondays from the function. Show me Mondays from my function that are = 7 days from this Monday's date.
It seems like it should be straightforward, but I'm obviously missing something big. Any help?
I want it to only list records with LastDateIn 10 days prior to today's date.
CREATE Procedure [dbo].[spRMU_NoFilesBookedInByDay] AS
SELECT Convert(nvarchar, LastDateIn,103) AS Expr1, COUNT(Status) AS Expr2 FROM tblFiles where Convert(datetime, LastDateIn,103) < dateadd(day, -10, getdate()) GROUP BY Convert(nvarchar, LastDateIn,103) ORDER BY Convert(nvarchar, LastDateIn,103) DESC
can i have brief explaination of datediff and dateadd function, regarding their arguments and params, definitions.. like the books online definition.. anybody have the books online url?
becoz im curious what this line do in detail(found it in http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729 : count age function and it's kewlLLl)
I need help in T-SQL.I am using DATEADD function and I want to add 6 months to a date. Butit does not return me the rusults, which I wante.g. SELECT DATEADD(m,'20040630') returns 20041230which is logical correct? But I want it to return end of month (i.e.20041231)Any help in this context will be highly appreciatedAbdul N. Khan
I have a bigint column called "MillisecondsSince1970" that I need to convert to a date - SSIS is erroring out when I use DATEADD with the 8 byte int (if I use 4 byte it works but the column is bigger than 4 byte). The error is really lame:
[Derived Column [79]] Error: The "component "Derived Column" (79)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "Date" (100)" specifies failure on error. An error occurred on the specified object of the specified component.
Anyone have a way around it... a VB.NET equivalent of DATEADD or something else I can do?
Problem: I have two nearly identical querys that are running. One of the query's is using the SQL DATEADD function. These query's are running on a SLQ2005 MSDE. There is now service pack four applied to this server. The question is: Are there any known issues with the DATEADD function in SQL 2005? This query is used in a report that has been in use for about two years. There were some changes made to the server (BIOS upgrade) including adding this service pack. These changes did not cause any other SQL issues. This query now takes almost five minutes to run. The nearly identical query (the difference is the date is passed in as a parameter rather than using the DATEADD function) takes from one to two seconds. These reports have a history of nearly identical running time.
What is a good way to try and correct the issue or at least to try and isolate the problem? Have there been any known issues with this function?
Heres My SQL to get me 2 hours back of Midnight, two months ago (so 11pm of the prev day)
SELECT DATEADD("hh",-2,DATEADD("m",-2,DATEADD(dd,-(DAY(GetDate())-1),DATEADD(day,DATEDIFF(day, 0, GETDATE()),0)))) So I converted it into RS for my report start date, but it's not running...cant even get preview to load, it detects an error right away.,,just doesnt tell me what it is
I'm pretty sure I have the "h", "m" and "d" syntax right, I tried them all individually and they give me the correct dates...other than changing GetDate() to Now() I dont know what makes this diffenent...is it just too much nesting to Reporting Services?