New to SQL.
I'm trying to include 2 dates in a query. They query works, but not
logically. I'm sure I'm missing something small.
When the query runs, it doesn't include dates that have times later than
12:00:00AM. How do I get rid of that?
SELECT dbo.Orders.OrderID, dbo.Orders.CustomerID, dbo.Orders.OrderDate,
dbo.Products.ProductName
FROM dbo.Orders INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order
Details].OrderID INNER JOIN
dbo.Products ON dbo.[Order Details].ProductID =
dbo.Products.ProductID
WHERE (dbo.Orders.OrderDate >= @.ordDate) AND (dbo.Orders.OrderDate <=
@.ordDate2)This example should demonstrate.
DECLARE @.ordDate datetime
DECLARE @.ordDate2 datetime
SET @.ordDate '20060531' -- As no time is specified this will be interpreted
as '20060531 00:00'
SET @.ordDate2 '20060531 23:59:59'
To capture all data on the 31-May-2006 your WHERE clause would be :-
WHERE dbo.Orders.OrderDate BETWEEN @.ordDate AND @.ordDate2
HTH. Ryan
"Tony K" <king-tony2@.comcast.nospam.net> wrote in message
news:O%23IUoRFhGHA.4404@.TK2MSFTNGP05.phx.gbl...
> New to SQL.
> I'm trying to include 2 dates in a query. They query works, but not
> logically. I'm sure I'm missing something small.
> When the query runs, it doesn't include dates that have times later than
> 12:00:00AM. How do I get rid of that?
> SELECT dbo.Orders.OrderID, dbo.Orders.CustomerID,
> dbo.Orders.OrderDate, dbo.Products.ProductName
> FROM dbo.Orders INNER JOIN
> dbo.[Order Details] ON dbo.Orders.OrderID =
> dbo.[Order Details].OrderID INNER JOIN
> dbo.Products ON dbo.[Order Details].ProductID =
> dbo.Products.ProductID
> WHERE (dbo.Orders.OrderDate >= @.ordDate) AND (dbo.Orders.OrderDate <=
> @.ordDate2)
>|||>SET @.ordDate2 '20060531 23:59:59'
To be strict about it there is a very small chance this will not work
because it ignores the milliseconds. But you can not simply add .999
to the end. Consider this query and results:
select 990, convert(datetime,'20060531 23:59:59.990') UNION ALL
select 991, convert(datetime,'20060531 23:59:59.991') UNION ALL
select 992, convert(datetime,'20060531 23:59:59.992') UNION ALL
select 993, convert(datetime,'20060531 23:59:59.993') UNION ALL
select 994, convert(datetime,'20060531 23:59:59.994') UNION ALL
select 995, convert(datetime,'20060531 23:59:59.995') UNION ALL
select 996, convert(datetime,'20060531 23:59:59.996') UNION ALL
select 997, convert(datetime,'20060531 23:59:59.997') UNION ALL
select 998, convert(datetime,'20060531 23:59:59.998') UNION ALL
select 999, convert(datetime,'20060531 23:59:59.999')
order by 1
-- --
990 2006-05-31 23:59:59.990
991 2006-05-31 23:59:59.990
992 2006-05-31 23:59:59.993
993 2006-05-31 23:59:59.993
994 2006-05-31 23:59:59.993
995 2006-05-31 23:59:59.997
996 2006-05-31 23:59:59.997
997 2006-05-31 23:59:59.997
998 2006-05-31 23:59:59.997
999 2006-06-01 00:00:00.000
As you can see, SQL Server doesn't quite work in milliseconds, and
23:59:59.999 resolves to the NEXT DAY. However, you have four choices
for the largest value for the same day, .995 through .998.
Roy Harvey
Beacon Falls, CT
On Wed, 31 May 2006 09:44:24 +0100, "Ryan"
<Ryan_Waight@.nospam.hotmail.com> wrote:
>This example should demonstrate.
>DECLARE @.ordDate datetime
>DECLARE @.ordDate2 datetime
>SET @.ordDate '20060531' -- As no time is specified this will be interpreted
>as '20060531 00:00'
>SET @.ordDate2 '20060531 23:59:59'
>To capture all data on the 31-May-2006 your WHERE clause would be :-
>WHERE dbo.Orders.OrderDate BETWEEN @.ordDate AND @.ordDate2|||Valid point, well made.
HTH. Ryan
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:v7vq72haq4c3a3npo7krbl1qceb4emk7mc@.
4ax.com...
> To be strict about it there is a very small chance this will not work
> because it ignores the milliseconds. But you can not simply add .999
> to the end. Consider this query and results:
> select 990, convert(datetime,'20060531 23:59:59.990') UNION ALL
> select 991, convert(datetime,'20060531 23:59:59.991') UNION ALL
> select 992, convert(datetime,'20060531 23:59:59.992') UNION ALL
> select 993, convert(datetime,'20060531 23:59:59.993') UNION ALL
> select 994, convert(datetime,'20060531 23:59:59.994') UNION ALL
> select 995, convert(datetime,'20060531 23:59:59.995') UNION ALL
> select 996, convert(datetime,'20060531 23:59:59.996') UNION ALL
> select 997, convert(datetime,'20060531 23:59:59.997') UNION ALL
> select 998, convert(datetime,'20060531 23:59:59.998') UNION ALL
> select 999, convert(datetime,'20060531 23:59:59.999')
> order by 1
> -- --
> 990 2006-05-31 23:59:59.990
> 991 2006-05-31 23:59:59.990
> 992 2006-05-31 23:59:59.993
> 993 2006-05-31 23:59:59.993
> 994 2006-05-31 23:59:59.993
> 995 2006-05-31 23:59:59.997
> 996 2006-05-31 23:59:59.997
> 997 2006-05-31 23:59:59.997
> 998 2006-05-31 23:59:59.997
> 999 2006-06-01 00:00:00.000
> As you can see, SQL Server doesn't quite work in milliseconds, and
> 23:59:59.999 resolves to the NEXT DAY. However, you have four choices
> for the largest value for the same day, .995 through .998.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 31 May 2006 09:44:24 +0100, "Ryan"
> <Ryan_Waight@.nospam.hotmail.com> wrote:
>|||All these things is the reason I wrote http://www.karaszi.com/SQLServer/in...
ime.asp
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eiAcoeKhGHA.896@.TK2MSFTNGP02.phx.gbl...
> Valid point, well made.
> --
> HTH. Ryan
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:v7vq72haq4c3a3npo7krbl1qceb4emk7mc@.
4ax.com...
>|||On Wed, 31 May 2006 13:54:22 +0200, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>All these things is the reason I wrote http://www.karaszi.com/SQLServer/in...br />
time.asp
>
>:-)
Very nice. I've got to catch up on all the articles you guys wrote
since I was last around!
Roy|||Thank you everybody. I'll look at the website and write back if I have
other issues.
Thank you everybody!
Tony
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:6t2s729hu1bdn521lbur5qea0ia4ct55a8@.
4ax.com...
> On Wed, 31 May 2006 13:54:22 +0200, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Very nice. I've got to catch up on all the articles you guys wrote
> since I was last around!
> Roy
Showing posts with label include. Show all posts
Showing posts with label include. Show all posts
Wednesday, March 21, 2012
Tuesday, March 20, 2012
Queried Parameter Null Values
Hello,
For queried parameters, I need to have the option of using a null value as well. Is there a way to include a null value, or do I have to include it in the query that is returned?
Thanks.
Use DBNull.Value in .NET as the parameter value.
If this does not help you, please clarify your question.
|||I have a parameter that pulls back data to it. But, this field should be optional, so the user should also be able to select null as another value. How do I allow the user to select null? I can't figure this out... By the way, I tried setting the default value to System.DBNull.Value, and that didn't work... nothing returns, but I did try the query and the query did work. For some reason, this is with RS...
|||What are you using to send the command to the database? An ObjectDataSource, custom ADO.NET, etc. If you can tell us what you're using, how the users are currently selecting values, etc, it would help.
|||I'm using a Reporting Services DataSet, standard report... The query works fine, and it has been tested. I can't figure out how to send a null value to the stored procedure parameter. That is what the problem is.
|||Hello,
Here is the answer that I've found: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1789337&SiteID=1
Monday, March 12, 2012
Qualify query with table name owner
All of the sudden when I do queries on my databases I have to include the ta
ble owner name
Before - Select * from people
After - Select * from carl.people
Don't know what changed... Haven't changed any table owners, config options
for the server or query tool.
Anyone know why?
Thanks,
CarlYou probably don't operate under the user name "carl", quite simply. Execute
SELECT SESSTION_USER and see what it returns.
Btw, it is a good practice to *always* owner-qualify in production code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Carl at pnm" <anonymous@.discussions.microsoft.com> wrote in message
news:EC65D713-D380-4A63-A367-E72CBB18FC6E@.microsoft.com...
> All of the sudden when I do queries on my databases I have to include the
table owner name
> Before - Select * from people
> After - Select * from carl.people
> Don't know what changed... Haven't changed any table owners, config
options for the server or query tool.
> Anyone know why?
> Thanks,
> Carl
ble owner name
Before - Select * from people
After - Select * from carl.people
Don't know what changed... Haven't changed any table owners, config options
for the server or query tool.
Anyone know why?
Thanks,
CarlYou probably don't operate under the user name "carl", quite simply. Execute
SELECT SESSTION_USER and see what it returns.
Btw, it is a good practice to *always* owner-qualify in production code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Carl at pnm" <anonymous@.discussions.microsoft.com> wrote in message
news:EC65D713-D380-4A63-A367-E72CBB18FC6E@.microsoft.com...
> All of the sudden when I do queries on my databases I have to include the
table owner name
> Before - Select * from people
> After - Select * from carl.people
> Don't know what changed... Haven't changed any table owners, config
options for the server or query tool.
> Anyone know why?
> Thanks,
> Carl
Qualifier in URL Access not working
Hi,
When I export to CSV using URL Access, it doesn't include the default Qualifier ("). Even when I tried to use Qualifier=" or some other value, it doesn't include the qualifier in the output CSV file.
Any idea?
Thanks in advance.
Mouli
What version of Reporting Services are you using?
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.|||
You probably need to add rc: before Qualifier to make it work.
Your URL would look something like:
/reportserver/?/itempath&rs:Command=render&rs:Format=CSV&rc:Qualifier" href="http://links.10026.com/?link=http://
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
Hi,
I'm trying to get the "-text qualifier to work as well in CSV exports, but without any luck. Are there any other conditions which should be met for text columns to get exported within quotes?
Regards, Jeroen
|||What does the URL you're specifying look like?
-Lukasz
Qualifier in URL Access not working
Hi,
When I export to CSV using URL Access, it doesn't include the default Qualifier ("). Even when I tried to use Qualifier=" or some other value, it doesn't include the qualifier in the output CSV file.
Any idea?
Thanks in advance.
Mouli
What version of Reporting Services are you using?
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.|||
You probably need to add rc: before Qualifier to make it work.
Your URL would look something like:
/reportserver/?/itempath&rs:Command=render&rs:Format=CSV&rc:Qualifier" href="http://links.10026.com/?link=http://
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
Hi,
I'm trying to get the "-text qualifier to work as well in CSV exports, but without any luck. Are there any other conditions which should be met for text columns to get exported within quotes?
Regards, Jeroen
|||What does the URL you're specifying look like?
-Lukasz
Qualifier in URL Access not working
Hi,
When I export to CSV using URL Access, it doesn't include the default Qualifier ("). Even when I tried to use Qualifier=" or some other value, it doesn't include the qualifier in the output CSV file.
Any idea?
Thanks in advance.
Mouli
What version of Reporting Services are you using?
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.|||
You probably need to add rc: before Qualifier to make it work.
Your URL would look something like:
/reportserver/?/itempath&rs:Command=render&rs:Format=CSV&rc:Qualifier" href="http://links.10026.com/?link=http://
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
Hi,
I'm trying to get the "-text qualifier to work as well in CSV exports, but without any luck. Are there any other conditions which should be met for text columns to get exported within quotes?
Regards, Jeroen
|||What does the URL you're specifying look like?
-Lukasz
Qualifier in URL Access not working
Hi,
When I export to CSV using URL Access, it doesn't include the default Qualifier ("). Even when I tried to use Qualifier=" or some other value, it doesn't include the qualifier in the output CSV file.
Any idea?
Thanks in advance.
Mouli
What version of Reporting Services are you using?
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.|||
You probably need to add rc: before Qualifier to make it work.
Your URL would look something like:
/reportserver/?/itempath&rs:Command=render&rs:Format=CSV&rc:Qualifier" href="http://links.10026.com/?link=http://
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
Hi,
I'm trying to get the "-text qualifier to work as well in CSV exports, but without any luck. Are there any other conditions which should be met for text columns to get exported within quotes?
Regards, Jeroen
|||What does the URL you're specifying look like?
-Lukasz
Subscribe to:
Posts (Atom)