Date/Time select Problem

Hi NG,

i've got a mystic problem i can't solve, perhaps one of you has a good idea...
i will explain with some code, so fo better understanding my probroblem:
i'll use asp, but for testing the syntax i use the MS Query Aanalyzer.

following Stored Procedure is ok and does it's job fine with "EXECUTE
IntDayView"

CREATE PROCEDURE dbo.IntDayView AS
SELECT Mitarbeiter.UserName, Interviews.Interview_Nr, Ergebnisse.Thema_Nr,
Ergebnisse.Bewertung, Ergebnisse.Bemerkung
FROM Interviews, Mitarbeiter, Ergebnisse
WHERE (Interviews.DatumZeit >=convert(datetime,'2004-02-10 00:00:00',120)
AND Interviews.DatumZeit <=convert(datetime,'2004-05-16 23:59:59',120)
AND Interviews.User_Nr=Mitarbeiter.User_Nr
AND Interviews.Interview_Nr=Ergebnisse.Interview_Nr)

but now, i want to have variables for the Date string, so i tried following
syntax:

CREATE PROCEDURE dbo.IntDayView @.DayViewf DATETIME,@.DayViewl DATETIME AS
SELECT Mitarbeiter.UserName, Interviews.Interview_Nr, Ergebnisse.Thema_Nr,
Ergebnisse.Bewertung, Ergebnisse.Bemerkung
FROM Interviews, Mitarbeiter, Ergebnisse
WHERE (Interviews.DatumZeit >=@.DayViewf
AND Interviews.DatumZeit <=@.DayViewl
AND Interviews.User_Nr=Mitarbeiter.User_Nr
AND Interviews.Interview_Nr=Ergebnisse.Interview_Nr)
---
EXECUTE IntDayView
@.DayViewf=convert(datetime,'2004-02-10 00:00:00',120),
@.DayViewl=convert(datetime,'2004-05-16 23:59:59',120)

so i tried the convert in execute, and also in the Procedure, but both times an
error occures Error Nr. 156, or 8114,
so something with the convert is wrong but, also if i do it without convert it
does not work.

So how to select by time, if this doesn't work???
Any suggestions??

Please help me,
thanks

Jan SchmidtHi Jan,

I think that the first thing to point out is that SQL Server is
smarter than your average toaster, so it doesn't actually need you to
use the CONVERT function when specifying DATETIME values. As long as
you are using an accepted non-ambiguous format it should be fine. For
example, the following code works without error (also notice the use
of the BETWEEN operator, which you might find useful):

CREATE TABLE Test (my_id INT IDENTITY, my_date DATETIME NOT NULL)
GO

INSERT INTO Test VALUES ('2004-01-01')
INSERT INTO Test VALUES ('2003-01-01')
INSERT INTO Test VALUES ('2005-01-01')
INSERT INTO Test VALUES ('2004-06-01')
GO

CREATE PROCEDURE My_Proc
@.start_date DATETIME,
@.end_date DATETIME
AS
BEGIN
SELECT *
FROM Test
WHERE my_date BETWEEN @.start_date AND @.end_date
END
GO

EXEC My_Proc @.start_date = '2004-01-01', @.end_date = '2004-09-01'
GO

DROP PROCEDURE My_Proc
GO

DROP TABLE Test
GO

-Tom.

"Jan Schmidt" <histery@.gmx.net> wrote in message news:<2hh2keFcv2ooU1@.uni-berlin.de>...
> Hi NG,
> i've got a mystic problem i can't solve, perhaps one of you has a good idea...
> i will explain with some code, so fo better understanding my probroblem:
> i'll use asp, but for testing the syntax i use the MS Query Aanalyzer.
> following Stored Procedure is ok and does it's job fine with "EXECUTE
> IntDayView"
> CREATE PROCEDURE dbo.IntDayView AS
> SELECT Mitarbeiter.UserName, Interviews.Interview_Nr, Ergebnisse.Thema_Nr,
> Ergebnisse.Bewertung, Ergebnisse.Bemerkung
> FROM Interviews, Mitarbeiter, Ergebnisse
> WHERE (Interviews.DatumZeit >=convert(datetime,'2004-02-10 00:00:00',120)
> AND Interviews.DatumZeit <=convert(datetime,'2004-05-16 23:59:59',120)
> AND Interviews.User_Nr=Mitarbeiter.User_Nr
> AND Interviews.Interview_Nr=Ergebnisse.Interview_Nr)
> but now, i want to have variables for the Date string, so i tried following
> syntax:
> CREATE PROCEDURE dbo.IntDayView @.DayViewf DATETIME,@.DayViewl DATETIME AS
> SELECT Mitarbeiter.UserName, Interviews.Interview_Nr, Ergebnisse.Thema_Nr,
> Ergebnisse.Bewertung, Ergebnisse.Bemerkung
> FROM Interviews, Mitarbeiter, Ergebnisse
> WHERE (Interviews.DatumZeit >=@.DayViewf
> AND Interviews.DatumZeit <=@.DayViewl
> AND Interviews.User_Nr=Mitarbeiter.User_Nr
> AND Interviews.Interview_Nr=Ergebnisse.Interview_Nr)
> ---
> EXECUTE IntDayView
> @.DayViewf=convert(datetime,'2004-02-10 00:00:00',120),
> @.DayViewl=convert(datetime,'2004-05-16 23:59:59',120)
> so i tried the convert in execute, and also in the Procedure, but both times an
> error occures Error Nr. 156, or 8114,
> so something with the convert is wrong but, also if i do it without convert it
> does not work.
> So how to select by time, if this doesn't work???
> Any suggestions??
> Please help me,
> thanks
> Jan Schmidt|||Thomas R. Hummel (tom_hummel@.hotmail.com) writes:
> I think that the first thing to point out is that SQL Server is
> smarter than your average toaster, so it doesn't actually need you to
> use the CONVERT function when specifying DATETIME values. As long as
> you are using an accepted non-ambiguous format it should be fine. For
> example, the following code works without error (also notice the use
> of the BETWEEN operator, which you might find useful):
> CREATE TABLE Test (my_id INT IDENTITY, my_date DATETIME NOT NULL)
> GO
> INSERT INTO Test VALUES ('2004-01-01')

But that is not an unambiguous format. This format fails with SET
DATEFORMAT DMY or SET LANGUAGE GERMAN. There are two safe dateformats:

YYYYMMDD and YYYY-MM-DDTHH:MM:SS[.mmm] T in the latter formats stands
for itself.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||[posted and mailed, please reply in news]

Jan Schmidt (histery@.gmx.net) writes:
> EXECUTE IntDayView
> @.DayViewf=convert(datetime,'2004-02-10 00:00:00',120),
> @.DayViewl=convert(datetime,'2004-05-16 23:59:59',120)
> so i tried the convert in execute, and also in the Procedure, but both
> times an error occures Error Nr. 156, or 8114, so something with the
> convert is wrong but, also if i do it without convert it does not work.

You cannot pass expressions as parameters when you call stored procedure.
You need to do:

DECLARE @.d1 datetime,
@.d2 datetime
SELECT @.d1 = convert(datetime,'2004-02-10 00:00:00',120),
@.d2 = convert(datetime,'2004-02-10 23:59:59',120)
EXEC IntDayView @.d1, @.d2

Or:

EXEC IntDayView '2004-02-10T00:00:00', '2004-02-10T23:59:59'

I would suggest that you rewrite the procedure as:

WHERE (Interviews.DatumZeit >= @.DayViewf
AND Interviews.DatumZeit < @.DayViewl

Now you can say:

EXEC '20040210', '20040211'

Please see my reply to Thomas Hummel about date formats.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp