|
Hi,
I have a table which has a date column "OrderedDate" which can be null in db.
Secondly i have a UI which has an frmdate and todate. While querying if the two textboxes are empty i need to show all the records( including the rows with null date). I have written the following query but it fetches the ros with non null values only.
AND
(
isnull(OrderedDate,@frmdate) between isnull(@frmdate,OrderedDate) and isnull(@todate,OrderedDate)
)
I can`t figure out whats the problem. Kindly look into the matter if possible. Thank You.
When you fail to plan, you are planning to fail.
|
|
|
|
|
You can construct separate queries for cases when the text boxes are empty.
|
|
|
|
|
Your logic seems flawed; I would try
AND
(
@frmdate IS NULL OR @todate IS NULL OR OrderedDate BETWEEN @frmdate AND @todate
)
warning: BETWEEN is inclusive or exclusive, depending on the actual database; when uncertain use explicit comparison with >= and <=
|
|
|
|
|
While I already answered your actual question, what I would really do myself is:
AND
(
@frmdate IS NULL OR OrderedDate >= @frmdate
}
AND
(
@todate IS NULL OR OrderedDate <= @todate
)
That allows you to leave either one empty, so you can get:
- all
- all up to some date
- all since some date
- all in an interval
|
|
|
|
|
There are two things you can do in this case.
1. Either you can check the null value of @frmdate and @todate at programming level and then
fire queries accordingly.
2. You can write the same condition in Stored procedure and fire query conditionally.
But, by using single query it is not possible.
Because when the @frmdate and @todate is null it will always consider those
fields with value and ignore rows having null in OrderDate column, there are
reason for such behavior.
1. Either between is evaluating while there is value in OrderDate column and
ignoring row while it is null.
Hope I will clear your problem.
Jinal Desai
|
|
|
|
|
I would take another approach. The Business Layer should not allow the UI layer to pass null values in; it should require proper DateTime values.
The UI layer should then pass in something like System.Data.SqlTypes.SqlDateTime.MinValue or System.Data.SqlTypes.SqlDateTime.MaxValue as appropriate.
You <i>are</i> parsing the TextBox values to DateTimes <i>before</i> passing them to the Business Layer <i>aren't</i> you?
In fact, why aren't you using DateTimePickers? 
|
|
|
|
|
PIEBALDconsult wrote: In fact, why aren't you using DateTimePickers?
Actually i am. I have a layered architecture and pass the datetime values as Nullable<datetime> which allows me to send a null datetime to my SP.
Anyways, Luc`s answer fits correctly for me.
AND
(
@frmdate IS NULL OR OrderedDate >= @frmdate
}
AND
(
@todate IS NULL OR OrderedDate <= @todate
)
Thanks again.
When you fail to plan, you are planning to fail.
|
|
|
|
|
Following is conditional code that will work for your situation.
declare @date1 as DateTime;
declare @date2 as DateTime;
If both the dates are specified the query goes as follow
set @date1='1970-04-04 00:00:00.000';
set @date2='1978-07-07 00:00:00.000';
if @date1 is null or @date2 is null
begin
select * from tblemployee
end
else
begin
select * from tblemployee WHERE
birthdate between @date1 and @date2
end
If any of the field or both of the field is null then the query goes as follow
set @date1=NULL;
set @date2='1978-07-07 00:00:00.000';
if @date1 is null or @date2 is null
begin
select * from tblemployee
end
else
begin
select * from tblemployee WHERE
birthdate between @date1 and @date2
end
Jinal Desai
|
|
|
|
|
Can I use the SQL Server Datbase mail as mail serve like the Microsft Exchange for internal and external emails?
|
|
|
|
|
My first reaction was to be rude, disparaging and sarcastic when answering this question, so was my second.
While I don't use sql mail much I have never even equated the 2 products as being in the same area, SQL mail is for notifying admins of problems and job progress etc, I would never have thought of it as a mail server!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I dont think so SQL Server email service is not a good option as a mail server.
Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
When I try to run this query, I get a message saying "Syntax error near '='" on the 2nd line (actually, any line that has the ISNULL check in it):
SELECT o.ID
,o.[Name] = ISNULL([Name], 'NO VALUE')
,o.Abbreviation = ISNULL(Abbreviation, 'NO VALUE')
,o.OrgTypeID = ISNULL(OrgTypeID, 'NO VALUE')
,o.DisplayOrder = ISNULL(DisplayOrder, 999)
,s.MetricNumber
,s.Status = ISNULL(Status, 'U')
,s.Command
FROM [dashboard].[tblSLOrganizations] o
RIGHT OUTER JOIN [dashboard].[tblMetricStatus] s
ON (s.Base = o.Abbreviation)
ORDER BY DisplayOrder, MetricNumber
If I comment out all of the "= ISNULL..." instances, the query works.
What am I doing wrong?
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
SELECT o.ID
,ISNULL(o.[Name], 'NO VALUE') AS Name
,ISNULL(o.Abbreviation, 'NO VALUE') AS abbreviation
,ISNULL(o.OrgTypeID, 'NO VALUE') AS OrgTypeID
,ISNULL(o.DisplayOrder, 999) AS DisplayOrder
,s.MetricNumber
,ISNULL(s.Status, 'U') AS Status
,s.Command
FROM [dashboard].[tblSLOrganizations] o
RIGHT OUTER JOIN [dashboard].[tblMetricStatus] s
ON (s.Base = o.Abbreviation)
ORDER BY DisplayOrder, MetricNumber
|
|
|
|
|
you beat me to it.
|
|
|
|
|
Sorry  been doing queries like that most of the week
|
|
|
|
|
Could you please clarify on why the column alias is required?
Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
While it is not required you end up with a result set with no relevant column headers and you cannot use such a query in a view.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yep..I agreed Thanks.
Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
after reading up a bit, I think you need an AS keyword:
SELECT o.ID
,ISNULL(o.[Name], 'NO VALUE') as [Name]
...
|
|
|
|
|
If T-SQL it is not mandatory.
|
|
|
|
|
I have a table in my DB containing a column of type Datetime, which allows null values. I created DataSet on which I drag&droped this table. Everything was fine, DataTable with TableAdapter have been successfully created and are both working. When I select this column in this DataTable (on designer surface) it shows in properties that this column is indeed nullable (AllowDBNull: True).
However ADO.NET generated false code for this example. Look at the following code:
var t= new ABCTableAdapter().GetData();<br />
ticket[0].MyNullableColumn -> this return 'Datetime' instead of 'Datetime?'.
If a given record in DB has indeed null value in this column, then such code generates "StrongTypingException". So this is quite clear that ADO.NET made some mistakes in code generation.
I tried deleting all DataSet and recreating it from scratch. Also I`m sure that my column really allows null values as some records have such value in this column.
Could you please help me and tell how am I supposed to force ADO.NET to generate this code properly? I want the following code to compile:
if (t[0].MyNullableColumn != DBNull.Value) {...}
|
|
|
|
|
This is why you don't use the drag and drop crap.
It generates a method .IsMyNullableColunNull()
|
|
|
|
|
Jamie is right, learn to use a proper DAL and get control of your application, don't rely on MS generated code!
This may be a better option for you
t[0].MyNullableColumn ?? DBNull.Value
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Sure sure, its the best to write everything in assembler or even machine code. Such approach is senseless. ADO's designer saved me a lot of time during many years, this is first time it fails. Still a very good result.
|
|
|
|
|
Yoyosch wrote: Sure sure, its the best to write everything in assembler or even machine code.
Actually thats a bloody silly statement, I'm talking about the framework you use every day.
Yoyosch wrote: ADO's designer saved me a lot of time during many years, this is first time it fails
This would seem to indicate that you are doing fairly straight forward CRUD operations. We see this type of problem in the forums regularly, and yes it is generally something slightly different.
IMHO it is not the productive issues, the designer/wizard stuff does make it quick and simple, it is the lack of any deep knowledge about your data and tools.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
