Days between Two Dates.

Hi all,

I have a table in which I have two fields in my DB.

FromDate and ToDate.

Both are stored as Varchar(MAX).

I would like to have an SP which gives me the Days in Between them.

Regards,

Naveen.

try this:

selectdatediff(day,convert (datetime ,'07/05/2007'),convert (datetime ,'07/06/2007'))as [day diff]

|||

Hi addie,

what I want is an SP.

Regards,

Naveen

|||

ifexists (select *fromsysobjectswhere id =object_id ('sp_test'))drop proceduresp_testgocreate proceduresp_test@.start_datevarchar(20),@.end_datevarchar(20)asselectdatediff(day,convert (datetime , @.start_date),convert (datetime , @.end_date))as [day diff]

Date-time unsupported in subquery?

I have a query that returns 2 fields, a date-time field and a Count(*)
field. Basically it tells me how many people exist for a specific date. I
want to use this as a subquery and test for the earliest date that the
Count(*) field (members) is below a certain number. However, once I use it
in a subquery I get an error on the date-time field that it is an
unsupported data type... any clues?
SELECT TermEndDate, Members
FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Members
FROM Person_mm_Board AS P
WHERE (BoardID = @.BoardID)
GROUP BY TermEndDate
ORDER BY TermEndDate) AS SAre you making this too difficult? Perhaps this will do the same without err
or.
SELECT
TermEndDate
, count( Members )
FROM Person_mm_Board
WHERE BoardID = @.BoardID
GROUP BY TermEndDate
ORDER BY TermEndDate
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.
gbl...
>I have a query that returns 2 fields, a date-time field and a Count(*)
> field. Basically it tells me how many people exist for a specific date.
I
> want to use this as a subquery and test for the earliest date that the
> Count(*) field (members) is below a certain number. However, once I use i
t
> in a subquery I get an error on the date-time field that it is an
> unsupported data type... any clues?
>
> SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Member
s
> FROM Person_mm_Board AS P
> WHERE (BoardID = @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S
>
>|||Yes this will do the same thing. I'm not finished with the main query yet.
The end query will look something like below. Just trying to simplify thin
gs to find the source of the problem. The question remains - Why is the dat
etime field not showing up when used as a subquery? Thanks.
SELECT TOP(1) TermEndDate, Members
FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Members,
BoardID
FROM Person_mm_Board AS P
WHERE (BoardID = @.BoardID)
GROUP BY TermEndDate
ORDER BY TermEndDate) AS S
INNER JOIN Board AS B
ON B.BoardID = S.BoardID
WHERE S.Members < B.Members
"Arnie Rowland" <arnie@.1568.com> wrote in message news:eVSMvOcpGHA.2400@.TK2M
SFTNGP03.phx.gbl...
Are you making this too difficult? Perhaps this will do the same without err
or.
SELECT
TermEndDate
, count( Members )
FROM Person_mm_Board
WHERE BoardID = @.BoardID
GROUP BY TermEndDate
ORDER BY TermEndDate
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.
gbl...
>I have a query that returns 2 fields, a date-time field and a Count(*)
> field. Basically it tells me how many people exist for a specific date.
I
> want to use this as a subquery and test for the earliest date that the
> Count(*) field (members) is below a certain number. However, once I use i
t
> in a subquery I get an error on the date-time field that it is an
> unsupported data type... any clues?
>
> SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Member
s
> FROM Person_mm_Board AS P
> WHERE (BoardID = @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S
>
>|||OK I cast the datetime field to varchar and then back to datetime in the mai
n query and it seems to work. Here's my final query.
SELECT TOP (1) S.Members, CAST(S.Expr1 AS datetime) AS TermEnd
FROM (SELECT TOP (100) PERCENT CAST(TermEndDate AS Varchar) AS E
xpr1, COUNT(*) AS Members, BoardID
FROM Person_mm_Board AS P
WHERE (BoardID = @.BoardID)
GROUP BY TermEndDate, BoardID
ORDER BY TermEndDate) AS S INNER JOIN
Board AS B ON S.BoardID = B.BoardID AND S.Members < B.NumberMembers
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message news:OJ3MOdcpGHA.4548@.TK2
MSFTNGP03.phx.gbl...
Yes this will do the same thing. I'm not finished with the main query yet.
The end query will look something like below. Just trying to simplify thin
gs to find the source of the problem. The question remains - Why is the dat
etime field not showing up when used as a subquery? Thanks.
SELECT TOP(1) TermEndDate, Members
FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Members,
BoardID
FROM Person_mm_Board AS P
WHERE (BoardID = @.BoardID)
GROUP BY TermEndDate
ORDER BY TermEndDate) AS S
INNER JOIN Board AS B
ON B.BoardID = S.BoardID
WHERE S.Members < B.Members
"Arnie Rowland" <arnie@.1568.com> wrote in message news:eVSMvOcpGHA.2400@.TK2M
SFTNGP03.phx.gbl...
Are you making this too difficult? Perhaps this will do the same without err
or.
SELECT
TermEndDate
, count( Members )
FROM Person_mm_Board
WHERE BoardID = @.BoardID
GROUP BY TermEndDate
ORDER BY TermEndDate
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.
gbl...
>I have a query that returns 2 fields, a date-time field and a Count(*)
> field. Basically it tells me how many people exist for a specific date.
I
> want to use this as a subquery and test for the earliest date that the
> Count(*) field (members) is below a certain number. However, once I use i
t
> in a subquery I get an error on the date-time field that it is an
> unsupported data type... any clues?
>
> SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Member
s
> FROM Person_mm_Board AS P
> WHERE (BoardID = @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S
>
>|||Just to clarify:
if you remove the parenthesis around the number 100 so that the code reads
... (SELECT TOP 100 PERCENT ...
your code is good to run.
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.gbl...
>I have a query that returns 2 fields, a date-time field and a Count(*)
>field. Basically it tells me how many people exist for a specific date. I
>want to use this as a subquery and test for the earliest date that the
>Count(*) field (members) is below a certain number. However, once I use it
>in a subquery I get an error on the date-time field that it is an
>unsupported data type... any clues?
> SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS
> Members
> FROM Person_mm_Board AS P
> WHERE (BoardID = @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S
>|||On Wed, 12 Jul 2006 09:48:39 -0500, Ryan wrote:

>I have a query that returns 2 fields, a date-time field and a Count(*)
>field. Basically it tells me how many people exist for a specific date. I
>want to use this as a subquery and test for the earliest date that the
>Count(*) field (members) is below a certain number. However, once I use it
>in a subquery I get an error on the date-time field that it is an
>unsupported data type... any clues?
Hi Ryan,
None at all. Datetime columns are allowed in subqueries. It might help
if could post the actual error message (use copy and paste to prevent
transcription errors). I'd also like to see the structure of the table
(posted as a CREATE TABLE statement).

>SELECT TermEndDate, Members
>FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Members
> FROM Person_mm_Board AS P
> WHERE (BoardID = @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S
The ORDER BY and the TOP (100) PERCENT in the subquery are completely
useless. Get rid of them.
If you need the results to be ordered, put an ORDER BY clause on the
outer query:
SELECT TermEndDate, Members
FROM (SELECT TermEndDate, COUNT(*) AS Members
FROM Person_mm_Board AS P
WHERE BoardID = @.BoardID)
GROUP BY TermEndDate) AS S
ORDER BY TermEndDate
Hugo Kornelis, SQL Server MVP|||It still looks like you are making this too difficult.
Datetime fields work just fine in sub-queries.
Are you attempting to locate the next Board Member with term expiring?
Having a bit more detail about what you are working with (table DDL, sample
data, complete problem story) sure would make it easier to assist you. Witho
ut that, we are just playing twenty questions with you.
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message news:OE5bfhcpGHA.756@.TK2M
SFTNGP05.phx.gbl...
OK I cast the datetime field to varchar and then back to datetime in the mai
n query and it seems to work. Here's my final query.
SELECT TOP (1) S.Members, CAST(S.Expr1 AS datetime) AS TermEnd
FROM (SELECT TOP (100) PERCENT CAST(TermEndDate AS Varchar) AS E
xpr1, COUNT(*) AS Members, BoardID
FROM Person_mm_Board AS P
WHERE (BoardID = @.BoardID)
GROUP BY TermEndDate, BoardID
ORDER BY TermEndDate) AS S INNER JOIN
Board AS B ON S.BoardID = B.BoardID AND S.Members < B.NumberMembers
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message news:OJ3MOdcpGHA.4548@.TK2
MSFTNGP03.phx.gbl...
Yes this will do the same thing. I'm not finished with the main query yet.
The end query will look something like below. Just trying to simplify thin
gs to find the source of the problem. The question remains - Why is the dat
etime field not showing up when used as a subquery? Thanks.
SELECT TOP(1) TermEndDate, Members
FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Members,
BoardID
FROM Person_mm_Board AS P
WHERE (BoardID = @.BoardID)
GROUP BY TermEndDate
ORDER BY TermEndDate) AS S
INNER JOIN Board AS B
ON B.BoardID = S.BoardID
WHERE S.Members < B.Members
"Arnie Rowland" <arnie@.1568.com> wrote in message news:eVSMvOcpGHA.2400@.TK2M
SFTNGP03.phx.gbl...
Are you making this too difficult? Perhaps this will do the same without err
or.
SELECT
TermEndDate
, count( Members )
FROM Person_mm_Board
WHERE BoardID = @.BoardID
GROUP BY TermEndDate
ORDER BY TermEndDate
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.
gbl...
>I have a query that returns 2 fields, a date-time field and a Count(*)
> field. Basically it tells me how many people exist for a specific date.
I
> want to use this as a subquery and test for the earliest date that the
> Count(*) field (members) is below a certain number. However, once I use i
t
> in a subquery I get an error on the date-time field that it is an
> unsupported data type... any clues?
>
> SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Member
s
> FROM Person_mm_Board AS P
> WHERE (BoardID = @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S
>
>

Date-time unsupported in subquery?

I have a query that returns 2 fields, a date-time field and a Count(*)
field. Basically it tells me how many people exist for a specific date. I
want to use this as a subquery and test for the earliest date that the
Count(*) field (members) is below a certain number. However, once I use it
in a subquery I get an error on the date-time field that it is an
unsupported data type... any clues?
SELECT TermEndDate, Members
FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Members
FROM Person_mm_Board AS P
WHERE (BoardID = @.BoardID)
GROUP BY TermEndDate
ORDER BY TermEndDate) AS SThis is a multi-part message in MIME format.
--=_NextPart_000_0BC1_01C6A589.01BE9E50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Are you making this too difficult? Perhaps this will do the same without =error.
SELECT
TermEndDate
, count( Members )
FROM Person_mm_Board
WHERE BoardID =3D @.BoardID
GROUP BY TermEndDate
ORDER BY TermEndDate
-- Arnie Rowland* "To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message =news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.gbl...
>I have a query that returns 2 fields, a date-time field and a Count(*) > field. Basically it tells me how many people exist for a specific =date. I > want to use this as a subquery and test for the earliest date that the =
> Count(*) field (members) is below a certain number. However, once I =use it > in a subquery I get an error on the date-time field that it is an > unsupported data type... any clues?
> > SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS =Members
> FROM Person_mm_Board AS P
> WHERE (BoardID =3D @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S > >
--=_NextPart_000_0BC1_01C6A589.01BE9E50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Are you making this too difficult? =Perhaps this will do the same without error.
SELECT TermEndDate , count( Members )FROM =Person_mm_BoardWHERE BoardID =3D @.BoardIDGROUP BY TermEndDateORDER BY =TermEndDate
-- Arnie Rowland* "To be =successful, your heart must accompany your knowledge."
"Ryan" wrote in message news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.gbl...>I =have a query that returns 2 fields, a date-time field and a Count(*) > =field. Basically it tells me how many people exist for a specific date. I => want to use this as a subquery and test for the earliest date =that the > Count(*) field (members) is below a certain number. =However, once I use it > in a subquery I get an error on the date-time field =that it is an > unsupported data type... any clues?> > SELECT TermEndDate, Members> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) =AS Members> &nbs=p;  =; FROM =Person_mm_Board AS P> &nbs=p; WHERE (BoardID =3D @.BoardID)> &n=bsp; &nb=sp; GROUP BY TermEndDate> = &=nbsp; ORDER BY TermEndDate) AS S > >

---

--=_NextPart_000_0BC1_01C6A589.01BE9E50--|||This is a multi-part message in MIME format.
--=_NextPart_000_0033_01C6A59D.202C71F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Yes this will do the same thing. I'm not finished with the main query =yet. The end query will look something like below. Just trying to =simplify things to find the source of the problem. The question remains =- Why is the datetime field not showing up when used as a subquery? =Thanks.
SELECT TOP(1) TermEndDate, Members
FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS =Members, BoardID
FROM Person_mm_Board AS P
WHERE (BoardID =3D @.BoardID)
GROUP BY TermEndDate
ORDER BY TermEndDate) AS S INNER JOIN Board AS B ON B.BoardID =3D S.BoardID
WHERE S.Members < B.Members "Arnie Rowland" <arnie@.1568.com> wrote in message =news:eVSMvOcpGHA.2400@.TK2MSFTNGP03.phx.gbl...
Are you making this too difficult? Perhaps this will do the same =without error.
SELECT
TermEndDate
, count( Members )
FROM Person_mm_Board
WHERE BoardID =3D @.BoardID
GROUP BY TermEndDate
ORDER BY TermEndDate
-- Arnie Rowland* "To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message =news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.gbl...
>I have a query that returns 2 fields, a date-time field and a =Count(*) > field. Basically it tells me how many people exist for a specific =date. I > want to use this as a subquery and test for the earliest date that =the > Count(*) field (members) is below a certain number. However, once I =use it > in a subquery I get an error on the date-time field that it is an > unsupported data type... any clues?
> > SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS =Members
> FROM Person_mm_Board AS P
> WHERE (BoardID =3D @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S > >
--=_NextPart_000_0033_01C6A59D.202C71F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Yes this will do the same thing. =I'm not finished with the main query yet. The end query will look =something like below. Just trying to simplify things to find the source of the problem. The question remains - Why is the datetime field not =showing up when used as a subquery? Thanks.
SELECT TOP(1) TermEndDate, MembersFROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) =AS Members, BoardID &n=bsp; FROM =Person_mm_Board AS P &n=bsp; WHERE (BoardID =3D @.BoardID) = =GROUP BY TermEndDate &nbs=p;  =; ORDER BY TermEndDate) AS S
INNER JOIN Board AS B
ON B.BoardID =3D S.BoardID
WHERE S.Members < B.Members
"Arnie Rowland" wrote in message =news:eVSMvOcpGHA.2400=@.TK2MSFTNGP03.phx.gbl...
Are you making this too difficult? =Perhaps this will do the same without error.

SELECT TermEndDate , count( Members )FROM Person_mm_BoardWHERE BoardID =3D @.BoardIDGROUP BY =TermEndDateORDER BY TermEndDate

-- Arnie Rowland* "To be =successful, your heart must accompany your knowledge."


"Ryan" wrote in message news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.gbl...>I have a query that returns 2 fields, a date-time field and a Count(*) > =field. Basically it tells me how many people exist for a specific date. =I > want to use this as a subquery and test for the earliest date =that the > Count(*) field (members) is below a certain number. = However, once I use it > in a subquery I get an error on the =date-time field that it is an > unsupported data type... any =clues?> > SELECT TermEndDate, Members> FROM (SELECT TOP (100) PERCENT TermEndDate, =COUNT(*) AS =Members> &nbs=p;  =; FROM =Person_mm_Board AS =P> &nbs=p; WHERE (BoardID =3D =@.BoardID)> &n=bsp; &nb=sp; GROUP BY =TermEndDate> = &=nbsp; ORDER BY TermEndDate) AS S > > =

---

--=_NextPart_000_0033_01C6A59D.202C71F0--|||This is a multi-part message in MIME format.
--=_NextPart_000_004C_01C6A59E.30B9DA20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
OK I cast the datetime field to varchar and then back to datetime in the =main query and it seems to work. Here's my final query.
SELECT TOP (1) S.Members, CAST(S.Expr1 AS datetime) AS TermEnd
FROM (SELECT TOP (100) PERCENT CAST(TermEndDate AS Varchar) =AS Expr1, COUNT(*) AS Members, BoardID
FROM Person_mm_Board AS P
WHERE (BoardID =3D @.BoardID)
GROUP BY TermEndDate, BoardID
ORDER BY TermEndDate) AS S INNER JOIN
Board AS B ON S.BoardID =3D B.BoardID AND =S.Members < B.NumberMembers
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message =news:OJ3MOdcpGHA.4548@.TK2MSFTNGP03.phx.gbl...
Yes this will do the same thing. I'm not finished with the main query =yet. The end query will look something like below. Just trying to =simplify things to find the source of the problem. The question remains =- Why is the datetime field not showing up when used as a subquery? =Thanks.
SELECT TOP(1) TermEndDate, Members
FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS =Members, BoardID
FROM Person_mm_Board AS P
WHERE (BoardID =3D @.BoardID)
GROUP BY TermEndDate
ORDER BY TermEndDate) AS S INNER JOIN Board AS B ON B.BoardID =3D S.BoardID
WHERE S.Members < B.Members "Arnie Rowland" <arnie@.1568.com> wrote in message =news:eVSMvOcpGHA.2400@.TK2MSFTNGP03.phx.gbl...
Are you making this too difficult? Perhaps this will do the same =without error.
SELECT
TermEndDate
, count( Members )
FROM Person_mm_Board
WHERE BoardID =3D @.BoardID
GROUP BY TermEndDate
ORDER BY TermEndDate
-- Arnie Rowland* "To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message =news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.gbl...
>I have a query that returns 2 fields, a date-time field and a =Count(*) > field. Basically it tells me how many people exist for a specific =date. I > want to use this as a subquery and test for the earliest date that =the > Count(*) field (members) is below a certain number. However, once =I use it > in a subquery I get an error on the date-time field that it is an > unsupported data type... any clues?
> > SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) =AS Members
> FROM Person_mm_Board AS P
> WHERE (BoardID =3D @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S > >
--=_NextPart_000_004C_01C6A59E.30B9DA20
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

OK I cast the datetime field to varchar =and then back to datetime in the main query and it seems to work. Here's my =final query.
SELECT TOP (1) =S.Members, CAST(S.Expr1 AS datetime) AS TermEndFROM (SELECT TOP (100) PERCENT CAST(TermEndDate AS =Varchar) AS Expr1, COUNT(*) AS Members, BoardID &n=bsp; FROM =Person_mm_Board AS P &n=bsp; WHERE (BoardID =3D @.BoardID) = =GROUP BY TermEndDate, BoardID &n=bsp; ORDER BY TermEndDate) AS S INNER JOIN  =; Board AS B ON S.BoardID =3D B.BoardID AND S.Members < B.NumberMembers
"Ryan" = wrote in message news:OJ3MOdcpGHA.4548=@.TK2MSFTNGP03.phx.gbl...
Yes this will do the same =thing. I'm not finished with the main query yet. The end query will look =something like below. Just trying to simplify things to find the source of the problem. The question remains - Why is the datetime field not =showing up when used as a subquery? Thanks.

SELECT TOP(1) TermEndDate, MembersFROM (SELECT TOP (100) PERCENT TermEndDate, =COUNT(*) AS Members, =BoardID &n=bsp; FROM =Person_mm_Board AS =P &n=bsp; WHERE (BoardID =3D =@.BoardID) = = GROUP BY =TermEndDate &nbs=p;  =; ORDER BY TermEndDate) AS S
INNER JOIN Board AS B
ON B.BoardID =3D S.BoardID
WHERE S.Members < B.Members
"Arnie Rowland" wrote in =message news:eVSMvOcpGHA.2400=@.TK2MSFTNGP03.phx.gbl...
Are you making this too difficult? =Perhaps this will do the same without error.

SELECT TermEndDate , count( Members )FROM Person_mm_BoardWHERE BoardID =3D @.BoardIDGROUP BY =TermEndDateORDER BY TermEndDate

-- Arnie Rowland* "To =be successful, your heart must accompany your knowledge."


"Ryan" wrote in message news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.gbl...>I have a query that returns 2 fields, a date-time field and a Count(*) > field. Basically it tells me how many people exist for a =specific date. I > want to use this as a subquery and test for =the earliest date that the > Count(*) field (members) is below a =certain number. However, once I use it > in a subquery I get an =error on the date-time field that it is an > unsupported data =type... any clues?> > SELECT =TermEndDate, Members> FROM = (SELECT TOP (100) PERCENT TermEndDate, =COUNT(*) AS =Members> &nbs=p;  =; FROM =Person_mm_Board AS =P> &nbs=p; WHERE (BoardID =3D =@.BoardID)> &n=bsp; &nb=sp; GROUP BY =TermEndDate> = &=nbsp; ORDER BY TermEndDate) AS S > >

---

--=_NextPart_000_004C_01C6A59E.30B9DA20--|||Just to clarify:
if you remove the parenthesis around the number 100 so that the code reads
... (SELECT TOP 100 PERCENT ...
your code is good to run.
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.gbl...
>I have a query that returns 2 fields, a date-time field and a Count(*)
>field. Basically it tells me how many people exist for a specific date. I
>want to use this as a subquery and test for the earliest date that the
>Count(*) field (members) is below a certain number. However, once I use it
>in a subquery I get an error on the date-time field that it is an
>unsupported data type... any clues?
> SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS
> Members
> FROM Person_mm_Board AS P
> WHERE (BoardID = @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S
>|||On Wed, 12 Jul 2006 09:48:39 -0500, Ryan wrote:
>I have a query that returns 2 fields, a date-time field and a Count(*)
>field. Basically it tells me how many people exist for a specific date. I
>want to use this as a subquery and test for the earliest date that the
>Count(*) field (members) is below a certain number. However, once I use it
>in a subquery I get an error on the date-time field that it is an
>unsupported data type... any clues?
Hi Ryan,
None at all. Datetime columns are allowed in subqueries. It might help
if could post the actual error message (use copy and paste to prevent
transcription errors). I'd also like to see the structure of the table
(posted as a CREATE TABLE statement).
>SELECT TermEndDate, Members
>FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Members
> FROM Person_mm_Board AS P
> WHERE (BoardID = @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S
The ORDER BY and the TOP (100) PERCENT in the subquery are completely
useless. Get rid of them.
If you need the results to be ordered, put an ORDER BY clause on the
outer query:
SELECT TermEndDate, Members
FROM (SELECT TermEndDate, COUNT(*) AS Members
FROM Person_mm_Board AS P
WHERE BoardID = @.BoardID)
GROUP BY TermEndDate) AS S
ORDER BY TermEndDate
Hugo Kornelis, SQL Server MVP|||This is a multi-part message in MIME format.
--=_NextPart_000_0C84_01C6A5D2.9A8AF130
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
It still looks like you are making this too difficult.
Datetime fields work just fine in sub-queries.
Are you attempting to locate the next Board Member with term expiring?
Having a bit more detail about what you are working with (table DDL, =sample data, complete problem story) sure would make it easier to assist =you. Without that, we are just playing twenty questions with you.
-- Arnie Rowland* "To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message =news:OE5bfhcpGHA.756@.TK2MSFTNGP05.phx.gbl...
OK I cast the datetime field to varchar and then back to datetime in =the main query and it seems to work. Here's my final query.
SELECT TOP (1) S.Members, CAST(S.Expr1 AS datetime) AS TermEnd
FROM (SELECT TOP (100) PERCENT CAST(TermEndDate AS =Varchar) AS Expr1, COUNT(*) AS Members, BoardID
FROM Person_mm_Board AS P
WHERE (BoardID =3D @.BoardID)
GROUP BY TermEndDate, BoardID
ORDER BY TermEndDate) AS S INNER JOIN
Board AS B ON S.BoardID =3D B.BoardID AND =S.Members < B.NumberMembers
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message =news:OJ3MOdcpGHA.4548@.TK2MSFTNGP03.phx.gbl...
Yes this will do the same thing. I'm not finished with the main =query yet. The end query will look something like below. Just trying =to simplify things to find the source of the problem. The question =remains - Why is the datetime field not showing up when used as a =subquery? Thanks.
SELECT TOP(1) TermEndDate, Members
FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS =Members, BoardID
FROM Person_mm_Board AS P
WHERE (BoardID =3D @.BoardID)
GROUP BY TermEndDate
ORDER BY TermEndDate) AS S INNER JOIN Board AS B ON B.BoardID =3D S.BoardID
WHERE S.Members < B.Members "Arnie Rowland" <arnie@.1568.com> wrote in message =news:eVSMvOcpGHA.2400@.TK2MSFTNGP03.phx.gbl...
Are you making this too difficult? Perhaps this will do the same =without error.
SELECT
TermEndDate
, count( Members )
FROM Person_mm_Board
WHERE BoardID =3D @.BoardID
GROUP BY TermEndDate
ORDER BY TermEndDate
-- Arnie Rowland* "To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message =news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.gbl...
>I have a query that returns 2 fields, a date-time field and a =Count(*) > field. Basically it tells me how many people exist for a =specific date. I > want to use this as a subquery and test for the earliest date =that the > Count(*) field (members) is below a certain number. However, =once I use it > in a subquery I get an error on the date-time field that it is =an > unsupported data type... any clues?
> > SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) =AS Members
> FROM Person_mm_Board AS P
> WHERE (BoardID =3D @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S > >
--=_NextPart_000_0C84_01C6A5D2.9A8AF130
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

It still looks like you are making this =too difficult.
Datetime fields work just fine in sub-queries.
Are you attempting to locate the next =Board Member with term expiring?
Having a bit more detail about what you =are working with (table DDL, sample data, complete problem story) sure would make it =easier to assist you. Without that, we are just playing twenty questions with =you.
-- Arnie Rowland* "To be successful, your heart must =accompany your knowledge."
"Ryan" = wrote in message news:OE5bfhcpGHA.756@.T=K2MSFTNGP05.phx.gbl...
OK I cast the datetime field to =varchar and then back to datetime in the main query and it seems to work. Here's =my final query.

SELECT TOP =(1) S.Members, CAST(S.Expr1 AS datetime) AS TermEndFROM (SELECT TOP (100) PERCENT CAST(TermEndDate AS =Varchar) AS Expr1, COUNT(*) AS Members, =BoardID &n=bsp; FROM =Person_mm_Board AS =P &n=bsp; WHERE (BoardID =3D =@.BoardID) = = GROUP BY TermEndDate, =BoardID &n=bsp; ORDER BY TermEndDate) AS S INNER =JOIN  =; Board AS B ON S.BoardID =3D B.BoardID AND S.Members < B.NumberMembers
"Ryan" = wrote in message news:OJ3MOdcpGHA.4548=@.TK2MSFTNGP03.phx.gbl...
Yes this will do the same =thing. I'm not finished with the main query yet. The end query will look =something like below. Just trying to simplify things to find the source =of the problem. The question remains - Why is the datetime field not =showing up when used as a subquery? Thanks.

SELECT TOP(1) TermEndDate, MembersFROM (SELECT TOP (100) PERCENT TermEndDate, =COUNT(*) AS Members, =BoardID &n=bsp; FROM =Person_mm_Board AS =P &n=bsp; WHERE (BoardID =3D =@.BoardID) = = GROUP BY =TermEndDate &nbs=p;  =; ORDER BY TermEndDate) AS S
INNER JOIN Board AS B
ON B.BoardID =3D S.BoardID
WHERE S.Members < B.Members
"Arnie Rowland" wrote in =message news:eVSMvOcpGHA.2400=@.TK2MSFTNGP03.phx.gbl...
Are you making this too =difficult? Perhaps this will do the same without error.

SELECT TermEndDate , count( Members )FROM Person_mm_BoardWHERE BoardID =3D @.BoardIDGROUP BY TermEndDateORDER BY TermEndDate

-- Arnie Rowland* "To =be successful, your heart must accompany your =knowledge."


"Ryan" wrote in message news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.gbl...>I have a query that returns 2 fields, a date-time field and a Count(*) => field. Basically it tells me how many people exist for a =specific date. I > want to use this as a subquery and test for =the earliest date that the > Count(*) field (members) is below =a certain number. However, once I use it > in a =subquery I get an error on the date-time field that it is an > unsupported =data type... any clues?> > =SELECT TermEndDate, Members> FROM (SELECT TOP (100) PERCENT TermEndDate, =COUNT(*) AS =Members> &nbs=p;  =; FROM =Person_mm_Board AS =P> &nbs=p; WHERE (BoardID =3D =@.BoardID)> &n=bsp; &nb=sp; GROUP BY =TermEndDate> = &=nbsp; ORDER BY TermEndDate) AS S > >

---

--=_NextPart_000_0C84_01C6A5D2.9A8AF130--

datetime fields and constarints

I've created a user defined datatype called t_date. It's a datetime datatype. I would like to bind a rule to the udt that constrains the time to 12:00am. Can anyone recommend the best way to do this.What about this?

create rule [myrule] as @.date=convert(varchar(10),@.date,101)+' 12:00'
GO|||Yep that works, in fact that's what I've been doing. I was hoping there was a more efficient way to check instead of having to cast to a string and back to a date again. It's not in your example but I cast back to a datetime to do the compare. Not sure if I have to or not.

Do you know how costly casting data in SQL server is?

Thanks for the help :-)|||It doesn't matter how costly it is, 'cause its the only way! You wants it, you pays for it!

blindman|||Thanx blindman, damn good thing my company has deep pockets because weez gonna have to pay|||Pay for processors...pay for memory...make sure they pay you good too!

Seriously, casting/converting datetime and string values is probably not a fast process, but for the string lengths you are dealing with it should not have a big impact on performance. And there is no way around it. MS Access, MS Excel, and other Microsquash products allow you to take the integer portion of the datetime value (a vast operation), but SQL Server uses a different logic for storing datetime values and this method won't work.

blindman

DateTime comparison with some exceptions

I have StratDateTime and EndDateTime fields in the table. I need to compare this two datetime fields and find seconds. I can use DateDiff but there are the following exceptions:

1. Exclude seconds coming from the date which are Saturday and Sunday

2. Exclude seconds coming from time range between 7:01pm and 6:59am

3. Exclude seconds coming from Jan 1^st and Jul 4^th.

So do you want to make the difference between the two columns in seconds a column in the query results or do you want to compare them to each other or some other values in the WHERE clause of the query? I'd suggest that you post the query you have written and then one of us can help you with the query, as it is you have not really posted enough information for us to help you.|||

Ok. Thank you very much for your response.

SELECT Datediff(ss,StartDateTime,EndDateTime) AS mySeconds

FROM MyTable

This will return seconds. However this does not hold all the exceptions I listed above. Let’s say I have StartDateTime=12/15/2006 7:00pm and EndDateTime=12/18/2006 9:00am, then the difference should be 2 hours because between 12/15/2006 7:00pm and 12/18/2006 7:00am is not a business period, the rest is 2 business hours.

|||This is the same question asked by you before

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1022431&SiteID=1

I will again suggest you to use the calender-table. This will make life easy as you are not able to know 12/16/2006, is Saturday or working day.
With calender-table you can easily find that, more over you can create the holiday list too, find the difference between any date & more functionality can be add according to your own requirements.

Gurpreet S. Gill|||

Thank you very much for your help. That does not work for me since it is considering the day, not the time. My business day should be between 7:00am and 7:00pm in the weekdays. I do not see how getting number of business days would really help.

I would ask the same question, let’s say I have a calendar table, how would I get calendar table return me 2 hours for the following example. I have StartDateTime=12/15/2006 7:00pm and EndDateTime=12/18/2006 9:00am, then the difference should be 2 hours because between 12/15/2006 7:00pm and 12/18/2006 7:00am is not a business period, the rest is 2 business hours.

Thanks you very much for your help.

|||

What you need to do is create a user defined function to calculate your desired value. It will look like this, I haven't put in all the conditional code for you, that will take a while, but you get the idea.

CREATE FUNCTION BusinessSeconds(@.StartTime datetime, @.EndTime datetime)
RETURNS int
AS
BEGIN
DECLARE @.retVal int
SET @.retVal = datediff(ss, @.StartTime, @.EndTime)
--Conditional code here to subtract your non-business periods
--eg. IF ... SET @.retVal = @.retVal - 86400
RETURN @.retVal
END

And you'll use it like this

SELECT dbo.BusinessSeconds(StartDateTime, EndDateTime) AS MySeconds
FROM MyTable

Dates from multiple fields

I have fields that are listed as nvarchar in a table. Field1 contains
the month, Field2 contains the day, Field3 contains the year.
I need to pick stuff off this table with a "date" >= 12/10/2004.
How do you construct the "date" from the 3 fields above?
I've tried cast & converts on it, but have not hit the correct combination.
Any help appreciated.
BCBlasting,
Try:
SELECT <COLUMNS>
FROM <TABLE>
WHERE CAST(CAST(FIELD3+FIELD2+FIELD1 AS CHAR(8)) AS DATETIME) <
ETDATE() -- OR OTHER DATE
HTH
Jerry
"Blasting Cap" <goober@.christian.net> wrote in message
news:uWQkDUl1FHA.1028@.TK2MSFTNGP12.phx.gbl...
>I have fields that are listed as nvarchar in a table. Field1 contains the
>month, Field2 contains the day, Field3 contains the year.
> I need to pick stuff off this table with a "date" >= 12/10/2004.
> How do you construct the "date" from the 3 fields above?
> I've tried cast & converts on it, but have not hit the correct
> combination.
> Any help appreciated.
> BC
>|||concatenate and add '/' between the fields and convert the whole thing to a
datetime
http://sqlservercode.blogspot.com/
"Blasting Cap" wrote:

> I have fields that are listed as nvarchar in a table. Field1 contains
> the month, Field2 contains the day, Field3 contains the year.
> I need to pick stuff off this table with a "date" >= 12/10/2004.
> How do you construct the "date" from the 3 fields above?
> I've tried cast & converts on it, but have not hit the correct combination
.
> Any help appreciated.
> BC
>

Dates being saved incorrectly

Hi,
I've got a question about saving into datetime fields in a SQL Server table. A form I have create has two fields both for dates as well as other form fields, but the user may or may not fill in all the form fields, so when they click the save link I have a query which saves all the form fields whether they are blank or not. Unfortunately this is causing the two date fields to be saved in the database as "01/01/1900" even thoough they are blank fields.

What is a good way to not save blank date fields as "01/01/1900"?

Thanks

Stephen

Two choises:

1). If user doesn't leave the field blank, you can set DateTime = System.DateTime.Now, so that you can get the current dattime;

2). Allow your datetime column in your table as NULL value, or GetDate() as Default value, so that if user didn't put value, you can set the field as NULL or current datetime

Hope it helps

Dateime valid ranges

I've got a linked server setup to DB2, and some of the
date fields in the DB contain 1/1/0001 values.

I've got views created in SQL2000 against the DB2 linked server.

When I run a query against a particular table that contains multiple field of datetime type.

I get the below error
Server: Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.

This only happens when I include in the select the field that contains 1/1/0001 values.

I assume since valid dates ranges in SQL are from
January 1, 1753 through December 31, 9999, this would be
what's causing this.

I tried to covert in the select but that failed as well. The only thing that I've been able to do, is to use a DTS to pull the data from the DB2 to a local SQL2000 table, with that fields type set as varchar. This works.

Using a DTS to pull the data to a local table in production isn't a viable workaround, since this table contains 1.8 million rows.

How is DTS converting this field, when convert fails in the select?

How do I get around this?
Thanksif you convert the DB2 attribute to (v)char rather than a date does your query work?

If that works could you convert to string then use a case statment to move the date to the epoc for SQL server?|||Convert the field to vchar on the DB2 side?

This is the only field from the DB2 that I'm having issue wth, I have other fields that are also datetime and all other fields can be retrieved.

I just can't retrieve this one in particular, because it contains 1/1/0001 values, outside the valid SQL2000 date range.|||How about...Add a predicate so the result set only returns valid data, and UNION it to a select where the date column is a literal...since it's unusable anyway...|||convert the attribute in your select. I am wondering if the above mentioned error occures when converting the db2 date to a sql date. If that is the case why not convert it to a (v)char? Or more precisly, use a case statment to move the 01/01/0001 date to (v)char that you cna test and if needed replace the date with an 01/01/1753 date?|||I tried to convert it to vchar, it failed as well.

I don't think your case idea will work either, since I won't be able to get teh value to test against.

I conditionally need this fields values, so I can include that condition, Union as was mentioned in a prior post and just have a place holder in my first slect.

This select is pretty nasty, it already contains one union, so now it looks like it's going to have three. Another for each of the existing selects, ouch.

With the size of this select, I may split it apart, that way I only have to do the union on the one specific field, and the db again as I go thru the first result set. Not prime I know, but sometimes you gotta work around issues.

Thanks for the ideas|||No Create a view on that table

CREATE VIEW myView99
AS
SELECT Col list
FROM myTable99
WHERE ISDATE(DB2dateCol)=1
UNION ALL
SELECT Col list
FROM myTable99
WHERE ISDATE(DB2dateCol)=0|||cool suggestion!

Would your isdate()=0 default the offending date field to a value other than '01/01/0001'?|||Let me try that Brett, I'll let you know the outcome|||CREATE VIEW dbo.blspt_test
AS
SELECT ordid, mgfordt
FROM blspt
WHERE ISDATE(mgfordt)=1
and blbatid = 790
UNION ALL
SELECT ordid,''
FROM blspt
WHERE ISDATE(mgfordt)=0
and blbatid = 790

select * from blspt_test

Same error

Server: Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.

Great suggestion though.|||This is the select without the added unions that I'll have to add because of the date issue.

SELECT dbo.BLSPT.STNID,
dbo.BLSPT.ORDID,
dbo.BLSPT.ORDLNNUM,
dbo.BLSPT.ORDLNTYP,
dbo.BLSPT.ORDLNSEQ,
dbo.BLSPT.ALTLOG,
dbo.BLSPT.SPTNUM,
dbo.BLSPT.SPTLEN,
dbo.BLSPT.EXTCPYNUM,
dbo.BLSPT.PREDSCR,
dbo.BLSPT.RT,
dbo.BLSPT.BCSTDT,
dbo.BLSPT.AIRTIM,
dbo.BLSPT.SPTCHR,
dbo.BLSPT.BCSTTIM,
dbo.BUYUNTHDR.BUYSNAM,
dbo.BUYUNTHDR.BUYLNAM,
dbo.BUYUNTHDR.STRTIM,
dbo.BUYUNTHDR.ETIM,
dbo.ORDLN.SPTCHR,
dbo.ORDLN.SPTPAT,
dbo.OFC.SOFNAM,
dbo.ORDHDR.ACCTTYP,
dbo.STF.FSTNAM,
dbo.STF.LSTNAM,
dbo.PROP_HDR.DEAL_ID
FROM dbo.STF RIGHT OUTER JOIN
dbo.PROP_HDR ON dbo.STF.USR = dbo.PROP_HDR.AEX RIGHT OUTER JOIN
dbo.BLSPT INNER JOIN
dbo.ORDLN ON dbo.BLSPT.ORDID = dbo.ORDLN.ORDID AND dbo.BLSPT.ORDLNNUM = dbo.ORDLN.ORDLNNUM AND dbo.BLSPT.ORDLNTYP = dbo.ORDLN.ORDLNTYP AND
dbo.BLSPT.ORDLNSEQ = dbo.ORDLN.ORDLNSEQ INNER JOIN
dbo.ORDHDR ON dbo.ORDLN.ORDID = dbo.ORDHDR.ORDID ON dbo.PROP_HDR.PROPOSAL_ID = dbo.ORDHDR.PROPOSAL_ID AND
dbo.PROP_HDR.VERSION_ID = dbo.ORDHDR.VERSION_ID LEFT OUTER JOIN
dbo.OFC ON dbo.ORDHDR.SOFID = dbo.OFC.SOFID LEFT OUTER JOIN
dbo.BUYUNTHDR ON dbo.ORDLN.BUYUNTID = dbo.BUYUNTHDR.BUYUNTID
WHERE (dbo.BLSPT.BLBATID = @.batch)

UNION

SELECT dbo.BLSPT.STNID,
dbo.BLSPT.ORDID,
dbo.BLSPT.ORDLNNUM,
dbo.BLSPT.ORDLNTYP,
dbo.BLSPT.ORDLNSEQ,
dbo.BLSPT.ALTLOG,
dbo.BLSPT.SPTNUM,
dbo.BLSPT.SPTLEN,
dbo.BLSPT.EXTCPYNUM,
dbo.BLSPT.PREDSCR,
dbo.BLSPT.RT,
dbo.BLSPT.BCSTDT,
dbo.BLSPT.AIRTIM,
dbo.BLSPT.SPTCHR,
dbo.BLSPT.BCSTTIM,
dbo.BUYUNTHDR.BUYSNAM,
dbo.BUYUNTHDR.BUYLNAM,
dbo.BUYUNTHDR.STRTIM,
dbo.BUYUNTHDR.ETIM,
8 as sptchr,
'' as sptpat,
dbo.OFC.SOFNAM,
dbo.ORDHDR.ACCTTYP,
dbo.STF.FSTNAM,
dbo.STF.LSTNAM,
dbo.PROP_HDR.DEAL_ID
FROM dbo.STF
RIGHT OUTER JOIN dbo.PROP_HDR ON dbo.STF.USR = dbo.PROP_HDR.AEX
RIGHT OUTER JOIN dbo.BLSPT
INNER JOIN dbo.ORDLNNT ON dbo.BLSPT.ORDID = dbo.ORDLNNT.ORDID
INNER JOIN dbo.ORDHDR ON dbo.ORDLNNT.ORDID = dbo.ORDHDR.ORDID ON dbo.PROP_HDR.PROPOSAL_ID = dbo.ORDHDR.PROPOSAL_ID
AND dbo.PROP_HDR.VERSION_ID = dbo.ORDHDR.VERSION_ID
LEFT OUTER JOIN dbo.OFC ON dbo.ORDHDR.SOFID = dbo.OFC.SOFID
LEFT OUTER JOIN dbo.BUYUNTHDR ON dbo.BLSPT.BUYUNTID = dbo.BUYUNTHDR.BUYUNTID
WHERE (dbo.BLSPT.BLBATID = @.batch)|||Originally posted by jtn916
CREATE VIEW dbo.blspt_test
AS
SELECT ordid, mgfordt
FROM blspt
WHERE ISDATE(mgfordt)=1
and blbatid = 790
UNION ALL
SELECT ordid,''
FROM blspt
WHERE ISDATE(mgfordt)=0
and blbatid = 790

select * from blspt_test

Same error

Server: Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.

Great suggestion though.

Damn...

OK...do you have any say over the DB2 Box?

Maybe you can put the view there...|||I can create tables on the db2 but, when it's updated, which is often, they wipe out all that doesn't belong to the DB, according to "them", thus any tables or views I have would be deleted.

I could however check for existence of the needed view, if it doesn't exist, create it in my app, and go on.

I think I'll just go with my SP that I have working.

if you curious

Thanks for your time.

I have another interesting problem in relationship to DB2 linked servers, views and SQL2000 on 2003 server. I posted it here as well the other day, but got no replies.

In short, a view created against a linked db2 server in sql2000 on 2003 server, will only return 512k of data in a result set when you run a query against it. This is not the case if done on 2000 server. I gave up on that one, and moved all my crap over to a 2000 box.

Thanks for your time, if you hear of a better solution, I'd be interested in hearing it. In relationship to this date issue.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--IF BLSPT.ORDLNTYP = 2 then it's a make good
--Since paradigm stores default values in the mgfordt field
-- as 1/1/0001, and sql pukes on that date, because it's outside
--the valid date range, we'll add unions to handle this.
--One select with a place holder 01/01/1900 for the mgfordt if it's not a
--makegood, and get mgfordt if it is

ALTER procedure dbo.sp_blspt_extract
@.batch as integer
as

SELECT dbo.BLSPT.STNID,
dbo.BLSPT.ORDID,
dbo.BLSPT.ORDLNNUM,
dbo.BLSPT.ORDLNTYP,
dbo.BLSPT.ORDLNSEQ,
dbo.BLSPT.ALTLOG,
dbo.BLSPT.SPTNUM,
dbo.BLSPT.SPTLEN,
dbo.BLSPT.EXTCPYNUM,
dbo.BLSPT.PREDSCR,
cast('01/01/1900' as datetime) as MGFORDT,
dbo.BLSPT.RT,
dbo.BLSPT.BCSTDT,
dbo.BLSPT.AIRTIM,
dbo.BLSPT.SPTCHR,
dbo.BLSPT.BCSTTIM,
dbo.BUYUNTHDR.BUYSNAM,
dbo.BUYUNTHDR.BUYLNAM,
dbo.BUYUNTHDR.STRTIM,
dbo.BUYUNTHDR.ETIM,
dbo.ORDLN.SPTCHR,
dbo.ORDLN.SPTPAT,
dbo.OFC.SOFNAM,
dbo.ORDHDR.ACCTTYP,
dbo.STF.FSTNAM,
dbo.STF.LSTNAM,
dbo.PROP_HDR.DEAL_ID
FROM dbo.STF RIGHT OUTER JOIN
dbo.PROP_HDR ON dbo.STF.USR = dbo.PROP_HDR.AEX RIGHT OUTER JOIN
dbo.BLSPT INNER JOIN
dbo.ORDLN ON dbo.BLSPT.ORDID = dbo.ORDLN.ORDID AND dbo.BLSPT.ORDLNNUM = dbo.ORDLN.ORDLNNUM AND dbo.BLSPT.ORDLNTYP = dbo.ORDLN.ORDLNTYP AND
dbo.BLSPT.ORDLNSEQ = dbo.ORDLN.ORDLNSEQ INNER JOIN
dbo.ORDHDR ON dbo.ORDLN.ORDID = dbo.ORDHDR.ORDID ON dbo.PROP_HDR.PROPOSAL_ID = dbo.ORDHDR.PROPOSAL_ID AND
dbo.PROP_HDR.VERSION_ID = dbo.ORDHDR.VERSION_ID LEFT OUTER JOIN
dbo.OFC ON dbo.ORDHDR.SOFID = dbo.OFC.SOFID LEFT OUTER JOIN
dbo.BUYUNTHDR ON dbo.ORDLN.BUYUNTID = dbo.BUYUNTHDR.BUYUNTID
WHERE (dbo.BLSPT.BLBATID = 790)
--AND (NOT (RTRIM(STNID) IN ('INTUSA', 'INTCAN')))
--CLIEN ACCESS pukes on this one, I'll wead out the few I don't need in code
AND DBO.BLSPT.ORDLNTYP <> 2 --Not a makegood

UNION

SELECT dbo.BLSPT.STNID,
dbo.BLSPT.ORDID,
dbo.BLSPT.ORDLNNUM,
dbo.BLSPT.ORDLNTYP,
dbo.BLSPT.ORDLNSEQ,
dbo.BLSPT.ALTLOG,
dbo.BLSPT.SPTNUM,
dbo.BLSPT.SPTLEN,
dbo.BLSPT.EXTCPYNUM,
dbo.BLSPT.PREDSCR,
cast('01/01/1900' as datetime) as MGFORDT,
dbo.BLSPT.RT,
dbo.BLSPT.BCSTDT,
dbo.BLSPT.AIRTIM,
dbo.BLSPT.SPTCHR,
dbo.BLSPT.BCSTTIM,
dbo.BUYUNTHDR.BUYSNAM,
dbo.BUYUNTHDR.BUYLNAM,
dbo.BUYUNTHDR.STRTIM,
dbo.BUYUNTHDR.ETIM,
8 as sptchr,
'' as sptpat,
dbo.OFC.SOFNAM,
dbo.ORDHDR.ACCTTYP,
dbo.STF.FSTNAM,
dbo.STF.LSTNAM,
dbo.PROP_HDR.DEAL_ID
FROM dbo.STF
RIGHT OUTER JOIN dbo.PROP_HDR ON dbo.STF.USR = dbo.PROP_HDR.AEX
RIGHT OUTER JOIN dbo.BLSPT
INNER JOIN dbo.ORDLNNT ON dbo.BLSPT.ORDID = dbo.ORDLNNT.ORDID
INNER JOIN dbo.ORDHDR ON dbo.ORDLNNT.ORDID = dbo.ORDHDR.ORDID ON dbo.PROP_HDR.PROPOSAL_ID = dbo.ORDHDR.PROPOSAL_ID
AND dbo.PROP_HDR.VERSION_ID = dbo.ORDHDR.VERSION_ID
LEFT OUTER JOIN dbo.OFC ON dbo.ORDHDR.SOFID = dbo.OFC.SOFID
LEFT OUTER JOIN dbo.BUYUNTHDR ON dbo.BLSPT.BUYUNTID = dbo.BUYUNTHDR.BUYUNTID
WHERE (dbo.BLSPT.BLBATID = @.batch)
AND DBO.BLSPT.ORDLNTYP <> 2 --Not a makegood

UNION --Now we'll get the makegoods

SELECT dbo.BLSPT.STNID,
dbo.BLSPT.ORDID,
dbo.BLSPT.ORDLNNUM,
dbo.BLSPT.ORDLNTYP,
dbo.BLSPT.ORDLNSEQ,
dbo.BLSPT.ALTLOG,
dbo.BLSPT.SPTNUM,
dbo.BLSPT.SPTLEN,
dbo.BLSPT.EXTCPYNUM,
dbo.BLSPT.PREDSCR,
dbo.BLSPT.MGFORDT,
dbo.BLSPT.RT,
dbo.BLSPT.BCSTDT,
dbo.BLSPT.AIRTIM,
dbo.BLSPT.SPTCHR,
dbo.BLSPT.BCSTTIM,
dbo.BUYUNTHDR.BUYSNAM,
dbo.BUYUNTHDR.BUYLNAM,
dbo.BUYUNTHDR.STRTIM,
dbo.BUYUNTHDR.ETIM,
dbo.ORDLN.SPTCHR,
dbo.ORDLN.SPTPAT,
dbo.OFC.SOFNAM,
dbo.ORDHDR.ACCTTYP,
dbo.STF.FSTNAM,
dbo.STF.LSTNAM,
dbo.PROP_HDR.DEAL_ID
FROM dbo.STF RIGHT OUTER JOIN
dbo.PROP_HDR ON dbo.STF.USR = dbo.PROP_HDR.AEX RIGHT OUTER JOIN
dbo.BLSPT INNER JOIN
dbo.ORDLN ON dbo.BLSPT.ORDID = dbo.ORDLN.ORDID AND dbo.BLSPT.ORDLNNUM = dbo.ORDLN.ORDLNNUM AND dbo.BLSPT.ORDLNTYP = dbo.ORDLN.ORDLNTYP AND
dbo.BLSPT.ORDLNSEQ = dbo.ORDLN.ORDLNSEQ INNER JOIN
dbo.ORDHDR ON dbo.ORDLN.ORDID = dbo.ORDHDR.ORDID ON dbo.PROP_HDR.PROPOSAL_ID = dbo.ORDHDR.PROPOSAL_ID AND
dbo.PROP_HDR.VERSION_ID = dbo.ORDHDR.VERSION_ID LEFT OUTER JOIN
dbo.OFC ON dbo.ORDHDR.SOFID = dbo.OFC.SOFID LEFT OUTER JOIN
dbo.BUYUNTHDR ON dbo.ORDLN.BUYUNTID = dbo.BUYUNTHDR.BUYUNTID
WHERE (dbo.BLSPT.BLBATID = @.batch)
--AND (NOT (RTRIM(STNID) IN ('INTUSA', 'INTCAN')))
--CLIEN ACCESS pukes on this one, I'll wead out the few I don't need in code
AND DBO.BLSPT.ORDLNTYP = 2 --makegood

UNION

SELECT dbo.BLSPT.STNID,
dbo.BLSPT.ORDID,
dbo.BLSPT.ORDLNNUM,
dbo.BLSPT.ORDLNTYP,
dbo.BLSPT.ORDLNSEQ,
dbo.BLSPT.ALTLOG,
dbo.BLSPT.SPTNUM,
dbo.BLSPT.SPTLEN,
dbo.BLSPT.EXTCPYNUM,
dbo.BLSPT.PREDSCR,
dbo.BLSPT.MGFORDT,
dbo.BLSPT.RT,
dbo.BLSPT.BCSTDT,
dbo.BLSPT.AIRTIM,
dbo.BLSPT.SPTCHR,
dbo.BLSPT.BCSTTIM,
dbo.BUYUNTHDR.BUYSNAM,
dbo.BUYUNTHDR.BUYLNAM,
dbo.BUYUNTHDR.STRTIM,
dbo.BUYUNTHDR.ETIM,
8 as sptchr,
'' as sptpat,
dbo.OFC.SOFNAM,
dbo.ORDHDR.ACCTTYP,
dbo.STF.FSTNAM,
dbo.STF.LSTNAM,
dbo.PROP_HDR.DEAL_ID
FROM dbo.STF
RIGHT OUTER JOIN dbo.PROP_HDR ON dbo.STF.USR = dbo.PROP_HDR.AEX
RIGHT OUTER JOIN dbo.BLSPT
INNER JOIN dbo.ORDLNNT ON dbo.BLSPT.ORDID = dbo.ORDLNNT.ORDID
INNER JOIN dbo.ORDHDR ON dbo.ORDLNNT.ORDID = dbo.ORDHDR.ORDID ON dbo.PROP_HDR.PROPOSAL_ID = dbo.ORDHDR.PROPOSAL_ID
AND dbo.PROP_HDR.VERSION_ID = dbo.ORDHDR.VERSION_ID
LEFT OUTER JOIN dbo.OFC ON dbo.ORDHDR.SOFID = dbo.OFC.SOFID
LEFT OUTER JOIN dbo.BUYUNTHDR ON dbo.BLSPT.BUYUNTID = dbo.BUYUNTHDR.BUYUNTID
WHERE (dbo.BLSPT.BLBATID = @.batch)
AND DBO.BLSPT.ORDLNTYP = 2 --makegood
ORDER BY dbo.BLSPT.BCSTDT ASC,
dbo.BLSPT.AIRTIM ASC

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO|||But I was suggesting you create the view on DB2 and reference it from sql server...

dateformat is ignored

Hello,

I receive a file containing some character fields along with a date.
The date values in the file are formatted as "dd/mm/yy", that is
2-digit day, 2-digit month, and 2-digit year. The separator could be
slash or a dash ("-"). The file is in a proprietary format, and bcp is
not an option.

So, I decided to load the file using a prepared statement. I open a
cursor with an INSERT statement, read from the file, parse out values,
and put it in the database using the cursor. All is OK; except that
the date values are mangled. This is despite the fact that I am issuing
a "set dateformat dmy" before running the INSERT statement.

It seems that the "set dateformat dmy" is not being accepted, or it is
being ignored. I set it at the beginning right after opening a
connection to the database. From what I understand, it should work.
Am I doing something wrong? Any suggestions on how to get this to
work?

Thanks!newtophp2000@.yahoo.com wrote:

> Hello,
> I receive a file containing some character fields along with a date.
> The date values in the file are formatted as "dd/mm/yy", that is
> 2-digit day, 2-digit month, and 2-digit year. The separator could be
> slash or a dash ("-"). The file is in a proprietary format, and bcp is
> not an option.
> So, I decided to load the file using a prepared statement. I open a
> cursor with an INSERT statement, read from the file, parse out values,
> and put it in the database using the cursor. All is OK; except that
> the date values are mangled. This is despite the fact that I am issuing
> a "set dateformat dmy" before running the INSERT statement.
> It seems that the "set dateformat dmy" is not being accepted, or it is
> being ignored. I set it at the beginning right after opening a
> connection to the database. From what I understand, it should work.
> Am I doing something wrong? Any suggestions on how to get this to
> work?
> Thanks!

You say BCP isn't an option but you didn't explain what other method
you are using to read the file or why a cursor is necessary. Don't rely
on SET DATEFORMAT. Use the CONVERT function with the style parameter to
specify the exact format. Looks like style 3 or 103 is what you need.

--
David Portas
SQL Server MVP
--|||David Portas wrote:
> You say BCP isn't an option but you didn't explain what other method
> you are using to read the file or why a cursor is necessary. Don't rely
> on SET DATEFORMAT. Use the CONVERT function with the style parameter to
> specify the exact format. Looks like style 3 or 103 is what you need.

I read from the file line by line and parse the line to extract the
fields. I then use the bound variables in the prepared Insert
statement to add it to the database. I wanted to change the DATEFORMAT
configuration as it seemed to be such a straight answer. I guess I
could use the CONVERT function if it is fast enough. I can do some
tests to see how it performs.

I am curius: is there a particular reason to shy away from setting
DATEFORMAT? Is it not reliable as implemented or something else?

Thanks a lot!

> --
> David Portas
> SQL Server MVP
> --|||Hi

If you are parsing a string then you constructing the date in CCYYMMDD
format will be a safe option.

John

<newtophp2000@.yahoo.com> wrote in message
news:1135777730.480129.321010@.z14g2000cwz.googlegr oups.com...
> David Portas wrote:
>> You say BCP isn't an option but you didn't explain what other method
>> you are using to read the file or why a cursor is necessary. Don't rely
>> on SET DATEFORMAT. Use the CONVERT function with the style parameter to
>> specify the exact format. Looks like style 3 or 103 is what you need.
>
> I read from the file line by line and parse the line to extract the
> fields. I then use the bound variables in the prepared Insert
> statement to add it to the database. I wanted to change the DATEFORMAT
> configuration as it seemed to be such a straight answer. I guess I
> could use the CONVERT function if it is fast enough. I can do some
> tests to see how it performs.
> I am curius: is there a particular reason to shy away from setting
> DATEFORMAT? Is it not reliable as implemented or something else?
> Thanks a lot!
>
>> --
>> David Portas
>> SQL Server MVP
>> --|||David and John,

Thank you very much for your input. I am now using the techniques that
you suggested and it works great!

DATEDIFF possibilities

Hi,

I have two fields in one of my database table. They are date fields, one for the start and the other for the end of the "problem solving". What I want to do is to show the concrete elapsed time between the two.

My first problem is that I'm not able to show hour AND minutes. I wrote this for instance:

SELECT DATEDIFF(hour, fld_date_debut, fld_date_fin) As elapsed_time

I tried HH:mm instead of hour, but it is not working.

My second problem is that I can have more than one start and end hour for the same "problem solving". In the database it's like:

start hour: 10:00 End hour: 11:00 Number of the problem: 1
start hour: 13:00 End hour: 16:00 Number of the problem: 1

So I would like to add these to my elapsed time. I want (11:00 - 10:00) + (16:00 - 13:00)...but how can i do this in my SQL query ?

Thanks.SQL Server is not particularly good at calculating elapsed times. The DATEDIFF function doesn't have a mask to return hours and minutes. My suggestion would be to determine the number of seconds between your dates and then calculate out the hours, minutes, and seconds.

Like this:

DECLARE @.StartDate datetime, @.EndDate datetime, @.ElapsedTime varchar(30)
SELECT @.StartDate = '20040701 11:39:00'
SELECT @.EndDate = '20040701 11:42:09'
SELECT @.ElapsedTime = DATEDIFF(s, @.StartDate, @.EndDate)
SELECT
@.ElapsedTime/3600 AS Hours,
(@.ElapsedTime % 3600)/60 AS Minutes,
@.ElapsedTime % 60 AS Seconds

So, for your needs I think you'd have something like this:

SELECT
ProblemNumber,
Elapsed/3600 AS Hours,
(Elapsed % 3600)/60 AS Minutes,
Elapsed % 60 AS Seconds
FROM
(
SELECT
ProblemNumber,
SUM(DATEDIFF(s,fld_date_debut,fld_date_fin)) AS Elapsed
FROM
YourTable
GROUP BY
ID
) AS SubQuery

Terri

Datediff formula on insert returning null value

I have a form with two date fields that the user will submit their requested vacation time off with. When they insert it, I am trying to say find the difference between the request_start_date and request_end_date in days MINUS any of the days they would already have off like weekends or holidays that are included in another table. Everything inserts okay, but I am getting null for the request_duration. If I put dates in quotes and run the query it comes back with the right results. If I put the dates in the form and submit it, I get Null for the request_duration.

Thank you in advnace for any help on this!

INSERTrequest(emp_id,request_submit_date, request_start_date,request_end_date,request_duration,request_notes,time_off_id)Select@.emp_id,GETDATE(),@.request_start_date,@.request_end_date, 1 +DATEDIFF(day, @.request_start_date, @.request_end_date) - (selectcount(*)from WeekEndsAndHolidayswhere DayOfWeekDatebetween @.request_start_dateand @.request_end_date),@.request_notes,@.time_off_id

Either request_start_date or request_end_date is coming across as null. Are you sure that request_duration is the only null field?|||

Motley,

Thanks for the response. It took a while to get posted and I figured it out way before it got posted. I had worked on it for about an hour before I posted this and figure it out 5 minutes after I posted it. I didn't have one of my text boxes bound correctly.

DateDiff Display All Fields Begin / End Greater 2 Min

I have a SQL Server 2000 database with the table listed below.
I would like to write sql statement from the table listed below to display
of the
fields in the table using datediff function where begin_time - end_time is
greater than 2 minutes.
Please help me complete this task.
Table City_Time
Name varchar(25),
City varchar(35),
State varchar(2),
Begin_Time datetime,
End_Time datetimeCould it be as simple as one of these?
SELECT *
FROM City_Time
WHERE datediff(second, Begin_Time, End_Time) > 120
or
SELECT *
FROM City_Time
WHERE datediff(minute, Begin_Time, End_Time) > 2
Roy Harvey
Beacon Falls, CT
On Tue, 30 Jan 2007 20:15:01 -0800, Joe K.
<JoeK@.discussions.microsoft.com> wrote:
>I have a SQL Server 2000 database with the table listed below.
>I would like to write sql statement from the table listed below to display
>of the
>fields in the table using datediff function where begin_time - end_time is
>greater than 2 minutes.
>Please help me complete this task.
>Table City_Time
>Name varchar(25),
>City varchar(35),
>State varchar(2),
>Begin_Time datetime,
>End_Time datetime
>

DateDiff Display All Fields Begin / End Greater 2 Min

I have a SQL Server 2000 database with the table listed below.
I would like to write sql statement from the table listed below to display
of the
fields in the table using datediff function where begin_time - end_time is
greater than 2 minutes.
Please help me complete this task.
Table City_Time
Name varchar(25),
City varchar(35),
State varchar(2),
Begin_Time datetime,
End_Time datetime
Could it be as simple as one of these?
SELECT *
FROM City_Time
WHERE datediff(second, Begin_Time, End_Time) > 120
or
SELECT *
FROM City_Time
WHERE datediff(minute, Begin_Time, End_Time) > 2
Roy Harvey
Beacon Falls, CT
On Tue, 30 Jan 2007 20:15:01 -0800, Joe K.
<JoeK@.discussions.microsoft.com> wrote:

>I have a SQL Server 2000 database with the table listed below.
>I would like to write sql statement from the table listed below to display
>of the
>fields in the table using datediff function where begin_time - end_time is
>greater than 2 minutes.
>Please help me complete this task.
>Table City_Time
>Name varchar(25),
>City varchar(35),
>State varchar(2),
>Begin_Time datetime,
>End_Time datetime
>

DateDiff Display All Fields Begin / End Greater 2 Min

I have a SQL Server 2000 database with the table listed below.
I would like to write sql statement from the table listed below to display
of the
fields in the table using datediff function where begin_time - end_time is
greater than 2 minutes.
Please help me complete this task.
Table City_Time
Name varchar(25),
City varchar(35),
State varchar(2),
Begin_Time datetime,
End_Time datetimeCould it be as simple as one of these?
SELECT *
FROM City_Time
WHERE datediff(second, Begin_Time, End_Time) > 120
or
SELECT *
FROM City_Time
WHERE datediff(minute, Begin_Time, End_Time) > 2
Roy Harvey
Beacon Falls, CT
On Tue, 30 Jan 2007 20:15:01 -0800, Joe K.
<JoeK@.discussions.microsoft.com> wrote:

>I have a SQL Server 2000 database with the table listed below.
>I would like to write sql statement from the table listed below to display
>of the
>fields in the table using datediff function where begin_time - end_time is
>greater than 2 minutes.
>Please help me complete this task.
>Table City_Time
>Name varchar(25),
>City varchar(35),
>State varchar(2),
>Begin_Time datetime,
>End_Time datetime
>

date/time fields

Hallo,
I know a lot has already been told about date/time fields in a database but
still confuses me, specif when dealing with SQLserver(Express).
It seems that sqlserver only accepts the date in a "yyyyMMdd" format?
(difference between Express and MSDE2000A ?)
What is the one and only true way to deal with this problem in VB2005:
Local settings are Dutch (Belgium) ; thus date is in "dd/MM/yy" (or perhaps
dd/MM/yyyy) and time in "hh:mm:ss"
dim MyDateVar as string, MyIdVar as Integer
dim MyCommand = New Sqlcommand("",Connection)
MyCommand.commandtext = "Update MyTable Set MyDatefield = '" & MyDateVar &
"' Where MyIdField = " & MyIdVar = SomeIntegerValue
MyCommand.executenonquery
How to deal with the MyDateVar when:
1.
The Variable comes from a textbox knowing that the user puts in dd/MMyyyy
In this case there is no need to have the time with it.
2.
The date comes from a datetimepicker control
(MyDateVar = DtPicker.Value)?
3.
The date and time comes from the system
MyDateVar= Format(DateTime.Now, "yyyyMMdd") seems to work but
Format(DateTime.Now, "yyyyMMdd.hhmmss") gives a runtime error
So, any help and/or suggestion on this will be greatly appreciated.
Thanks and greetings to all
JeromeJerome,
It is very simple you should never use a date/time as a string, however
always as a DateTime field.
When you present that to a textbox, than you can use the overloaded toString
with the Iformatprovider
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemglobalizationdatetimeformatinfoclasstopic.asp
If you get it back you can use the Cdate
mydateField = Cdate(mytextbox.text)
And if you want to supply it to a database you use the parameters.
http://www.vb-tips.com/default.aspx?ID=886bba68-8a2f-4b99-8f66-7139b8970071
Maybe even better to show with this more extended but with a Dutch datetime
in it and for Access (OleDb)
http://www.vb-tips.com/default.aspx?ID=550279ec-6767-44ff-aaa3-eb8b44af0137
In fact is that all.
(The datetimepicker.value returns a datetime field).
Cor|||Hi
This is my little guide :
- For dates, use date variables, not into strings. That way you can
do arithmetics, format properly , passs parameters without problems etc
- Use Cdate when picking up dates/times from text fields
- When calling SQLprocedures, use parameters.
- When building an SQL string in a (VB) program, use date format
yyyy-mm-dd , ie. today is 2006-01-25 , and format explicitly ,
do not rely on implicit (locale dependent) formatting.
ie. SQLtext = ... & format (date_var,"yyyy-mm-dd hh:MM:ss") & ...
The somewhat exotic format does not matter inside a program, the important
thing
is that SQLserver never fails to understand you correctly.
No more lottery if 01/04/06 is April 1st or January 4th or ...
Matti
"Jerome" <Jommeke@.fake.com> wrote in message
news:ywKBf.209108$DX6.7008400@.phobos.telenet-ops.be...
> Hallo,
> I know a lot has already been told about date/time fields in a database
> but still confuses me, specif when dealing with SQLserver(Express).
> It seems that sqlserver only accepts the date in a "yyyyMMdd" format?
> (difference between Express and MSDE2000A ?)
> What is the one and only true way to deal with this problem in VB2005:
> Local settings are Dutch (Belgium) ; thus date is in "dd/MM/yy" (or
> perhaps dd/MM/yyyy) and time in "hh:mm:ss"
> dim MyDateVar as string, MyIdVar as Integer
> dim MyCommand = New Sqlcommand("",Connection)
> MyCommand.commandtext = "Update MyTable Set MyDatefield = '" & MyDateVar &
> "' Where MyIdField = " & MyIdVar = SomeIntegerValue
> MyCommand.executenonquery
> How to deal with the MyDateVar when:
> 1.
> The Variable comes from a textbox knowing that the user puts in dd/MMyyyy
> In this case there is no need to have the time with it.
> 2.
> The date comes from a datetimepicker control
> (MyDateVar = DtPicker.Value)?
> 3.
> The date and time comes from the system
> MyDateVar= Format(DateTime.Now, "yyyyMMdd") seems to work but
> Format(DateTime.Now, "yyyyMMdd.hhmmss") gives a runtime error
> So, any help and/or suggestion on this will be greatly appreciated.
> Thanks and greetings to all
> Jerome
>|||I'm a database person, so from a database perspective:
Make sure the variable in the client is date datatype, not string.
Pass it though a command object and a parameter object to SQL Server = you are safe. ADO will do the
string conversion for you.
If you absolutely want to pass it as a string to SQL Server, read
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerome" <Jommeke@.fake.com> wrote in message news:ywKBf.209108$DX6.7008400@.phobos.telenet-ops.be...
> Hallo,
> I know a lot has already been told about date/time fields in a database but still confuses me,
> specif when dealing with SQLserver(Express).
> It seems that sqlserver only accepts the date in a "yyyyMMdd" format? (difference between Express
> and MSDE2000A ?)
> What is the one and only true way to deal with this problem in VB2005:
> Local settings are Dutch (Belgium) ; thus date is in "dd/MM/yy" (or perhaps dd/MM/yyyy) and time
> in "hh:mm:ss"
> dim MyDateVar as string, MyIdVar as Integer
> dim MyCommand = New Sqlcommand("",Connection)
> MyCommand.commandtext = "Update MyTable Set MyDatefield = '" & MyDateVar & "' Where MyIdField = "
> & MyIdVar = SomeIntegerValue
> MyCommand.executenonquery
> How to deal with the MyDateVar when:
> 1.
> The Variable comes from a textbox knowing that the user puts in dd/MMyyyy
> In this case there is no need to have the time with it.
> 2.
> The date comes from a datetimepicker control
> (MyDateVar = DtPicker.Value)?
> 3.
> The date and time comes from the system
> MyDateVar= Format(DateTime.Now, "yyyyMMdd") seems to work but Format(DateTime.Now,
> "yyyyMMdd.hhmmss") gives a runtime error
> So, any help and/or suggestion on this will be greatly appreciated.
> Thanks and greetings to all
> Jerome
>|||Hoi Friends,
Thanks very much the answers. At least these are short, understandable and
valuable answers! Far more better than all the microsoft stuff readings.
I will try the suggestions right away when my sqlserverExpress is working
again. Yesterday Mr. Murphy came to visit and ruined my VS2005 and
Sqlexpress. Nice!
Anyway, the answers leaves my with one more question:
What are the benifits of using Parameters instead plain variables (for
numeric or charachter fields at least)?
As i can see at a first glance there is a lot more wrtiting to do for the
Parameters. (adding them to a command before they are usable, defining the
number of chars for a string param, etc,etc)?
For instance: If the client decides that a stringfield should have more
characters capacity, one should go trough the whole project and adjust the
number of chars for the Params that points to that specific field? Or can
one program a param with, let's say 100 chars, where the field is only 50
chars ? The max charachters is limited by the maxlength property of the
textbox anyway.
Thanks once again for the answers and suggestions
Jerome
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schreef
in bericht news:eapIArdIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> I'm a database person, so from a database perspective:
> Make sure the variable in the client is date datatype, not string.
> Pass it though a command object and a parameter object to SQL Server = you
> are safe. ADO will do the string conversion for you.
> If you absolutely want to pass it as a string to SQL Server, read
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerome" <Jommeke@.fake.com> wrote in message
> news:ywKBf.209108$DX6.7008400@.phobos.telenet-ops.be...
>> Hallo,
>> I know a lot has already been told about date/time fields in a database
>> but still confuses me, specif when dealing with SQLserver(Express).
>> It seems that sqlserver only accepts the date in a "yyyyMMdd" format?
>> (difference between Express and MSDE2000A ?)
>> What is the one and only true way to deal with this problem in VB2005:
>> Local settings are Dutch (Belgium) ; thus date is in "dd/MM/yy" (or
>> perhaps dd/MM/yyyy) and time in "hh:mm:ss"
>> dim MyDateVar as string, MyIdVar as Integer
>> dim MyCommand = New Sqlcommand("",Connection)
>> MyCommand.commandtext = "Update MyTable Set MyDatefield = '" & MyDateVar
>> & "' Where MyIdField = " & MyIdVar = SomeIntegerValue
>> MyCommand.executenonquery
>> How to deal with the MyDateVar when:
>> 1.
>> The Variable comes from a textbox knowing that the user puts in dd/MMyyyy
>> In this case there is no need to have the time with it.
>> 2.
>> The date comes from a datetimepicker control
>> (MyDateVar = DtPicker.Value)?
>> 3.
>> The date and time comes from the system
>> MyDateVar= Format(DateTime.Now, "yyyyMMdd") seems to work but
>> Format(DateTime.Now, "yyyyMMdd.hhmmss") gives a runtime error
>> So, any help and/or suggestion on this will be greatly appreciated.
>> Thanks and greetings to all
>> Jerome
>>
>|||> What are the benifits of using Parameters instead plain variables (for numeric or charachter
> fields at least)?
* Avoid "SQL Injection" (Google and you will find.)
* Assuming that ADO.NET is smart enough to execute your code using sp_executesql and make parameters
for that out of your ADO.NET parameters: You will have a lot greater chance for your query plan to
be re-used.
If you just build a string and first search for "johnson", then SQL Server can cache that plan. But
that cached plan is identified (basically) based on all the text in the query. "johnson" is a part
of that text. Next time, you search for "smith", and SQL Server first searches for a plan match.
Such doesn't exists (you searched for "johnson" last time). So a new plan will be added to plan
cache for this query with "smith" embedded. I've seen installations with 10,000 instances of plans
in cache for the same query! And how much memory is now available for caching data? Not to speak
about the overhead of searching through many many thousands of plans in cache in order to find a
match - every time you execute a query - in vain. If they were parametized, then you'd have only one
plan for the query in cache, and SQL Server would substitute the parameters.
* Better yet, use stored procedures. This way you also have control over if this plan should be
cached in the first place and also plan recompiles. Along with bunch of other advantages of using
stored procedures.
*"Feels better"
I bet others can jump in with other advantages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerome" <Jommeke@.fake.com> wrote in message news:oK2Cf.211227$Xy5.6942778@.phobos.telenet-ops.be...
> Hoi Friends,
> Thanks very much the answers. At least these are short, understandable and valuable answers! Far
> more better than all the microsoft stuff readings.
> I will try the suggestions right away when my sqlserverExpress is working again. Yesterday Mr.
> Murphy came to visit and ruined my VS2005 and Sqlexpress. Nice!
> Anyway, the answers leaves my with one more question:
> What are the benifits of using Parameters instead plain variables (for numeric or charachter
> fields at least)?
> As i can see at a first glance there is a lot more wrtiting to do for the Parameters. (adding them
> to a command before they are usable, defining the number of chars for a string param, etc,etc)?
> For instance: If the client decides that a stringfield should have more characters capacity, one
> should go trough the whole project and adjust the number of chars for the Params that points to
> that specific field? Or can one program a param with, let's say 100 chars, where the field is only
> 50 chars ? The max charachters is limited by the maxlength property of the textbox anyway.
> Thanks once again for the answers and suggestions
> Jerome
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schreef in bericht
> news:eapIArdIGHA.2900@.TK2MSFTNGP14.phx.gbl...
>> I'm a database person, so from a database perspective:
>> Make sure the variable in the client is date datatype, not string.
>> Pass it though a command object and a parameter object to SQL Server = you are safe. ADO will do
>> the string conversion for you.
>> If you absolutely want to pass it as a string to SQL Server, read
>> http://www.karaszi.com/SQLServer/info_datetime.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Jerome" <Jommeke@.fake.com> wrote in message
>> news:ywKBf.209108$DX6.7008400@.phobos.telenet-ops.be...
>> Hallo,
>> I know a lot has already been told about date/time fields in a database but still confuses me,
>> specif when dealing with SQLserver(Express).
>> It seems that sqlserver only accepts the date in a "yyyyMMdd" format? (difference between
>> Express and MSDE2000A ?)
>> What is the one and only true way to deal with this problem in VB2005:
>> Local settings are Dutch (Belgium) ; thus date is in "dd/MM/yy" (or perhaps dd/MM/yyyy) and time
>> in "hh:mm:ss"
>> dim MyDateVar as string, MyIdVar as Integer
>> dim MyCommand = New Sqlcommand("",Connection)
>> MyCommand.commandtext = "Update MyTable Set MyDatefield = '" & MyDateVar & "' Where MyIdField =>> " & MyIdVar = SomeIntegerValue
>> MyCommand.executenonquery
>> How to deal with the MyDateVar when:
>> 1.
>> The Variable comes from a textbox knowing that the user puts in dd/MMyyyy
>> In this case there is no need to have the time with it.
>> 2.
>> The date comes from a datetimepicker control
>> (MyDateVar = DtPicker.Value)?
>> 3.
>> The date and time comes from the system
>> MyDateVar= Format(DateTime.Now, "yyyyMMdd") seems to work but Format(DateTime.Now,
>> "yyyyMMdd.hhmmss") gives a runtime error
>> So, any help and/or suggestion on this will be greatly appreciated.
>> Thanks and greetings to all
>> Jerome
>>
>|||Hoi Tibor,
That explains a lot.
SQL Injection, in my case, is unlikely to occur.They are not going to tamper
with the application. There are a maximu of 4 persons working with the
application and the whole bunch is not even connected to the internet.
Nobody at the site in question ever heard about Sql not to speak about
running a query! I was obliged to use an existing MsAccess Db as backend
(they already are working for years with Access) ;-) and i had to enhance
and expanding the application. So, rewriting 200+ functions!?
Now i'm trying for myself and for learning purposes to rebuild parts of the
applic in VB2005 and with a sqlexpress as backend and that's when i ran into
those date problems. Perhaps that explains a bit more my questions and i am
happy that people like you and others are willing to give advice. If you
have to learn it from the books of Microsoft....pfff. Even for a simple
readonly lookup table and a combobox they lead trough a complete
strongly-typed dataset! Ridicolous
Anyway, thanks a lot for the feedback
Jerome
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schreef
in bericht news:eFrJeZnIGHA.1728@.TK2MSFTNGP09.phx.gbl...
>> What are the benifits of using Parameters instead plain variables (for
>> numeric or charachter fields at least)?
> * Avoid "SQL Injection" (Google and you will find.)
> * Assuming that ADO.NET is smart enough to execute your code using
> sp_executesql and make parameters for that out of your ADO.NET parameters:
> You will have a lot greater chance for your query plan to be re-used.
> If you just build a string and first search for "johnson", then SQL Server
> can cache that plan. But that cached plan is identified (basically) based
> on all the text in the query. "johnson" is a part of that text. Next time,
> you search for "smith", and SQL Server first searches for a plan match.
> Such doesn't exists (you searched for "johnson" last time). So a new plan
> will be added to plan cache for this query with "smith" embedded. I've
> seen installations with 10,000 instances of plans in cache for the same
> query! And how much memory is now available for caching data? Not to speak
> about the overhead of searching through many many thousands of plans in
> cache in order to find a match - every time you execute a query - in vain.
> If they were parametized, then you'd have only one plan for the query in
> cache, and SQL Server would substitute the parameters.
> * Better yet, use stored procedures. This way you also have control over
> if this plan should be cached in the first place and also plan recompiles.
> Along with bunch of other advantages of using stored procedures.
> *"Feels better"
> I bet others can jump in with other advantages.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerome" <Jommeke@.fake.com> wrote in message
> news:oK2Cf.211227$Xy5.6942778@.phobos.telenet-ops.be...
>> Hoi Friends,
>> Thanks very much the answers. At least these are short, understandable
>> and valuable answers! Far more better than all the microsoft stuff
>> readings.
>> I will try the suggestions right away when my sqlserverExpress is working
>> again. Yesterday Mr. Murphy came to visit and ruined my VS2005 and
>> Sqlexpress. Nice!
>> Anyway, the answers leaves my with one more question:
>> What are the benifits of using Parameters instead plain variables (for
>> numeric or charachter fields at least)?
>> As i can see at a first glance there is a lot more wrtiting to do for the
>> Parameters. (adding them to a command before they are usable, defining
>> the number of chars for a string param, etc,etc)?
>> For instance: If the client decides that a stringfield should have more
>> characters capacity, one should go trough the whole project and adjust
>> the number of chars for the Params that points to that specific field? Or
>> can one program a param with, let's say 100 chars, where the field is
>> only 50 chars ? The max charachters is limited by the maxlength property
>> of the textbox anyway.
>> Thanks once again for the answers and suggestions
>> Jerome
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> schreef in bericht news:eapIArdIGHA.2900@.TK2MSFTNGP14.phx.gbl...
>> I'm a database person, so from a database perspective:
>> Make sure the variable in the client is date datatype, not string.
>> Pass it though a command object and a parameter object to SQL Server =>> you are safe. ADO will do the string conversion for you.
>> If you absolutely want to pass it as a string to SQL Server, read
>> http://www.karaszi.com/SQLServer/info_datetime.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Jerome" <Jommeke@.fake.com> wrote in message
>> news:ywKBf.209108$DX6.7008400@.phobos.telenet-ops.be...
>> Hallo,
>> I know a lot has already been told about date/time fields in a database
>> but still confuses me, specif when dealing with SQLserver(Express).
>> It seems that sqlserver only accepts the date in a "yyyyMMdd" format?
>> (difference between Express and MSDE2000A ?)
>> What is the one and only true way to deal with this problem in VB2005:
>> Local settings are Dutch (Belgium) ; thus date is in "dd/MM/yy" (or
>> perhaps dd/MM/yyyy) and time in "hh:mm:ss"
>> dim MyDateVar as string, MyIdVar as Integer
>> dim MyCommand = New Sqlcommand("",Connection)
>> MyCommand.commandtext = "Update MyTable Set MyDatefield = '" &
>> MyDateVar & "' Where MyIdField = " & MyIdVar = SomeIntegerValue
>> MyCommand.executenonquery
>> How to deal with the MyDateVar when:
>> 1.
>> The Variable comes from a textbox knowing that the user puts in
>> dd/MMyyyy
>> In this case there is no need to have the time with it.
>> 2.
>> The date comes from a datetimepicker control
>> (MyDateVar = DtPicker.Value)?
>> 3.
>> The date and time comes from the system
>> MyDateVar= Format(DateTime.Now, "yyyyMMdd") seems to work but
>> Format(DateTime.Now, "yyyyMMdd.hhmmss") gives a runtime error
>> So, any help and/or suggestion on this will be greatly appreciated.
>> Thanks and greetings to all
>> Jerome
>>
>>
>