I know a sql stored procedure can return >1 tables. How can I use .Net 2.0 to read these tables one at a time, for example the first one could iterate Forum entries and the second one all internal links used in these forums... The idea is to use fewer backtrips to the sql server?
Isthis article of any use?|||no fortunately not :(
Showing posts with label return. Show all posts
Showing posts with label return. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
Bind many tables from a single sp on many tables on a single repor
Hi,
i've a single stored procedure that return many tables from different select
queries. it is possible to bind on a single report all "recordset" on
different tables? in other words how i can "navigate" the source dataset, is
possible to refer to a kind of dataset index? ex. dsname[1], dsname[2] etc?RS does not support this. You either need multiple stored procedures or you
need to pass a parameter to the sp that says which recordset you want. Note
that either way you will have to call a stored procedure per dataset. It is
a one to one relationship.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"luspo" <luspo@.discussions.microsoft.com> wrote in message
news:CC7325EC-29A7-43F7-B857-4F0F0CFF1E67@.microsoft.com...
> Hi,
> i've a single stored procedure that return many tables from different
select
> queries. it is possible to bind on a single report all "recordset" on
> different tables? in other words how i can "navigate" the source dataset,
is
> possible to refer to a kind of dataset index? ex. dsname[1], dsname[2]
etc?
i've a single stored procedure that return many tables from different select
queries. it is possible to bind on a single report all "recordset" on
different tables? in other words how i can "navigate" the source dataset, is
possible to refer to a kind of dataset index? ex. dsname[1], dsname[2] etc?RS does not support this. You either need multiple stored procedures or you
need to pass a parameter to the sp that says which recordset you want. Note
that either way you will have to call a stored procedure per dataset. It is
a one to one relationship.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"luspo" <luspo@.discussions.microsoft.com> wrote in message
news:CC7325EC-29A7-43F7-B857-4F0F0CFF1E67@.microsoft.com...
> Hi,
> i've a single stored procedure that return many tables from different
select
> queries. it is possible to bind on a single report all "recordset" on
> different tables? in other words how i can "navigate" the source dataset,
is
> possible to refer to a kind of dataset index? ex. dsname[1], dsname[2]
etc?
binary_checksum craziness
Folks, hi.
Any idea why this patently different items return the same binary_checksum value:
select
binary_checksum('X', convert(datetime,'2006-13-12'), convert(datetime,'2007-17-04'), convert(datetime, null),'X','X','X','X','X','S','X') [1],
binary_checksum('X', convert(datetime, '2006-28-11'), convert(datetime,'2007-17-04'), convert(datetime, null),'X','X','X','X','X','B','X') [2]
The core of a very major system is based on these computing unique values for a unique set of expressions!Because BINARY_CHECKSUM is not guaranteed to be unique. From BOL:
BINARY_CHECKSUM(*), computed on any row of a table, returns the same value as long the row is not subsequently modified. BINARY_CHECKSUM(*) will return a different value for most, but not all, changes to the row, and can be used to detect most row modifications.|||The core of a very major system is based on these computing unique values for a unique set of expressions!
Oops. Somebody should have done some research first, eh?|||Oops. Somebody should have done some research first, eh?
At the risk of overstepping my bounds: don't be an anacedent.
Regards,
hmscott|||Point taken...|||A most helpful forum indeed it has to be said.|||Here is how CHECKSUM is calculated. You can see for yourself how Microsoft do and why many checksums are the same.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70832|||Thanks Peso.sql
Any idea why this patently different items return the same binary_checksum value:
select
binary_checksum('X', convert(datetime,'2006-13-12'), convert(datetime,'2007-17-04'), convert(datetime, null),'X','X','X','X','X','S','X') [1],
binary_checksum('X', convert(datetime, '2006-28-11'), convert(datetime,'2007-17-04'), convert(datetime, null),'X','X','X','X','X','B','X') [2]
The core of a very major system is based on these computing unique values for a unique set of expressions!Because BINARY_CHECKSUM is not guaranteed to be unique. From BOL:
BINARY_CHECKSUM(*), computed on any row of a table, returns the same value as long the row is not subsequently modified. BINARY_CHECKSUM(*) will return a different value for most, but not all, changes to the row, and can be used to detect most row modifications.|||The core of a very major system is based on these computing unique values for a unique set of expressions!
Oops. Somebody should have done some research first, eh?|||Oops. Somebody should have done some research first, eh?
At the risk of overstepping my bounds: don't be an anacedent.
Regards,
hmscott|||Point taken...|||A most helpful forum indeed it has to be said.|||Here is how CHECKSUM is calculated. You can see for yourself how Microsoft do and why many checksums are the same.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70832|||Thanks Peso.sql
Thursday, March 22, 2012
Binary Column always returns 0 for ISNULL
Why don't i ever get return value of 1 when the following binary column (profSignature) is null?
RETURN SELECT ISNULL
(profSignature, profSignature)FROMmpProfilesWHEREApplicantID =CAST(@.CIDAS INT)ANDProfileID =CAST(@.PIDAS INT)There is no conversion between GUID and INT that is what I think you are trying to do, try the link below for SQL Server data type conversion chart. Hope this helps.
http://msdn2.microsoft.com/en-us/library/ms187928.aspx
|||I forgot to add that the column type is "Image" in SQL 2005|||Dup post
|||How would I get it so a <NULL> column value makes the stored procedure return 1 or 0?|||I have checked the conversion chart again that is not valid either so check the create table statement below from Microsoft AdventureWorks it pick the columns relevant to your situation and you can get the information you are looking for. Sorry about the duplicate post. Hope this helps.
CREATE TABLE [EmployeePhoto] (
[EmployeePhotoID] [int] IDENTITY (1, 1) NOT NULL ,
[ThumbNailPhoto] [image] NULL ,
[ThumbnailPhotoFileName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LargePhoto] [image] NULL ,
[LargePhotoFileName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_EmployeePhoto_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_EmployeePhoto_EmployeePhotoID] PRIMARY KEY CLUSTERED
(
[EmployeePhotoID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
If you need 1 on null try this:
RETURN SELECT ISNULL(profSignature,1)
FROMmpProfiles
WHEREApplicantID =CAST(@.CIDAS INT)
ANDProfileID =CAST(@.PIDAS INT)
or
RETURN (case
when exist(Select * FROMmpProfiles
WHEREApplicantID =CAST(@.CIDAS INT)
ANDProfileID =CAST(@.PIDAS INT)
and profSignature is null) then 1 -- for null
when exist(Select * FROMmpProfiles
WHEREApplicantID =CAST(@.CIDAS INT)
ANDProfileID =CAST(@.PIDAS INT)
and not profSignature is null) then 2 -- for not nulls
else
0 -- does not exists in database
end)
|||Thanks ... the case statement did the trick.sqlTuesday, March 20, 2012
Big queries return empty result set
Hello guys,
MS SQL server 2000 behavies strange with big queries that involves relatively large number of tables:
If I just enumerate columns I want to receive, the result set is empty. Adding * to the column list without making any change to where clause seems to solve the problem but I guess it's not the best practice.
The most amazing is that this behavior is not stable, so I suppose there's something to deal with server itself, not the application.
Has anybody suffered this problem and what solution was adopted?
Thanks for any information you can provide.Most of my queries are small (less than 200 columns and 500,000 rows), so maybe I'm not hitting the kind of volume that you are, but I've never heard of that behavior! I also rarely push beyond 30-50 tables in a single query with the exception of one purchased package that sometimes can create monsters.
Can you give me a rough idea of how big your database is (in gigabytes) and how big your result set is (rows, columns, and megabytes)? I'll try to reproduce the behavior here with a known good server.
-PatP|||Definitely my queries never get this volume. I have a join of about 15 tables with 10 columns in each. Empty recordset is an ocasional behavior, i mean today the query may work perfectly, tomorrow absolutely exact query with absolutely exact parameters without any modification made to the database fails.
I was told that this could be caused by particular, localized version of SQL server that we use (sql server 2000 SP3 spanish, 8.00.760). Could you confirm that?
Originally posted by Pat Phelan
Most of my queries are small (less than 200 columns and 500,000 rows), so maybe I'm not hitting the kind of volume that you are, but I've never heard of that behavior! I also rarely push beyond 30-50 tables in a single query with the exception of one purchased package that sometimes can create monsters.
Can you give me a rough idea of how big your database is (in gigabytes) and how big your result set is (rows, columns, and megabytes)? I'll try to reproduce the behavior here with a known good server.
-PatP|||Originally posted by Pat Phelan
Most of my queries are small (less than 200 columns and 500,000 rows), so maybe I'm not hitting the kind of volume that you are, but I've never heard of that behavior! I also rarely push beyond 30-50 tables in a single query with the exception of one purchased package that sometimes can create monsters.
:D
Why not POST the query...|||Originally posted by Brett Kaiser
:D
Why not POST the query...
As you wish:
--NON-EMPTY RESULTSET--
select bb.i_object_id as bb_aa, bb.i_object_type_id as bb_ab,
<skipped about 50 columns>
bl.i_operation_id as bl_ak, bl.integra_operation_id as bl_al,
* <-- this allows me receive data
from i_sysobject bb, part_list_item bc,
i_relation bd,
i_sysobject be, part_list bf,
i_relation bg, i_sysobject bh,
production_order bi, i_relation bj,
i_sysobject bk, operation bl
where bc.i_part_list_item_id = bb.i_object_id and
bf.state = ? and
bf.i_part_list_id = be.i_object_id and
bi.i_production_order_id = bh.i_object_id and
bl.i_operation_id = bk.i_object_id and
bc.i_part_list_item_id=bd.i_relation_child_object_ id and
bd.i_relation_parent_object_id=bf.i_part_list_id and
bf.i_part_list_id=bg.i_relation_child_object_id and
bg.i_relation_parent_object_id=bi.i_production_ord er_id and
bf.i_part_list_id=bj.i_relation_child_object_id and
bj.i_relation_parent_object_id=bl.i_operation_id
order by bf.i_part_list_id asc
--EMPTY RESULTSET--
select bb.i_object_id as bb_aa, bb.i_object_type_id as bb_ab,
<skipped about 50 columns>
bl.i_operation_id as bl_ak, bl.integra_operation_id as bl_al
from i_sysobject bb, part_list_item bc,
i_relation bd,
i_sysobject be, part_list bf,
i_relation bg, i_sysobject bh,
production_order bi, i_relation bj,
i_sysobject bk, operation bl
where bc.i_part_list_item_id = bb.i_object_id and
bf.state = ? and
bf.i_part_list_id = be.i_object_id and
bi.i_production_order_id = bh.i_object_id and
bl.i_operation_id = bk.i_object_id and
bc.i_part_list_item_id=bd.i_relation_child_object_ id and
bd.i_relation_parent_object_id=bf.i_part_list_id and
bf.i_part_list_id=bg.i_relation_child_object_id and
bg.i_relation_parent_object_id=bi.i_production_ord er_id and
bf.i_part_list_id=bj.i_relation_child_object_id and
bj.i_relation_parent_object_id=bl.i_operation_id
order by bf.i_part_list_id asc
bf.state = ? receives the SAME parameter in both cases.|||Everything is identity, isn't it...
Is one of these tables a driver? Like what you want to base your result set on?
Lots of sele referencing...
Maybe you can use derived tables..
SELECT * FROM (SELECT * FROM ...join the table that relate) AS A
LEFT JOIN (SELECT * FROM ..same thing) AS B
ON A.key = B.Key
Here's your code cleaned up some..Not sure if it's doing the same thing...
I think it is...and easier to see what you're trying to do
SELECT *
FROM i_sysobject bb
LEFT JOIN part_list_item bc ON bc.i_part_list_item_id = bb.i_object_id
LEFT JOIN i_relation bd ON bc.i_part_list_item_id=bd.i_relation_child_object_ id
LEFT JOIN i_sysobject be ON bf.i_part_list_id = be.i_object_id and
LEFT JOIN part_list bf ON bd.i_relation_parent_object_id=bf.i_part_list_id
LEFT JOIN i_relation bg ON bf.i_part_list_id=bg.i_relation_child_object_id
LEFT JOIN i_sysobject bh ON bi.i_production_order_id = bh.i_object_id
LEFT JOIN production_order bi ON bg.i_relation_parent_object_id=bi.i_production_ord er_id
LEFT JOIN i_relation bj ON bf.i_part_list_id=bj.i_relation_child_object_id
LEFT JOIN i_sysobject bk ON bl.i_operation_id = bk.i_object_id
LEFT JOIN operation bl ON bj.i_relation_parent_object_id=bl.i_operation_id
WHERE bf.state = ?
MS SQL server 2000 behavies strange with big queries that involves relatively large number of tables:
If I just enumerate columns I want to receive, the result set is empty. Adding * to the column list without making any change to where clause seems to solve the problem but I guess it's not the best practice.
The most amazing is that this behavior is not stable, so I suppose there's something to deal with server itself, not the application.
Has anybody suffered this problem and what solution was adopted?
Thanks for any information you can provide.Most of my queries are small (less than 200 columns and 500,000 rows), so maybe I'm not hitting the kind of volume that you are, but I've never heard of that behavior! I also rarely push beyond 30-50 tables in a single query with the exception of one purchased package that sometimes can create monsters.
Can you give me a rough idea of how big your database is (in gigabytes) and how big your result set is (rows, columns, and megabytes)? I'll try to reproduce the behavior here with a known good server.
-PatP|||Definitely my queries never get this volume. I have a join of about 15 tables with 10 columns in each. Empty recordset is an ocasional behavior, i mean today the query may work perfectly, tomorrow absolutely exact query with absolutely exact parameters without any modification made to the database fails.
I was told that this could be caused by particular, localized version of SQL server that we use (sql server 2000 SP3 spanish, 8.00.760). Could you confirm that?
Originally posted by Pat Phelan
Most of my queries are small (less than 200 columns and 500,000 rows), so maybe I'm not hitting the kind of volume that you are, but I've never heard of that behavior! I also rarely push beyond 30-50 tables in a single query with the exception of one purchased package that sometimes can create monsters.
Can you give me a rough idea of how big your database is (in gigabytes) and how big your result set is (rows, columns, and megabytes)? I'll try to reproduce the behavior here with a known good server.
-PatP|||Originally posted by Pat Phelan
Most of my queries are small (less than 200 columns and 500,000 rows), so maybe I'm not hitting the kind of volume that you are, but I've never heard of that behavior! I also rarely push beyond 30-50 tables in a single query with the exception of one purchased package that sometimes can create monsters.
:D
Why not POST the query...|||Originally posted by Brett Kaiser
:D
Why not POST the query...
As you wish:
--NON-EMPTY RESULTSET--
select bb.i_object_id as bb_aa, bb.i_object_type_id as bb_ab,
<skipped about 50 columns>
bl.i_operation_id as bl_ak, bl.integra_operation_id as bl_al,
* <-- this allows me receive data
from i_sysobject bb, part_list_item bc,
i_relation bd,
i_sysobject be, part_list bf,
i_relation bg, i_sysobject bh,
production_order bi, i_relation bj,
i_sysobject bk, operation bl
where bc.i_part_list_item_id = bb.i_object_id and
bf.state = ? and
bf.i_part_list_id = be.i_object_id and
bi.i_production_order_id = bh.i_object_id and
bl.i_operation_id = bk.i_object_id and
bc.i_part_list_item_id=bd.i_relation_child_object_ id and
bd.i_relation_parent_object_id=bf.i_part_list_id and
bf.i_part_list_id=bg.i_relation_child_object_id and
bg.i_relation_parent_object_id=bi.i_production_ord er_id and
bf.i_part_list_id=bj.i_relation_child_object_id and
bj.i_relation_parent_object_id=bl.i_operation_id
order by bf.i_part_list_id asc
--EMPTY RESULTSET--
select bb.i_object_id as bb_aa, bb.i_object_type_id as bb_ab,
<skipped about 50 columns>
bl.i_operation_id as bl_ak, bl.integra_operation_id as bl_al
from i_sysobject bb, part_list_item bc,
i_relation bd,
i_sysobject be, part_list bf,
i_relation bg, i_sysobject bh,
production_order bi, i_relation bj,
i_sysobject bk, operation bl
where bc.i_part_list_item_id = bb.i_object_id and
bf.state = ? and
bf.i_part_list_id = be.i_object_id and
bi.i_production_order_id = bh.i_object_id and
bl.i_operation_id = bk.i_object_id and
bc.i_part_list_item_id=bd.i_relation_child_object_ id and
bd.i_relation_parent_object_id=bf.i_part_list_id and
bf.i_part_list_id=bg.i_relation_child_object_id and
bg.i_relation_parent_object_id=bi.i_production_ord er_id and
bf.i_part_list_id=bj.i_relation_child_object_id and
bj.i_relation_parent_object_id=bl.i_operation_id
order by bf.i_part_list_id asc
bf.state = ? receives the SAME parameter in both cases.|||Everything is identity, isn't it...
Is one of these tables a driver? Like what you want to base your result set on?
Lots of sele referencing...
Maybe you can use derived tables..
SELECT * FROM (SELECT * FROM ...join the table that relate) AS A
LEFT JOIN (SELECT * FROM ..same thing) AS B
ON A.key = B.Key
Here's your code cleaned up some..Not sure if it's doing the same thing...
I think it is...and easier to see what you're trying to do
SELECT *
FROM i_sysobject bb
LEFT JOIN part_list_item bc ON bc.i_part_list_item_id = bb.i_object_id
LEFT JOIN i_relation bd ON bc.i_part_list_item_id=bd.i_relation_child_object_ id
LEFT JOIN i_sysobject be ON bf.i_part_list_id = be.i_object_id and
LEFT JOIN part_list bf ON bd.i_relation_parent_object_id=bf.i_part_list_id
LEFT JOIN i_relation bg ON bf.i_part_list_id=bg.i_relation_child_object_id
LEFT JOIN i_sysobject bh ON bi.i_production_order_id = bh.i_object_id
LEFT JOIN production_order bi ON bg.i_relation_parent_object_id=bi.i_production_ord er_id
LEFT JOIN i_relation bj ON bf.i_part_list_id=bj.i_relation_child_object_id
LEFT JOIN i_sysobject bk ON bl.i_operation_id = bk.i_object_id
LEFT JOIN operation bl ON bj.i_relation_parent_object_id=bl.i_operation_id
WHERE bf.state = ?
Wednesday, March 7, 2012
BETWEEN, when given a date for a datetime field
BETWEEN '20070601' AND '20070630'
... does this return anything between
20070601 00:00:00 to 20070630 23:59:59
or only between
20070601 00:00:00 to 20070630 00:00:00The latter. You should use
WHERE (col >= '20070601' AND col < '20070101')
--
Aaron Bertrand
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegroups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>|||Neither. Between is inclusive. So for below...
BETWEEN '20070601' AND '20070630'
The strings will be converted to datetime values (thanks to implicit datatype conversion):
20070601 00:00:00 to 20070701 00:00:00
Not what you want, so don't use BETWEEN unless you guarantee that all stored datetime values has a
time portion of 00:00:00.000. I've devoted a section about this in:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegroups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>|||> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
BETWEEN is inclusive and the datetime SQL Server data type includes time.
The predicate:
WHERE
MyDate BETWEEN '20070601' AND '20070630'
is functionally identical to:
WHERE
MyDate >= '20070601 00:00:00' AND
MyDate <= '20070630 00:00:00'
If your objective is to include all dates within a given month, I suggest
avoiding BETWEEN. Instead, specify an exclusive operator and end date
value:
WHERE
MyDate >= '20070601' AND
MyDate < '20070701'
Hope this helps.
Dan Guzman
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegroups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>|||> BETWEEN '20070601' AND '20070630'
> The strings will be converted to datetime values (thanks to implicit
> datatype conversion):
> 20070601 00:00:00 to 20070701 00:00:00
Why would '20070630' be converted to '20070701 00:00:00'?
--
Aaron Bertrand
SQL Server MVP|||> Why would '20070630' be converted to '20070701 00:00:00'?
LOL. Good point Aaron. It won't, of course. I got stuck in the thinking that "June" was what
metaperl wanted, and for some reason this was transferred to me writing that incorrect value. Thanks
for catching it. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8H2oK4zHHA.1184@.TK2MSFTNGP04.phx.gbl...
>> BETWEEN '20070601' AND '20070630'
>> The strings will be converted to datetime values (thanks to implicit datatype conversion):
>> 20070601 00:00:00 to 20070701 00:00:00
> Why would '20070630' be converted to '20070701 00:00:00'?
> --
> Aaron Bertrand
> SQL Server MVP
>
>
... does this return anything between
20070601 00:00:00 to 20070630 23:59:59
or only between
20070601 00:00:00 to 20070630 00:00:00The latter. You should use
WHERE (col >= '20070601' AND col < '20070101')
--
Aaron Bertrand
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegroups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>|||Neither. Between is inclusive. So for below...
BETWEEN '20070601' AND '20070630'
The strings will be converted to datetime values (thanks to implicit datatype conversion):
20070601 00:00:00 to 20070701 00:00:00
Not what you want, so don't use BETWEEN unless you guarantee that all stored datetime values has a
time portion of 00:00:00.000. I've devoted a section about this in:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegroups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>|||> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
BETWEEN is inclusive and the datetime SQL Server data type includes time.
The predicate:
WHERE
MyDate BETWEEN '20070601' AND '20070630'
is functionally identical to:
WHERE
MyDate >= '20070601 00:00:00' AND
MyDate <= '20070630 00:00:00'
If your objective is to include all dates within a given month, I suggest
avoiding BETWEEN. Instead, specify an exclusive operator and end date
value:
WHERE
MyDate >= '20070601' AND
MyDate < '20070701'
Hope this helps.
Dan Guzman
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegroups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>|||> BETWEEN '20070601' AND '20070630'
> The strings will be converted to datetime values (thanks to implicit
> datatype conversion):
> 20070601 00:00:00 to 20070701 00:00:00
Why would '20070630' be converted to '20070701 00:00:00'?
--
Aaron Bertrand
SQL Server MVP|||> Why would '20070630' be converted to '20070701 00:00:00'?
LOL. Good point Aaron. It won't, of course. I got stuck in the thinking that "June" was what
metaperl wanted, and for some reason this was transferred to me writing that incorrect value. Thanks
for catching it. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8H2oK4zHHA.1184@.TK2MSFTNGP04.phx.gbl...
>> BETWEEN '20070601' AND '20070630'
>> The strings will be converted to datetime values (thanks to implicit datatype conversion):
>> 20070601 00:00:00 to 20070701 00:00:00
> Why would '20070630' be converted to '20070701 00:00:00'?
> --
> Aaron Bertrand
> SQL Server MVP
>
>
BETWEEN, when given a date for a datetime field
BETWEEN '20070601' AND '20070630'
... does this return anything between
20070601 00:00:00 to 20070630 23:59:59
or only between
20070601 00:00:00 to 20070630 00:00:00
The latter. You should use
WHERE (col >= '20070601' AND col < '20070101')
Aaron Bertrand
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegr oups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>
|||Neither. Between is inclusive. So for below...
BETWEEN '20070601' AND '20070630'
The strings will be converted to datetime values (thanks to implicit datatype conversion):
20070601 00:00:00 to 20070701 00:00:00
Not what you want, so don't use BETWEEN unless you guarantee that all stored datetime values has a
time portion of 00:00:00.000. I've devoted a section about this in:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegr oups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>
|||> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
BETWEEN is inclusive and the datetime SQL Server data type includes time.
The predicate:
WHERE
MyDate BETWEEN '20070601' AND '20070630'
is functionally identical to:
WHERE
MyDate >= '20070601 00:00:00' AND
MyDate <= '20070630 00:00:00'
If your objective is to include all dates within a given month, I suggest
avoiding BETWEEN. Instead, specify an exclusive operator and end date
value:
WHERE
MyDate >= '20070601' AND
MyDate < '20070701'
Hope this helps.
Dan Guzman
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegr oups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>
|||> BETWEEN '20070601' AND '20070630'
> The strings will be converted to datetime values (thanks to implicit
> datatype conversion):
> 20070601 00:00:00 to 20070701 00:00:00
Why would '20070630' be converted to '20070701 00:00:00'?
Aaron Bertrand
SQL Server MVP
|||> Why would '20070630' be converted to '20070701 00:00:00'?
LOL. Good point Aaron. It won't, of course. I got stuck in the thinking that "June" was what
metaperl wanted, and for some reason this was transferred to me writing that incorrect value. Thanks
for catching it. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8H2oK4zHHA.1184@.TK2MSFTNGP04.phx.gbl...
> Why would '20070630' be converted to '20070701 00:00:00'?
> --
> Aaron Bertrand
> SQL Server MVP
>
>
... does this return anything between
20070601 00:00:00 to 20070630 23:59:59
or only between
20070601 00:00:00 to 20070630 00:00:00
The latter. You should use
WHERE (col >= '20070601' AND col < '20070101')
Aaron Bertrand
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegr oups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>
|||Neither. Between is inclusive. So for below...
BETWEEN '20070601' AND '20070630'
The strings will be converted to datetime values (thanks to implicit datatype conversion):
20070601 00:00:00 to 20070701 00:00:00
Not what you want, so don't use BETWEEN unless you guarantee that all stored datetime values has a
time portion of 00:00:00.000. I've devoted a section about this in:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegr oups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>
|||> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
BETWEEN is inclusive and the datetime SQL Server data type includes time.
The predicate:
WHERE
MyDate BETWEEN '20070601' AND '20070630'
is functionally identical to:
WHERE
MyDate >= '20070601 00:00:00' AND
MyDate <= '20070630 00:00:00'
If your objective is to include all dates within a given month, I suggest
avoiding BETWEEN. Instead, specify an exclusive operator and end date
value:
WHERE
MyDate >= '20070601' AND
MyDate < '20070701'
Hope this helps.
Dan Guzman
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegr oups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>
|||> BETWEEN '20070601' AND '20070630'
> The strings will be converted to datetime values (thanks to implicit
> datatype conversion):
> 20070601 00:00:00 to 20070701 00:00:00
Why would '20070630' be converted to '20070701 00:00:00'?
Aaron Bertrand
SQL Server MVP
|||> Why would '20070630' be converted to '20070701 00:00:00'?
LOL. Good point Aaron. It won't, of course. I got stuck in the thinking that "June" was what
metaperl wanted, and for some reason this was transferred to me writing that incorrect value. Thanks
for catching it. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8H2oK4zHHA.1184@.TK2MSFTNGP04.phx.gbl...
> Why would '20070630' be converted to '20070701 00:00:00'?
> --
> Aaron Bertrand
> SQL Server MVP
>
>
BETWEEN, when given a date for a datetime field
BETWEEN '20070601' AND '20070630'
... does this return anything between
20070601 00:00:00 to 20070630 23:59:59
or only between
20070601 00:00:00 to 20070630 00:00:00The latter. You should use
WHERE (col >= '20070601' AND col < '20070101')
Aaron Bertrand
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegroups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>|||Neither. Between is inclusive. So for below...
BETWEEN '20070601' AND '20070630'
The strings will be converted to datetime values (thanks to implicit datatyp
e conversion):
20070601 00:00:00 to 20070701 00:00:00
Not what you want, so don't use BETWEEN unless you guarantee that all stored
datetime values has a
time portion of 00:00:00.000. I've devoted a section about this in:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegroups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>|||> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
BETWEEN is inclusive and the datetime SQL Server data type includes time.
The predicate:
WHERE
MyDate BETWEEN '20070601' AND '20070630'
is functionally identical to:
WHERE
MyDate >= '20070601 00:00:00' AND
MyDate <= '20070630 00:00:00'
If your objective is to include all dates within a given month, I suggest
avoiding BETWEEN. Instead, specify an exclusive operator and end date
value:
WHERE
MyDate >= '20070601' AND
MyDate < '20070701'
Hope this helps.
Dan Guzman
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegroups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>|||> BETWEEN '20070601' AND '20070630'
> The strings will be converted to datetime values (thanks to implicit
> datatype conversion):
> 20070601 00:00:00 to 20070701 00:00:00
Why would '20070630' be converted to '20070701 00:00:00'?
Aaron Bertrand
SQL Server MVP|||> Why would '20070630' be converted to '20070701 00:00:00'?
LOL. Good point Aaron. It won't, of course. I got stuck in the thinking that
"June" was what
metaperl wanted, and for some reason this was transferred to me writing that
incorrect value. Thanks
for catching it. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:u8H2oK4zHHA.1184@.TK2MSFTNGP04.phx.gbl...
> Why would '20070630' be converted to '20070701 00:00:00'?
> --
> Aaron Bertrand
> SQL Server MVP
>
>
... does this return anything between
20070601 00:00:00 to 20070630 23:59:59
or only between
20070601 00:00:00 to 20070630 00:00:00The latter. You should use
WHERE (col >= '20070601' AND col < '20070101')
Aaron Bertrand
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegroups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>|||Neither. Between is inclusive. So for below...
BETWEEN '20070601' AND '20070630'
The strings will be converted to datetime values (thanks to implicit datatyp
e conversion):
20070601 00:00:00 to 20070701 00:00:00
Not what you want, so don't use BETWEEN unless you guarantee that all stored
datetime values has a
time portion of 00:00:00.000. I've devoted a section about this in:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegroups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>|||> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
BETWEEN is inclusive and the datetime SQL Server data type includes time.
The predicate:
WHERE
MyDate BETWEEN '20070601' AND '20070630'
is functionally identical to:
WHERE
MyDate >= '20070601 00:00:00' AND
MyDate <= '20070630 00:00:00'
If your objective is to include all dates within a given month, I suggest
avoiding BETWEEN. Instead, specify an exclusive operator and end date
value:
WHERE
MyDate >= '20070601' AND
MyDate < '20070701'
Hope this helps.
Dan Guzman
SQL Server MVP
"metaperl" <metaperl@.gmail.com> wrote in message
news:1185451883.758176.310860@.o61g2000hsh.googlegroups.com...
> BETWEEN '20070601' AND '20070630'
> ... does this return anything between
> 20070601 00:00:00 to 20070630 23:59:59
> or only between
> 20070601 00:00:00 to 20070630 00:00:00
>|||> BETWEEN '20070601' AND '20070630'
> The strings will be converted to datetime values (thanks to implicit
> datatype conversion):
> 20070601 00:00:00 to 20070701 00:00:00
Why would '20070630' be converted to '20070701 00:00:00'?
Aaron Bertrand
SQL Server MVP|||> Why would '20070630' be converted to '20070701 00:00:00'?
LOL. Good point Aaron. It won't, of course. I got stuck in the thinking that
"June" was what
metaperl wanted, and for some reason this was transferred to me writing that
incorrect value. Thanks
for catching it. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:u8H2oK4zHHA.1184@.TK2MSFTNGP04.phx.gbl...
> Why would '20070630' be converted to '20070701 00:00:00'?
> --
> Aaron Bertrand
> SQL Server MVP
>
>
Saturday, February 25, 2012
BETWEEN 20/5/2007 AND 30/5/2007 not returning rows.
Hello,
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
SQL Server 2005
I have tried and cannot return any rows either though there are records that should be returned, using the sql below.
Code Snippet
SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') AND scheduleDate BETWEEN 20/5/2007 AND 30/5/2007
In the database scheduleDate is a dateTime datatype.
In the database I have copied and pasted. 23/05/2007 00:00:00
I tried the following
Code Snippet
SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') AND scheduleDate BETWEEN '20/5/2007' AND '30/5/2007'
And got an error message:The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Many thanks for any help with this,
Steve
First approach doesn't work because 20/5/2007 interpreted as arithmetical expression with value 0. Then with value converted to datetime as 1/1/1901. Try following code:
Code Snippet
declare @.dt datetime
set @.dt = 20/5/2007
select @.dt
You could rewrite second query with explicit convert:
Code Snippet
SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited')
AND scheduleDate BETWEEN convert('20/5/2007',104) AND convert('30/5/2007',104)
Or use
Code Snippet
SET DATEFORMAT dmy
for setting default dateformat
|||That is because the implicit conversion thinks the date is a US date.Do this:
.....BETWEEN CONVERT(datetime,'20/5/2007',103) AND CONVERT(datetime,'30/5/2007',103)
or
SET DATEFORMAT dmy
SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') AND scheduleDate BETWEEN '20/5/2007' AND '30/5/2007'
|||
steve_1_rm,
Try using the ISO or ISO8601 format (see function CONVERT in BOL) and SQL Server will interprete correctly the datetime string, no matter the language or setting of dateformat.
SELECT *
FROM Calls
WHERE
clientName IN ('Bankside Engineering Limited')
AND scheduleDate BETWEEN '20070527' AND '20070530'
AMB
|||Hello, Thanks for your, help. but the code above did not return the rows i wanted.I have used the following and with yyyymmdd format SELECT * FROM Calls WHERE [Date] BETWEEN '20070521' AND '20070531' This is the actual value i have in the database and this is what i am trying to get out. 23/05/2007 00:00:00 Many thanks Steve|||What do you get when you issue
Code Snippet
SELECT CONVERT(varchar(10), [Date], 103) as [Date], *
FROM Calls
WHERE [Date] BETWEEN '20070521' AND '20070531'
Sunday, February 19, 2012
Best way to send DB results to variable
Hello all
I have a database query which will return a single value
SELECT COUNT(DISTINCT(Level_id)) FROM RoleSkill WHERE Route_ID = 1
obviously this will return only a single value and not an array of data like most database queries, therefore i would like it to be sent directly to an integer variable.
I tried doing this directly with a sqlcommand but get error that it cannot be converted to integer. Is there a good way of solving this problem?
thanks for your help
TomExecuteScalar|||Aha right you are!
thanks very much i expected the process to be more complex, i had forgotten there are 3 options for a command object.
cheers ...Tom|||Output parameters
Best way to search
I have a stored procedure declared (shown below) The intent of the stored
proc is to return all records where the field values match the criteria
specified in the stored proc parameters. I want to specify some or all of
the parameter values. What I have written works, but I don't think it is
very efficient, any ideas?
CREATE PROCEDURE dbo.pSearch
@.strFirstName varchar(50) = NULL,
@.strLastName varchar(50) = NULL, @.iDay int = null, @.iMonth int = NULL,
@.iYear int = null
SELECT TOP 50
p.[ID],
np.[Name] as Prefix,
p.[FirstName],
p.[MiddleName],
p.[LastName],
p.[DateOfBirth]
FROM
[Patient] p
JOIN
[NamePrefix] np ON p.NamePrefixID = np.[ID]
WHERE
(@.strFirstName IS NULL OR [FirstName] Like @.strFirstName + '%') AND
(@.strLastName IS NULL OR [LastName] Like @.strLastName + '%') AND
(@.iMonth IS NULL OR DATEPART(m,[DateOfBirth]) = @.iMonth) AND
(@.iDay IS NULL OR DATEPART(d,[DateOfBirth]) = @.iDay) AND
(@.iYear IS NULL OR DATEPART(yyyy,[DateOfBirth]) =@.iYear )
ORDER BY
p.[LastName],
p.[MiddleName],
p.[FirstName]
GOHave a look at
http://www.sommarskog.se/dyn-search.html
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Jeremy Chapman" <nospam@.please.com> wrote in message
news:eSzgjdfhGHA.3424@.TK2MSFTNGP05.phx.gbl...
>I have a stored procedure declared (shown below) The intent of the stored
>proc is to return all records where the field values match the criteria
>specified in the stored proc parameters. I want to specify some or all of
>the parameter values. What I have written works, but I don't think it is
>very efficient, any ideas?
> CREATE PROCEDURE dbo.pSearch
> @.strFirstName varchar(50) = NULL,
> @.strLastName varchar(50) = NULL, @.iDay int = null, @.iMonth int = NULL,
> @.iYear int = null
>
> SELECT TOP 50
> p.[ID],
> np.[Name] as Prefix,
> p.[FirstName],
> p.[MiddleName],
> p.[LastName],
> p.[DateOfBirth]
> FROM
> [Patient] p
> JOIN
> [NamePrefix] np ON p.NamePrefixID = np.[ID]
> WHERE
> (@.strFirstName IS NULL OR [FirstName] Like @.strFirstName + '%') AND
> (@.strLastName IS NULL OR [LastName] Like @.strLastName + '%') AND
> (@.iMonth IS NULL OR DATEPART(m,[DateOfBirth]) = @.iMonth) AND
> (@.iDay IS NULL OR DATEPART(d,[DateOfBirth]) = @.iDay) AND
> (@.iYear IS NULL OR DATEPART(yyyy,[DateOfBirth]) =@.iYear )
> ORDER BY
> p.[LastName],
> p.[MiddleName],
> p.[FirstName]
> GO
>
proc is to return all records where the field values match the criteria
specified in the stored proc parameters. I want to specify some or all of
the parameter values. What I have written works, but I don't think it is
very efficient, any ideas?
CREATE PROCEDURE dbo.pSearch
@.strFirstName varchar(50) = NULL,
@.strLastName varchar(50) = NULL, @.iDay int = null, @.iMonth int = NULL,
@.iYear int = null
SELECT TOP 50
p.[ID],
np.[Name] as Prefix,
p.[FirstName],
p.[MiddleName],
p.[LastName],
p.[DateOfBirth]
FROM
[Patient] p
JOIN
[NamePrefix] np ON p.NamePrefixID = np.[ID]
WHERE
(@.strFirstName IS NULL OR [FirstName] Like @.strFirstName + '%') AND
(@.strLastName IS NULL OR [LastName] Like @.strLastName + '%') AND
(@.iMonth IS NULL OR DATEPART(m,[DateOfBirth]) = @.iMonth) AND
(@.iDay IS NULL OR DATEPART(d,[DateOfBirth]) = @.iDay) AND
(@.iYear IS NULL OR DATEPART(yyyy,[DateOfBirth]) =@.iYear )
ORDER BY
p.[LastName],
p.[MiddleName],
p.[FirstName]
GOHave a look at
http://www.sommarskog.se/dyn-search.html
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Jeremy Chapman" <nospam@.please.com> wrote in message
news:eSzgjdfhGHA.3424@.TK2MSFTNGP05.phx.gbl...
>I have a stored procedure declared (shown below) The intent of the stored
>proc is to return all records where the field values match the criteria
>specified in the stored proc parameters. I want to specify some or all of
>the parameter values. What I have written works, but I don't think it is
>very efficient, any ideas?
> CREATE PROCEDURE dbo.pSearch
> @.strFirstName varchar(50) = NULL,
> @.strLastName varchar(50) = NULL, @.iDay int = null, @.iMonth int = NULL,
> @.iYear int = null
>
> SELECT TOP 50
> p.[ID],
> np.[Name] as Prefix,
> p.[FirstName],
> p.[MiddleName],
> p.[LastName],
> p.[DateOfBirth]
> FROM
> [Patient] p
> JOIN
> [NamePrefix] np ON p.NamePrefixID = np.[ID]
> WHERE
> (@.strFirstName IS NULL OR [FirstName] Like @.strFirstName + '%') AND
> (@.strLastName IS NULL OR [LastName] Like @.strLastName + '%') AND
> (@.iMonth IS NULL OR DATEPART(m,[DateOfBirth]) = @.iMonth) AND
> (@.iDay IS NULL OR DATEPART(d,[DateOfBirth]) = @.iDay) AND
> (@.iYear IS NULL OR DATEPART(yyyy,[DateOfBirth]) =@.iYear )
> ORDER BY
> p.[LastName],
> p.[MiddleName],
> p.[FirstName]
> GO
>
Best way to return records in a date range using where clause?
Say I want to return only records with dates that fall within the next 6 months. Is there some straight-forward, simple way of doing so?
As of now, I'm explicitly giving it a date 6 months in the future, but I'd like to replace it with some sort of function.
SELECT DateField1
WHERE (DateField1 < CONVERT(DATETIME, '2008-06-03 00:00:00', 102))
Any help is greatly appreciated... btw I'm using SQL 2005.
SELECT *FROM YourTableWHERE DateFieldBETWEENGETDATE()ANDDATEADD(m, 6,GETDATE())
Friday, February 10, 2012
Best way to check if a field Is NULL
I usually check is a field is Null by doing something like this:
WHERE IsNull(myField,'') = ''
However, I just noticed that this is also return records where myField = 0
Does anyone know why?
What is the best and most common way to check if a field is null?
The best way to check if a field is null is
WHERE myField IS NULL
Are you by chance checking for a "blank" field instead of a null field?
|||Zero is not null.
Try this:
Code Snippet
select *
from MyTable
where MyField IS NULL
|||Arnie,The fact that rows with MyField = 0 are returned is not a problem with the query. If the type of MyField is integer and its value is zero, the query will test 0 = '', which is true. This is because '' is implicitly converted to integer in the comparison, and in SQL Server, cast('' as integer) has the value zero.
Steve Kass
Drew University
http://www.stevekass.com
|||
Steve,
...such rows would indicate that there is a problem in the query.
Thanks for the clarification. I was referring to the nature of the query -not the evaluation of implicit zero. The OP wanted NULL rows, so the way the query was written (invoking implicit conversions) would not satisfy his/her needs.
I could have more precise, but I dropped it. I'm glad you jumped in to dispel any misconceptions I may have created.
Subscribe to:
Posts (Atom)