Stored Proc SQL 2000 and Dates

19 May 2006 12:57 PM

steven scaife

I have written a stored procedure but i cant get it to function with dates
and i get the following error: Error converting data type nvarchar to
datetime.

If the stored proc had a sql string like

SELECT     T.Summary, T.Priority, T.DateRequested, T.AssignedTo,
P.ProblemType, T.Description, T.Location, P.ProblemCat, T.Status,
                      T.RequestedBy, T.Dept, T.Email, IPAddress, EmailSent,
P.ProblemType
FROM         Ticket T INNER JOIN
                      Problem P ON T.ProblemID = P.ProblemID
WHERE CAST(FLOOR(CAST(T.DateRequested AS float)) AS datetime) Between
CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
AND
CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)

Then it will return results, because the proc will take a couple of
arguments i have put the sql string within a varchar variable named @SQL
which iexecute at the end of the proc when it has been built. The code is
below. How can i correct my SP to take a date, any help, websites etc much
appreciated

Stored proc below

CREATE PROCEDURE usr_SP_SrchByTxt
@Summary varchar(255) = null,
@Description varchar(4000) = null,
@RequestedBy varchar(50) = null,
@Dept varchar(50) = null,
@DateReq datetime = null,
@AssignedTo varchar(50) = null,
@Status varchar(50) = null,
@Priority varchar(50) = null,
@ProbCat varchar(50) = null,
@ProbType varchar(50) = null
AS
DECLARE @SQL varchar(5000)

SELECT @SQL = 'SELECT     T.Summary, T.Priority, T.DateRequested,
T.AssignedTo, P.ProblemType, T.Description, T.Location, P.ProblemCat,
T.Status,
                      T.RequestedBy, T.Dept, T.Email, IPAddress, EmailSent,
P.ProblemType
FROM         Ticket T INNER JOIN
                      Problem P ON T.ProblemID = P.ProblemID
WHERE 1=1 '

/*
Because we are letting the user search on multiple items we need to
determine what paramaters are null and what are not then add in to the sql
query an appropriate where clause
*/
IF @Summary is not null
    BEGIN
        SELECT @SQL = @SQL + 'AND T.Summary like ''%' + @Summary + '%'' '
    END

IF @Description is not null
    BEGIN
        SELECT @SQL = @SQL + 'AND T.Description like ''%' + @Description + '%'' '
    END

IF @RequestedBy is not null
    BEGIN
        SELECT @SQL = @SQL + 'AND T.RequestedBy ''' + @RequestedBy + ''' '
    END

IF @Dept is not null
    BEGIN
        SELECT @SQL = @SQL + 'AND T.Dept = ''' + @Dept + ''' '
    END

IF @DateReq is not null
    BEGIN
        --SELECT @SQL = @SQL + 'AND T.DateRequested = convert(datetime, ''' +
@DateReq + ''', 102) '
        --SELECT @SQL = @SQL + 'AND T.DateRequested = DATEADD(dd, DATEDIFF(dd, 0,
' + @DateReq + '), 0) '
--        SELECT @SQL = @SQL + 'CAST(FLOOR(CAST(App.Date_Of_Travel AS float)) AS
datetime) Between CAST(FLOOR(CAST(' + @DateReq + ' AS float)) AS datetime)
AND CAST(FLOOR(CAST(' + @DateReq + ' AS float)) AS datetime) '
        SELECT @SQL = @SQL + 'CAST(FLOOR(CAST(T.DateRequested AS float)) AS
datetime) Between CAST(FLOOR(CAST(convert(datetime, ''' + @DateReq + ''',
102) AS float)) AS datetime) AND CAST(FLOOR(CAST(convert(datetime, ''' +
@DateReq + ''', 102) AS float)) AS datetime) '
    END

IF @AssignedTo is not null
    BEGIN
        SELECT @SQL = @SQL + 'AND T.AssignedTo = ''' + @AssignedTo + ''' '
    END

IF @Status is not null
    BEGIN
        SELECT @SQL = @SQL + 'AND T.Status = ''' + @Status + ''' '
    END

IF @Priority is not null
    BEGIN
        SELECT @SQL = @SQL + 'AND T.Priority = ''' + @Priority + ''' '
    END

IF @ProbCat is not null
    BEGIN
        SELECT @SQL = @SQL + 'AND P.ProbCat = ''' + @ProbCat + ''' '
    END

IF @ProbType is not null
    BEGIN
        SELECT @SQL = @SQL + 'AND P.ProbType = ''' + @ProbType + ''' '
    END

PRINT @SQL
exec (@SQL)
/*
SELECT     T.Summary, T.Priority, T.DateRequested, T.AssignedTo,
P.ProblemType, T.Description, T.Location, P.ProblemCat, T.Status,
                      T.RequestedBy, T.Dept, T.Email, IPAddress, EmailSent,
P.ProblemType
FROM         Ticket T INNER JOIN
                      Problem P ON T.ProblemID = P.ProblemID
WHERE CAST(FLOOR(CAST(T.DateRequested AS float)) AS datetime) Between
CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
AND
CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
*/
GO

19 May 2006 1:11 PM

Alejandro Mesa

Steven,

What is the meaning of this predicate?. Did you notice that the variable
@DateReq is being used in the begin_expression and end_expression of the
"between" operator?

WHERE
    CAST(FLOOR(CAST(T.DateRequested AS float)) AS datetime) Between
    CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
    AND
    CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)

If you are trying to find rows between two dates, then you can use:

declare @sd datetime
declare @ed datetime
....
where
   T.DateRequested >= convert(char(8), @sd, 112)
   and T.DateRequested < dateadd(day, 1, convert(char(8), @ed, 112))

AMB

Show quote

"steven scaife" wrote:

> I have written a stored procedure but i cant get it to function with dates
> and i get the following error: Error converting data type nvarchar to
> datetime.
>
> If the stored proc had a sql string like
>
> SELECT     T.Summary, T.Priority, T.DateRequested, T.AssignedTo,
> P.ProblemType, T.Description, T.Location, P.ProblemCat, T.Status,
>                       T.RequestedBy, T.Dept, T.Email, IPAddress, EmailSent,
> P.ProblemType
> FROM         Ticket T INNER JOIN
>                       Problem P ON T.ProblemID = P.ProblemID
> WHERE CAST(FLOOR(CAST(T.DateRequested AS float)) AS datetime) Between
> CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> AND
> CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
>
> Then it will return results, because the proc will take a couple of
> arguments i have put the sql string within a varchar variable named @SQL
> which iexecute at the end of the proc when it has been built. The code is
> below. How can i correct my SP to take a date, any help, websites etc much
> appreciated
>
> Stored proc below
>
> CREATE PROCEDURE usr_SP_SrchByTxt
> @Summary varchar(255) = null,
> @Description varchar(4000) = null,
> @RequestedBy varchar(50) = null,
> @Dept varchar(50) = null,
> @DateReq datetime = null,
> @AssignedTo varchar(50) = null,
> @Status varchar(50) = null,
> @Priority varchar(50) = null,
> @ProbCat varchar(50) = null,
> @ProbType varchar(50) = null
> AS
> DECLARE @SQL varchar(5000)
>
> SELECT @SQL = 'SELECT     T.Summary, T.Priority, T.DateRequested,
> T.AssignedTo, P.ProblemType, T.Description, T.Location, P.ProblemCat,
> T.Status,
>                       T.RequestedBy, T.Dept, T.Email, IPAddress, EmailSent,
> P.ProblemType
> FROM         Ticket T INNER JOIN
>                       Problem P ON T.ProblemID = P.ProblemID
> WHERE 1=1 '
>
> /*
> Because we are letting the user search on multiple items we need to
> determine what paramaters are null and what are not then add in to the sql
> query an appropriate where clause
> */
> IF @Summary is not null
>     BEGIN
>         SELECT @SQL = @SQL + 'AND T.Summary like ''%' + @Summary + '%'' '
>     END
>
> IF @Description is not null
>     BEGIN
>         SELECT @SQL = @SQL + 'AND T.Description like ''%' + @Description + '%'' '
>     END
>
> IF @RequestedBy is not null
>     BEGIN
>         SELECT @SQL = @SQL + 'AND T.RequestedBy ''' + @RequestedBy + ''' '
>     END
>
> IF @Dept is not null
>     BEGIN
>         SELECT @SQL = @SQL + 'AND T.Dept = ''' + @Dept + ''' '
>     END
>
> IF @DateReq is not null
>     BEGIN
>         --SELECT @SQL = @SQL + 'AND T.DateRequested = convert(datetime, ''' +
> @DateReq + ''', 102) '
>         --SELECT @SQL = @SQL + 'AND T.DateRequested = DATEADD(dd, DATEDIFF(dd, 0,
> ' + @DateReq + '), 0) '
> --        SELECT @SQL = @SQL + 'CAST(FLOOR(CAST(App.Date_Of_Travel AS float)) AS
> datetime) Between CAST(FLOOR(CAST(' + @DateReq + ' AS float)) AS datetime)
> AND CAST(FLOOR(CAST(' + @DateReq + ' AS float)) AS datetime) '
>         SELECT @SQL = @SQL + 'CAST(FLOOR(CAST(T.DateRequested AS float)) AS
> datetime) Between CAST(FLOOR(CAST(convert(datetime, ''' + @DateReq + ''',
> 102) AS float)) AS datetime) AND CAST(FLOOR(CAST(convert(datetime, ''' +
> @DateReq + ''', 102) AS float)) AS datetime) '
>     END
>
> IF @AssignedTo is not null
>     BEGIN
>         SELECT @SQL = @SQL + 'AND T.AssignedTo = ''' + @AssignedTo + ''' '
>     END
>
> IF @Status is not null
>     BEGIN
>         SELECT @SQL = @SQL + 'AND T.Status = ''' + @Status + ''' '
>     END
>
> IF @Priority is not null
>     BEGIN
>         SELECT @SQL = @SQL + 'AND T.Priority = ''' + @Priority + ''' '
>     END
>
> IF @ProbCat is not null
>     BEGIN
>         SELECT @SQL = @SQL + 'AND P.ProbCat = ''' + @ProbCat + ''' '
>     END
>
> IF @ProbType is not null
>     BEGIN
>         SELECT @SQL = @SQL + 'AND P.ProbType = ''' + @ProbType + ''' '
>     END
>
> PRINT @SQL
> exec (@SQL)
> /*
> SELECT     T.Summary, T.Priority, T.DateRequested, T.AssignedTo,
> P.ProblemType, T.Description, T.Location, P.ProblemCat, T.Status,
>                       T.RequestedBy, T.Dept, T.Email, IPAddress, EmailSent,
> P.ProblemType
> FROM         Ticket T INNER JOIN
>                       Problem P ON T.ProblemID = P.ProblemID
> WHERE CAST(FLOOR(CAST(T.DateRequested AS float)) AS datetime) Between
> CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> AND
> CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> */
> GO
>

19 May 2006 1:17 PM

steven scaife

dates confuse me a bit in sql if i want to find records on a date i usually
do between date 00:00:00 and date 23:59:59, as far as i know using the
cast(floor( etc removes the time portion and allows only the date portion to
be used. But i tried several things as you will have seen they were
commented out. I'll give your code a try

thanks

Show quote

"Alejandro Mesa" wrote:

> Steven,
>
> What is the meaning of this predicate?. Did you notice that the variable
> @DateReq is being used in the begin_expression and end_expression of the
> "between" operator?
>
> WHERE
>     CAST(FLOOR(CAST(T.DateRequested AS float)) AS datetime) Between
>     CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
>     AND
>     CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
>
> If you are trying to find rows between two dates, then you can use:
>
> declare @sd datetime
> declare @ed datetime
> ...
> where
>    T.DateRequested >= convert(char(8), @sd, 112)
>    and T.DateRequested < dateadd(day, 1, convert(char(8), @ed, 112))
>
>
> AMB
>
> "steven scaife" wrote:
>
> > I have written a stored procedure but i cant get it to function with dates
> > and i get the following error: Error converting data type nvarchar to
> > datetime.
> >
> > If the stored proc had a sql string like
> >
> > SELECT     T.Summary, T.Priority, T.DateRequested, T.AssignedTo,
> > P.ProblemType, T.Description, T.Location, P.ProblemCat, T.Status,
> >                       T.RequestedBy, T.Dept, T.Email, IPAddress, EmailSent,
> > P.ProblemType
> > FROM         Ticket T INNER JOIN
> >                       Problem P ON T.ProblemID = P.ProblemID
> > WHERE CAST(FLOOR(CAST(T.DateRequested AS float)) AS datetime) Between
> > CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > AND
> > CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> >
> > Then it will return results, because the proc will take a couple of
> > arguments i have put the sql string within a varchar variable named @SQL
> > which iexecute at the end of the proc when it has been built. The code is
> > below. How can i correct my SP to take a date, any help, websites etc much
> > appreciated
> >
> > Stored proc below
> >
> > CREATE PROCEDURE usr_SP_SrchByTxt
> > @Summary varchar(255) = null,
> > @Description varchar(4000) = null,
> > @RequestedBy varchar(50) = null,
> > @Dept varchar(50) = null,
> > @DateReq datetime = null,
> > @AssignedTo varchar(50) = null,
> > @Status varchar(50) = null,
> > @Priority varchar(50) = null,
> > @ProbCat varchar(50) = null,
> > @ProbType varchar(50) = null
> > AS
> > DECLARE @SQL varchar(5000)
> >
> > SELECT @SQL = 'SELECT     T.Summary, T.Priority, T.DateRequested,
> > T.AssignedTo, P.ProblemType, T.Description, T.Location, P.ProblemCat,
> > T.Status,
> >                       T.RequestedBy, T.Dept, T.Email, IPAddress, EmailSent,
> > P.ProblemType
> > FROM         Ticket T INNER JOIN
> >                       Problem P ON T.ProblemID = P.ProblemID
> > WHERE 1=1 '
> >
> > /*
> > Because we are letting the user search on multiple items we need to
> > determine what paramaters are null and what are not then add in to the sql
> > query an appropriate where clause
> > */
> > IF @Summary is not null
> >     BEGIN
> >         SELECT @SQL = @SQL + 'AND T.Summary like ''%' + @Summary + '%'' '
> >     END
> >
> > IF @Description is not null
> >     BEGIN
> >         SELECT @SQL = @SQL + 'AND T.Description like ''%' + @Description + '%'' '
> >     END
> >
> > IF @RequestedBy is not null
> >     BEGIN
> >         SELECT @SQL = @SQL + 'AND T.RequestedBy ''' + @RequestedBy + ''' '
> >     END
> >
> > IF @Dept is not null
> >     BEGIN
> >         SELECT @SQL = @SQL + 'AND T.Dept = ''' + @Dept + ''' '
> >     END
> >
> > IF @DateReq is not null
> >     BEGIN
> >         --SELECT @SQL = @SQL + 'AND T.DateRequested = convert(datetime, ''' +
> > @DateReq + ''', 102) '
> >         --SELECT @SQL = @SQL + 'AND T.DateRequested = DATEADD(dd, DATEDIFF(dd, 0,
> > ' + @DateReq + '), 0) '
> > --        SELECT @SQL = @SQL + 'CAST(FLOOR(CAST(App.Date_Of_Travel AS float)) AS
> > datetime) Between CAST(FLOOR(CAST(' + @DateReq + ' AS float)) AS datetime)
> > AND CAST(FLOOR(CAST(' + @DateReq + ' AS float)) AS datetime) '
> >         SELECT @SQL = @SQL + 'CAST(FLOOR(CAST(T.DateRequested AS float)) AS
> > datetime) Between CAST(FLOOR(CAST(convert(datetime, ''' + @DateReq + ''',
> > 102) AS float)) AS datetime) AND CAST(FLOOR(CAST(convert(datetime, ''' +
> > @DateReq + ''', 102) AS float)) AS datetime) '
> >     END
> >
> > IF @AssignedTo is not null
> >     BEGIN
> >         SELECT @SQL = @SQL + 'AND T.AssignedTo = ''' + @AssignedTo + ''' '
> >     END
> >
> > IF @Status is not null
> >     BEGIN
> >         SELECT @SQL = @SQL + 'AND T.Status = ''' + @Status + ''' '
> >     END
> >
> > IF @Priority is not null
> >     BEGIN
> >         SELECT @SQL = @SQL + 'AND T.Priority = ''' + @Priority + ''' '
> >     END
> >
> > IF @ProbCat is not null
> >     BEGIN
> >         SELECT @SQL = @SQL + 'AND P.ProbCat = ''' + @ProbCat + ''' '
> >     END
> >
> > IF @ProbType is not null
> >     BEGIN
> >         SELECT @SQL = @SQL + 'AND P.ProbType = ''' + @ProbType + ''' '
> >     END
> >
> > PRINT @SQL
> > exec (@SQL)
> > /*
> > SELECT     T.Summary, T.Priority, T.DateRequested, T.AssignedTo,
> > P.ProblemType, T.Description, T.Location, P.ProblemCat, T.Status,
> >                       T.RequestedBy, T.Dept, T.Email, IPAddress, EmailSent,
> > P.ProblemType
> > FROM         Ticket T INNER JOIN
> >                       Problem P ON T.ProblemID = P.ProblemID
> > WHERE CAST(FLOOR(CAST(T.DateRequested AS float)) AS datetime) Between
> > CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > AND
> > CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > */
> > GO
> >

19 May 2006 1:29 PM

Alejandro Mesa

Steven,

> dates confuse me a bit in sql

May be these articles can help you to understand sql server datetime data
type.

The ultimate guide to the datetime datatypes
http://www.karaszi.com/SQLServer/info_datetime.asp

Should I use BETWEEN in my database queries?
http://www.aspfaq.com/show.asp?id=2280

AMB

Show quote

"steven scaife" wrote:

> dates confuse me a bit in sql if i want to find records on a date i usually
> do between date 00:00:00 and date 23:59:59, as far as i know using the
> cast(floor( etc removes the time portion and allows only the date portion to
> be used. But i tried several things as you will have seen they were
> commented out. I'll give your code a try
>
> thanks
>
> "Alejandro Mesa" wrote:
>
> > Steven,
> >
> > What is the meaning of this predicate?. Did you notice that the variable
> > @DateReq is being used in the begin_expression and end_expression of the
> > "between" operator?
> >
> > WHERE
> >     CAST(FLOOR(CAST(T.DateRequested AS float)) AS datetime) Between
> >     CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> >     AND
> >     CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> >
> > If you are trying to find rows between two dates, then you can use:
> >
> > declare @sd datetime
> > declare @ed datetime
> > ...
> > where
> >    T.DateRequested >= convert(char(8), @sd, 112)
> >    and T.DateRequested < dateadd(day, 1, convert(char(8), @ed, 112))
> >
> >
> > AMB
> >
> > "steven scaife" wrote:
> >
> > > I have written a stored procedure but i cant get it to function with dates
> > > and i get the following error: Error converting data type nvarchar to
> > > datetime.
> > >
> > > If the stored proc had a sql string like
> > >
> > > SELECT     T.Summary, T.Priority, T.DateRequested, T.AssignedTo,
> > > P.ProblemType, T.Description, T.Location, P.ProblemCat, T.Status,
> > >                       T.RequestedBy, T.Dept, T.Email, IPAddress, EmailSent,
> > > P.ProblemType
> > > FROM         Ticket T INNER JOIN
> > >                       Problem P ON T.ProblemID = P.ProblemID
> > > WHERE CAST(FLOOR(CAST(T.DateRequested AS float)) AS datetime) Between
> > > CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > > AND
> > > CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > >
> > > Then it will return results, because the proc will take a couple of
> > > arguments i have put the sql string within a varchar variable named @SQL
> > > which iexecute at the end of the proc when it has been built. The code is
> > > below. How can i correct my SP to take a date, any help, websites etc much
> > > appreciated
> > >
> > > Stored proc below
> > >
> > > CREATE PROCEDURE usr_SP_SrchByTxt
> > > @Summary varchar(255) = null,
> > > @Description varchar(4000) = null,
> > > @RequestedBy varchar(50) = null,
> > > @Dept varchar(50) = null,
> > > @DateReq datetime = null,
> > > @AssignedTo varchar(50) = null,
> > > @Status varchar(50) = null,
> > > @Priority varchar(50) = null,
> > > @ProbCat varchar(50) = null,
> > > @ProbType varchar(50) = null
> > > AS
> > > DECLARE @SQL varchar(5000)
> > >
> > > SELECT @SQL = 'SELECT     T.Summary, T.Priority, T.DateRequested,
> > > T.AssignedTo, P.ProblemType, T.Description, T.Location, P.ProblemCat,
> > > T.Status,
> > >                       T.RequestedBy, T.Dept, T.Email, IPAddress, EmailSent,
> > > P.ProblemType
> > > FROM         Ticket T INNER JOIN
> > >                       Problem P ON T.ProblemID = P.ProblemID
> > > WHERE 1=1 '
> > >
> > > /*
> > > Because we are letting the user search on multiple items we need to
> > > determine what paramaters are null and what are not then add in to the sql
> > > query an appropriate where clause
> > > */
> > > IF @Summary is not null
> > >     BEGIN
> > >         SELECT @SQL = @SQL + 'AND T.Summary like ''%' + @Summary + '%'' '
> > >     END
> > >
> > > IF @Description is not null
> > >     BEGIN
> > >         SELECT @SQL = @SQL + 'AND T.Description like ''%' + @Description + '%'' '
> > >     END
> > >
> > > IF @RequestedBy is not null
> > >     BEGIN
> > >         SELECT @SQL = @SQL + 'AND T.RequestedBy ''' + @RequestedBy + ''' '
> > >     END
> > >
> > > IF @Dept is not null
> > >     BEGIN
> > >         SELECT @SQL = @SQL + 'AND T.Dept = ''' + @Dept + ''' '
> > >     END
> > >
> > > IF @DateReq is not null
> > >     BEGIN
> > >         --SELECT @SQL = @SQL + 'AND T.DateRequested = convert(datetime, ''' +
> > > @DateReq + ''', 102) '
> > >         --SELECT @SQL = @SQL + 'AND T.DateRequested = DATEADD(dd, DATEDIFF(dd, 0,
> > > ' + @DateReq + '), 0) '
> > > --        SELECT @SQL = @SQL + 'CAST(FLOOR(CAST(App.Date_Of_Travel AS float)) AS
> > > datetime) Between CAST(FLOOR(CAST(' + @DateReq + ' AS float)) AS datetime)
> > > AND CAST(FLOOR(CAST(' + @DateReq + ' AS float)) AS datetime) '
> > >         SELECT @SQL = @SQL + 'CAST(FLOOR(CAST(T.DateRequested AS float)) AS
> > > datetime) Between CAST(FLOOR(CAST(convert(datetime, ''' + @DateReq + ''',
> > > 102) AS float)) AS datetime) AND CAST(FLOOR(CAST(convert(datetime, ''' +
> > > @DateReq + ''', 102) AS float)) AS datetime) '
> > >     END
> > >
> > > IF @AssignedTo is not null
> > >     BEGIN
> > >         SELECT @SQL = @SQL + 'AND T.AssignedTo = ''' + @AssignedTo + ''' '
> > >     END
> > >
> > > IF @Status is not null
> > >     BEGIN
> > >         SELECT @SQL = @SQL + 'AND T.Status = ''' + @Status + ''' '
> > >     END
> > >
> > > IF @Priority is not null
> > >     BEGIN
> > >         SELECT @SQL = @SQL + 'AND T.Priority = ''' + @Priority + ''' '
> > >     END
> > >
> > > IF @ProbCat is not null
> > >     BEGIN
> > >         SELECT @SQL = @SQL + 'AND P.ProbCat = ''' + @ProbCat + ''' '
> > >     END
> > >
> > > IF @ProbType is not null
> > >     BEGIN
> > >         SELECT @SQL = @SQL + 'AND P.ProbType = ''' + @ProbType + ''' '
> > >     END
> > >
> > > PRINT @SQL
> > > exec (@SQL)
> > > /*
> > > SELECT     T.Summary, T.Priority, T.DateRequested, T.AssignedTo,
> > > P.ProblemType, T.Description, T.Location, P.ProblemCat, T.Status,
> > >                       T.RequestedBy, T.Dept, T.Email, IPAddress, EmailSent,
> > > P.ProblemType
> > > FROM         Ticket T INNER JOIN
> > >                       Problem P ON T.ProblemID = P.ProblemID
> > > WHERE CAST(FLOOR(CAST(T.DateRequested AS float)) AS datetime) Between
> > > CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > > AND
> > > CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > > */
> > > GO
> > >

19 May 2006 1:27 PM

steven scaife

I am still having problems it appears to be because i am building a string
then executing it. I am still getting Error converting data type nvarchar to
datetime.

I have now tried combinations of what you've posted

SELECT @SQL = @SQL + 'AND T.DateRequested = convert(char(8), ''' + @DateReq
+ ''', 102) '

thanks for the help

Show quote

"Alejandro Mesa" wrote:

> Steven,
>
> What is the meaning of this predicate?. Did you notice that the variable
> @DateReq is being used in the begin_expression and end_expression of the
> "between" operator?
>
> WHERE
>     CAST(FLOOR(CAST(T.DateRequested AS float)) AS datetime) Between
>     CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
>     AND
>     CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
>
> If you are trying to find rows between two dates, then you can use:
>
> declare @sd datetime
> declare @ed datetime
> ...
> where
>    T.DateRequested >= convert(char(8), @sd, 112)
>    and T.DateRequested < dateadd(day, 1, convert(char(8), @ed, 112))
>
>
> AMB
>
> "steven scaife" wrote:
>
> > I have written a stored procedure but i cant get it to function with dates
> > and i get the following error: Error converting data type nvarchar to
> > datetime.
> >
> > If the stored proc had a sql string like
> >
> > SELECT     T.Summary, T.Priority, T.DateRequested, T.AssignedTo,
> > P.ProblemType, T.Description, T.Location, P.ProblemCat, T.Status,
> >                       T.RequestedBy, T.Dept, T.Email, IPAddress, EmailSent,
> > P.ProblemType
> > FROM         Ticket T INNER JOIN
> >                       Problem P ON T.ProblemID = P.ProblemID
> > WHERE CAST(FLOOR(CAST(T.DateRequested AS float)) AS datetime) Between
> > CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > AND
> > CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> >
> > Then it will return results, because the proc will take a couple of
> > arguments i have put the sql string within a varchar variable named @SQL
> > which iexecute at the end of the proc when it has been built. The code is
> > below. How can i correct my SP to take a date, any help, websites etc much
> > appreciated
> >
> > Stored proc below
> >
> > CREATE PROCEDURE usr_SP_SrchByTxt
> > @Summary varchar(255) = null,
> > @Description varchar(4000) = null,
> > @RequestedBy varchar(50) = null,
> > @Dept varchar(50) = null,
> > @DateReq datetime = null,
> > @AssignedTo varchar(50) = null,
> > @Status varchar(50) = null,
> > @Priority varchar(50) = null,
> > @ProbCat varchar(50) = null,
> > @ProbType varchar(50) = null
> > AS
> > DECLARE @SQL varchar(5000)
> >
> > SELECT @SQL = 'SELECT     T.Summary, T.Priority, T.DateRequested,
> > T.AssignedTo, P.ProblemType, T.Description, T.Location, P.ProblemCat,
> > T.Status,
> >                       T.RequestedBy, T.Dept, T.Email, IPAddress, EmailSent,
> > P.ProblemType
> > FROM         Ticket T INNER JOIN
> >                       Problem P ON T.ProblemID = P.ProblemID
> > WHERE 1=1 '
> >
> > /*
> > Because we are letting the user search on multiple items we need to
> > determine what paramaters are null and what are not then add in to the sql
> > query an appropriate where clause
> > */
> > IF @Summary is not null
> >     BEGIN
> >         SELECT @SQL = @SQL + 'AND T.Summary like ''%' + @Summary + '%'' '
> >     END
> >
> > IF @Description is not null
> >     BEGIN
> >         SELECT @SQL = @SQL + 'AND T.Description like ''%' + @Description + '%'' '
> >     END
> >
> > IF @RequestedBy is not null
> >     BEGIN
> >         SELECT @SQL = @SQL + 'AND T.RequestedBy ''' + @RequestedBy + ''' '
> >     END
> >
> > IF @Dept is not null
> >     BEGIN
> >         SELECT @SQL = @SQL + 'AND T.Dept = ''' + @Dept + ''' '
> >     END
> >
> > IF @DateReq is not null
> >     BEGIN
> >         --SELECT @SQL = @SQL + 'AND T.DateRequested = convert(datetime, ''' +
> > @DateReq + ''', 102) '
> >         --SELECT @SQL = @SQL + 'AND T.DateRequested = DATEADD(dd, DATEDIFF(dd, 0,
> > ' + @DateReq + '), 0) '
> > --        SELECT @SQL = @SQL + 'CAST(FLOOR(CAST(App.Date_Of_Travel AS float)) AS
> > datetime) Between CAST(FLOOR(CAST(' + @DateReq + ' AS float)) AS datetime)
> > AND CAST(FLOOR(CAST(' + @DateReq + ' AS float)) AS datetime) '
> >         SELECT @SQL = @SQL + 'CAST(FLOOR(CAST(T.DateRequested AS float)) AS
> > datetime) Between CAST(FLOOR(CAST(convert(datetime, ''' + @DateReq + ''',
> > 102) AS float)) AS datetime) AND CAST(FLOOR(CAST(convert(datetime, ''' +
> > @DateReq + ''', 102) AS float)) AS datetime) '
> >     END
> >
> > IF @AssignedTo is not null
> >     BEGIN
> >         SELECT @SQL = @SQL + 'AND T.AssignedTo = ''' + @AssignedTo + ''' '
> >     END
> >
> > IF @Status is not null
> >     BEGIN
> >         SELECT @SQL = @SQL + 'AND T.Status = ''' + @Status + ''' '
> >     END
> >
> > IF @Priority is not null
> >     BEGIN
> >         SELECT @SQL = @SQL + 'AND T.Priority = ''' + @Priority + ''' '
> >     END
> >
> > IF @ProbCat is not null
> >     BEGIN
> >         SELECT @SQL = @SQL + 'AND P.ProbCat = ''' + @ProbCat + ''' '
> >     END
> >
> > IF @ProbType is not null
> >     BEGIN
> >         SELECT @SQL = @SQL + 'AND P.ProbType = ''' + @ProbType + ''' '
> >     END
> >
> > PRINT @SQL
> > exec (@SQL)
> > /*
> > SELECT     T.Summary, T.Priority, T.DateRequested, T.AssignedTo,
> > P.ProblemType, T.Description, T.Location, P.ProblemCat, T.Status,
> >                       T.RequestedBy, T.Dept, T.Email, IPAddress, EmailSent,
> > P.ProblemType
> > FROM         Ticket T INNER JOIN
> >                       Problem P ON T.ProblemID = P.ProblemID
> > WHERE CAST(FLOOR(CAST(T.DateRequested AS float)) AS datetime) Between
> > CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > AND
> > CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > */
> > GO
> >

19 May 2006 1:33 PM

Alejandro Mesa

Steven,

You are trying to concatenate a string with a datetime value. Convert the
datetime value to char/varchar.

> SELECT @SQL = @SQL + 'AND T.DateRequested = convert(char(8), ''' + @DateReq
> + ''', 102) '

SELECT @SQL = @SQL + 'AND T.DateRequested = convert(char(8), ''' +
convert(varchar(25), @DateReq, 126) + ''', 102) '

AMB

Show quote

"steven scaife" wrote:

> I am still having problems it appears to be because i am building a string
> then executing it. I am still getting Error converting data type nvarchar to
> datetime.
>
> I have now tried combinations of what you've posted
>
> SELECT @SQL = @SQL + 'AND T.DateRequested = convert(char(8), ''' + @DateReq
> + ''', 102) '
>
>
> thanks for the help
>
> "Alejandro Mesa" wrote:
>
> > Steven,
> >
> > What is the meaning of this predicate?. Did you notice that the variable
> > @DateReq is being used in the begin_expression and end_expression of the
> > "between" operator?
> >
> > WHERE
> >     CAST(FLOOR(CAST(T.DateRequested AS float)) AS datetime) Between
> >     CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> >     AND
> >     CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> >
> > If you are trying to find rows between two dates, then you can use:
> >
> > declare @sd datetime
> > declare @ed datetime
> > ...
> > where
> >    T.DateRequested >= convert(char(8), @sd, 112)
> >    and T.DateRequested < dateadd(day, 1, convert(char(8), @ed, 112))
> >
> >
> > AMB
> >
> > "steven scaife" wrote:
> >
> > > I have written a stored procedure but i cant get it to function with dates
> > > and i get the following error: Error converting data type nvarchar to
> > > datetime.
> > >
> > > If the stored proc had a sql string like
> > >
> > > SELECT     T.Summary, T.Priority, T.DateRequested, T.AssignedTo,
> > > P.ProblemType, T.Description, T.Location, P.ProblemCat, T.Status,
> > >                       T.RequestedBy, T.Dept, T.Email, IPAddress, EmailSent,
> > > P.ProblemType
> > > FROM         Ticket T INNER JOIN
> > >                       Problem P ON T.ProblemID = P.ProblemID
> > > WHERE CAST(FLOOR(CAST(T.DateRequested AS float)) AS datetime) Between
> > > CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > > AND
> > > CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > >
> > > Then it will return results, because the proc will take a couple of
> > > arguments i have put the sql string within a varchar variable named @SQL
> > > which iexecute at the end of the proc when it has been built. The code is
> > > below. How can i correct my SP to take a date, any help, websites etc much
> > > appreciated
> > >
> > > Stored proc below
> > >
> > > CREATE PROCEDURE usr_SP_SrchByTxt
> > > @Summary varchar(255) = null,
> > > @Description varchar(4000) = null,
> > > @RequestedBy varchar(50) = null,
> > > @Dept varchar(50) = null,
> > > @DateReq datetime = null,
> > > @AssignedTo varchar(50) = null,
> > > @Status varchar(50) = null,
> > > @Priority varchar(50) = null,
> > > @ProbCat varchar(50) = null,
> > > @ProbType varchar(50) = null
> > > AS
> > > DECLARE @SQL varchar(5000)
> > >
> > > SELECT @SQL = 'SELECT     T.Summary, T.Priority, T.DateRequested,
> > > T.AssignedTo, P.ProblemType, T.Description, T.Location, P.ProblemCat,
> > > T.Status,
> > >                       T.RequestedBy, T.Dept, T.Email, IPAddress, EmailSent,
> > > P.ProblemType
> > > FROM         Ticket T INNER JOIN
> > >                       Problem P ON T.ProblemID = P.ProblemID
> > > WHERE 1=1 '
> > >
> > > /*
> > > Because we are letting the user search on multiple items we need to
> > > determine what paramaters are null and what are not then add in to the sql
> > > query an appropriate where clause
> > > */
> > > IF @Summary is not null
> > >     BEGIN
> > >         SELECT @SQL = @SQL + 'AND T.Summary like ''%' + @Summary + '%'' '
> > >     END
> > >
> > > IF @Description is not null
> > >     BEGIN
> > >         SELECT @SQL = @SQL + 'AND T.Description like ''%' + @Description + '%'' '
> > >     END
> > >
> > > IF @RequestedBy is not null
> > >     BEGIN
> > >         SELECT @SQL = @SQL + 'AND T.RequestedBy ''' + @RequestedBy + ''' '
> > >     END
> > >
> > > IF @Dept is not null
> > >     BEGIN
> > >         SELECT @SQL = @SQL + 'AND T.Dept = ''' + @Dept + ''' '
> > >     END
> > >
> > > IF @DateReq is not null
> > >     BEGIN
> > >         --SELECT @SQL = @SQL + 'AND T.DateRequested = convert(datetime, ''' +
> > > @DateReq + ''', 102) '
> > >         --SELECT @SQL = @SQL + 'AND T.DateRequested = DATEADD(dd, DATEDIFF(dd, 0,
> > > ' + @DateReq + '), 0) '
> > > --        SELECT @SQL = @SQL + 'CAST(FLOOR(CAST(App.Date_Of_Travel AS float)) AS
> > > datetime) Between CAST(FLOOR(CAST(' + @DateReq + ' AS float)) AS datetime)
> > > AND CAST(FLOOR(CAST(' + @DateReq + ' AS float)) AS datetime) '
> > >         SELECT @SQL = @SQL + 'CAST(FLOOR(CAST(T.DateRequested AS float)) AS
> > > datetime) Between CAST(FLOOR(CAST(convert(datetime, ''' + @DateReq + ''',
> > > 102) AS float)) AS datetime) AND CAST(FLOOR(CAST(convert(datetime, ''' +
> > > @DateReq + ''', 102) AS float)) AS datetime) '
> > >     END
> > >
> > > IF @AssignedTo is not null
> > >     BEGIN
> > >         SELECT @SQL = @SQL + 'AND T.AssignedTo = ''' + @AssignedTo + ''' '
> > >     END
> > >
> > > IF @Status is not null
> > >     BEGIN
> > >         SELECT @SQL = @SQL + 'AND T.Status = ''' + @Status + ''' '
> > >     END
> > >
> > > IF @Priority is not null
> > >     BEGIN
> > >         SELECT @SQL = @SQL + 'AND T.Priority = ''' + @Priority + ''' '
> > >     END
> > >
> > > IF @ProbCat is not null
> > >     BEGIN
> > >         SELECT @SQL = @SQL + 'AND P.ProbCat = ''' + @ProbCat + ''' '
> > >     END
> > >
> > > IF @ProbType is not null
> > >     BEGIN
> > >         SELECT @SQL = @SQL + 'AND P.ProbType = ''' + @ProbType + ''' '
> > >     END
> > >
> > > PRINT @SQL
> > > exec (@SQL)
> > > /*
> > > SELECT     T.Summary, T.Priority, T.DateRequested, T.AssignedTo,
> > > P.ProblemType, T.Description, T.Location, P.ProblemCat, T.Status,
> > >                       T.RequestedBy, T.Dept, T.Email, IPAddress, EmailSent,
> > > P.ProblemType
> > > FROM         Ticket T INNER JOIN
> > >                       Problem P ON T.ProblemID = P.ProblemID
> > > WHERE CAST(FLOOR(CAST(T.DateRequested AS float)) AS datetime) Between
> > > CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > > AND
> > > CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > > */
> > > GO
> > >

19 May 2006 1:38 PM

Alejandro Mesa

Steven,

BTW, in the sample code I posted, I am using style 112 instead 102. It is
not tie to any lenguage or "set dateformat" setting.

AMB

Show quote

"Alejandro Mesa" wrote:

> Steven,
>
> You are trying to concatenate a string with a datetime value. Convert the
> datetime value to char/varchar.
>
> > SELECT @SQL = @SQL + 'AND T.DateRequested = convert(char(8), ''' + @DateReq
> > + ''', 102) '
>
> SELECT @SQL = @SQL + 'AND T.DateRequested = convert(char(8), ''' +
> convert(varchar(25), @DateReq, 126) + ''', 102) '
>
>
> AMB
>
> "steven scaife" wrote:
>
> > I am still having problems it appears to be because i am building a string
> > then executing it. I am still getting Error converting data type nvarchar to
> > datetime.
> >
> > I have now tried combinations of what you've posted
> >
> > SELECT @SQL = @SQL + 'AND T.DateRequested = convert(char(8), ''' + @DateReq
> > + ''', 102) '
> >
> >
> > thanks for the help
> >
> > "Alejandro Mesa" wrote:
> >
> > > Steven,
> > >
> > > What is the meaning of this predicate?. Did you notice that the variable
> > > @DateReq is being used in the begin_expression and end_expression of the
> > > "between" operator?
> > >
> > > WHERE
> > >     CAST(FLOOR(CAST(T.DateRequested AS float)) AS datetime) Between
> > >     CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > >     AND
> > >     CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > >
> > > If you are trying to find rows between two dates, then you can use:
> > >
> > > declare @sd datetime
> > > declare @ed datetime
> > > ...
> > > where
> > >    T.DateRequested >= convert(char(8), @sd, 112)
> > >    and T.DateRequested < dateadd(day, 1, convert(char(8), @ed, 112))
> > >
> > >
> > > AMB
> > >
> > > "steven scaife" wrote:
> > >
> > > > I have written a stored procedure but i cant get it to function with dates
> > > > and i get the following error: Error converting data type nvarchar to
> > > > datetime.
> > > >
> > > > If the stored proc had a sql string like
> > > >
> > > > SELECT     T.Summary, T.Priority, T.DateRequested, T.AssignedTo,
> > > > P.ProblemType, T.Description, T.Location, P.ProblemCat, T.Status,
> > > >                       T.RequestedBy, T.Dept, T.Email, IPAddress, EmailSent,
> > > > P.ProblemType
> > > > FROM         Ticket T INNER JOIN
> > > >                       Problem P ON T.ProblemID = P.ProblemID
> > > > WHERE CAST(FLOOR(CAST(T.DateRequested AS float)) AS datetime) Between
> > > > CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > > > AND
> > > > CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > > >
> > > > Then it will return results, because the proc will take a couple of
> > > > arguments i have put the sql string within a varchar variable named @SQL
> > > > which iexecute at the end of the proc when it has been built. The code is
> > > > below. How can i correct my SP to take a date, any help, websites etc much
> > > > appreciated
> > > >
> > > > Stored proc below
> > > >
> > > > CREATE PROCEDURE usr_SP_SrchByTxt
> > > > @Summary varchar(255) = null,
> > > > @Description varchar(4000) = null,
> > > > @RequestedBy varchar(50) = null,
> > > > @Dept varchar(50) = null,
> > > > @DateReq datetime = null,
> > > > @AssignedTo varchar(50) = null,
> > > > @Status varchar(50) = null,
> > > > @Priority varchar(50) = null,
> > > > @ProbCat varchar(50) = null,
> > > > @ProbType varchar(50) = null
> > > > AS
> > > > DECLARE @SQL varchar(5000)
> > > >
> > > > SELECT @SQL = 'SELECT     T.Summary, T.Priority, T.DateRequested,
> > > > T.AssignedTo, P.ProblemType, T.Description, T.Location, P.ProblemCat,
> > > > T.Status,
> > > >                       T.RequestedBy, T.Dept, T.Email, IPAddress, EmailSent,
> > > > P.ProblemType
> > > > FROM         Ticket T INNER JOIN
> > > >                       Problem P ON T.ProblemID = P.ProblemID
> > > > WHERE 1=1 '
> > > >
> > > > /*
> > > > Because we are letting the user search on multiple items we need to
> > > > determine what paramaters are null and what are not then add in to the sql
> > > > query an appropriate where clause
> > > > */
> > > > IF @Summary is not null
> > > >     BEGIN
> > > >         SELECT @SQL = @SQL + 'AND T.Summary like ''%' + @Summary + '%'' '
> > > >     END
> > > >
> > > > IF @Description is not null
> > > >     BEGIN
> > > >         SELECT @SQL = @SQL + 'AND T.Description like ''%' + @Description + '%'' '
> > > >     END
> > > >
> > > > IF @RequestedBy is not null
> > > >     BEGIN
> > > >         SELECT @SQL = @SQL + 'AND T.RequestedBy ''' + @RequestedBy + ''' '
> > > >     END
> > > >
> > > > IF @Dept is not null
> > > >     BEGIN
> > > >         SELECT @SQL = @SQL + 'AND T.Dept = ''' + @Dept + ''' '
> > > >     END
> > > >
> > > > IF @DateReq is not null
> > > >     BEGIN
> > > >         --SELECT @SQL = @SQL + 'AND T.DateRequested = convert(datetime, ''' +
> > > > @DateReq + ''', 102) '
> > > >         --SELECT @SQL = @SQL + 'AND T.DateRequested = DATEADD(dd, DATEDIFF(dd, 0,
> > > > ' + @DateReq + '), 0) '
> > > > --        SELECT @SQL = @SQL + 'CAST(FLOOR(CAST(App.Date_Of_Travel AS float)) AS
> > > > datetime) Between CAST(FLOOR(CAST(' + @DateReq + ' AS float)) AS datetime)
> > > > AND CAST(FLOOR(CAST(' + @DateReq + ' AS float)) AS datetime) '
> > > >         SELECT @SQL = @SQL + 'CAST(FLOOR(CAST(T.DateRequested AS float)) AS
> > > > datetime) Between CAST(FLOOR(CAST(convert(datetime, ''' + @DateReq + ''',
> > > > 102) AS float)) AS datetime) AND CAST(FLOOR(CAST(convert(datetime, ''' +
> > > > @DateReq + ''', 102) AS float)) AS datetime) '
> > > >     END
> > > >
> > > > IF @AssignedTo is not null
> > > >     BEGIN
> > > >         SELECT @SQL = @SQL + 'AND T.AssignedTo = ''' + @AssignedTo + ''' '
> > > >     END
> > > >
> > > > IF @Status is not null
> > > >     BEGIN
> > > >         SELECT @SQL = @SQL + 'AND T.Status = ''' + @Status + ''' '
> > > >     END
> > > >
> > > > IF @Priority is not null
> > > >     BEGIN
> > > >         SELECT @SQL = @SQL + 'AND T.Priority = ''' + @Priority + ''' '
> > > >     END
> > > >
> > > > IF @ProbCat is not null
> > > >     BEGIN
> > > >         SELECT @SQL = @SQL + 'AND P.ProbCat = ''' + @ProbCat + ''' '
> > > >     END
> > > >
> > > > IF @ProbType is not null
> > > >     BEGIN
> > > >         SELECT @SQL = @SQL + 'AND P.ProbType = ''' + @ProbType + ''' '
> > > >     END
> > > >
> > > > PRINT @SQL
> > > > exec (@SQL)
> > > > /*
> > > > SELECT     T.Summary, T.Priority, T.DateRequested, T.AssignedTo,
> > > > P.ProblemType, T.Description, T.Location, P.ProblemCat, T.Status,
> > > >                       T.RequestedBy, T.Dept, T.Email, IPAddress, EmailSent,
> > > > P.ProblemType
> > > > FROM         Ticket T INNER JOIN
> > > >                       Problem P ON T.ProblemID = P.ProblemID
> > > > WHERE CAST(FLOOR(CAST(T.DateRequested AS float)) AS datetime) Between
> > > > CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > > > AND
> > > > CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > > > */
> > > > GO
> > > >

19 May 2006 2:20 PM

steven scaife

thanks i got it working it accepts a date like yyyy-mm-dd now which is good
enough for now, then i just gotta play around with it when i get time

thanks for the help

Show quote

"Alejandro Mesa" wrote:

> Steven,
>
> BTW, in the sample code I posted, I am using style 112 instead 102. It is
> not tie to any lenguage or "set dateformat" setting.
>
>
> AMB
>
> "Alejandro Mesa" wrote:
>
> > Steven,
> >
> > You are trying to concatenate a string with a datetime value. Convert the
> > datetime value to char/varchar.
> >
> > > SELECT @SQL = @SQL + 'AND T.DateRequested = convert(char(8), ''' + @DateReq
> > > + ''', 102) '
> >
> > SELECT @SQL = @SQL + 'AND T.DateRequested = convert(char(8), ''' +
> > convert(varchar(25), @DateReq, 126) + ''', 102) '
> >
> >
> > AMB
> >
> > "steven scaife" wrote:
> >
> > > I am still having problems it appears to be because i am building a string
> > > then executing it. I am still getting Error converting data type nvarchar to
> > > datetime.
> > >
> > > I have now tried combinations of what you've posted
> > >
> > > SELECT @SQL = @SQL + 'AND T.DateRequested = convert(char(8), ''' + @DateReq
> > > + ''', 102) '
> > >
> > >
> > > thanks for the help
> > >
> > > "Alejandro Mesa" wrote:
> > >
> > > > Steven,
> > > >
> > > > What is the meaning of this predicate?. Did you notice that the variable
> > > > @DateReq is being used in the begin_expression and end_expression of the
> > > > "between" operator?
> > > >
> > > > WHERE
> > > >     CAST(FLOOR(CAST(T.DateRequested AS float)) AS datetime) Between
> > > >     CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > > >     AND
> > > >     CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > > >
> > > > If you are trying to find rows between two dates, then you can use:
> > > >
> > > > declare @sd datetime
> > > > declare @ed datetime
> > > > ...
> > > > where
> > > >    T.DateRequested >= convert(char(8), @sd, 112)
> > > >    and T.DateRequested < dateadd(day, 1, convert(char(8), @ed, 112))
> > > >
> > > >
> > > > AMB
> > > >
> > > > "steven scaife" wrote:
> > > >
> > > > > I have written a stored procedure but i cant get it to function with dates
> > > > > and i get the following error: Error converting data type nvarchar to
> > > > > datetime.
> > > > >
> > > > > If the stored proc had a sql string like
> > > > >
> > > > > SELECT     T.Summary, T.Priority, T.DateRequested, T.AssignedTo,
> > > > > P.ProblemType, T.Description, T.Location, P.ProblemCat, T.Status,
> > > > >                       T.RequestedBy, T.Dept, T.Email, IPAddress, EmailSent,
> > > > > P.ProblemType
> > > > > FROM         Ticket T INNER JOIN
> > > > >                       Problem P ON T.ProblemID = P.ProblemID
> > > > > WHERE CAST(FLOOR(CAST(T.DateRequested AS float)) AS datetime) Between
> > > > > CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > > > > AND
> > > > > CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > > > >
> > > > > Then it will return results, because the proc will take a couple of
> > > > > arguments i have put the sql string within a varchar variable named @SQL
> > > > > which iexecute at the end of the proc when it has been built. The code is
> > > > > below. How can i correct my SP to take a date, any help, websites etc much
> > > > > appreciated
> > > > >
> > > > > Stored proc below
> > > > >
> > > > > CREATE PROCEDURE usr_SP_SrchByTxt
> > > > > @Summary varchar(255) = null,
> > > > > @Description varchar(4000) = null,
> > > > > @RequestedBy varchar(50) = null,
> > > > > @Dept varchar(50) = null,
> > > > > @DateReq datetime = null,
> > > > > @AssignedTo varchar(50) = null,
> > > > > @Status varchar(50) = null,
> > > > > @Priority varchar(50) = null,
> > > > > @ProbCat varchar(50) = null,
> > > > > @ProbType varchar(50) = null
> > > > > AS
> > > > > DECLARE @SQL varchar(5000)
> > > > >
> > > > > SELECT @SQL = 'SELECT     T.Summary, T.Priority, T.DateRequested,
> > > > > T.AssignedTo, P.ProblemType, T.Description, T.Location, P.ProblemCat,
> > > > > T.Status,
> > > > >                       T.RequestedBy, T.Dept, T.Email, IPAddress, EmailSent,
> > > > > P.ProblemType
> > > > > FROM         Ticket T INNER JOIN
> > > > >                       Problem P ON T.ProblemID = P.ProblemID
> > > > > WHERE 1=1 '
> > > > >
> > > > > /*
> > > > > Because we are letting the user search on multiple items we need to
> > > > > determine what paramaters are null and what are not then add in to the sql
> > > > > query an appropriate where clause
> > > > > */
> > > > > IF @Summary is not null
> > > > >     BEGIN
> > > > >         SELECT @SQL = @SQL + 'AND T.Summary like ''%' + @Summary + '%'' '
> > > > >     END
> > > > >
> > > > > IF @Description is not null
> > > > >     BEGIN
> > > > >         SELECT @SQL = @SQL + 'AND T.Description like ''%' + @Description + '%'' '
> > > > >     END
> > > > >
> > > > > IF @RequestedBy is not null
> > > > >     BEGIN
> > > > >         SELECT @SQL = @SQL + 'AND T.RequestedBy ''' + @RequestedBy + ''' '
> > > > >     END
> > > > >
> > > > > IF @Dept is not null
> > > > >     BEGIN
> > > > >         SELECT @SQL = @SQL + 'AND T.Dept = ''' + @Dept + ''' '
> > > > >     END
> > > > >
> > > > > IF @DateReq is not null
> > > > >     BEGIN
> > > > >         --SELECT @SQL = @SQL + 'AND T.DateRequested = convert(datetime, ''' +
> > > > > @DateReq + ''', 102) '
> > > > >         --SELECT @SQL = @SQL + 'AND T.DateRequested = DATEADD(dd, DATEDIFF(dd, 0,
> > > > > ' + @DateReq + '), 0) '
> > > > > --        SELECT @SQL = @SQL + 'CAST(FLOOR(CAST(App.Date_Of_Travel AS float)) AS
> > > > > datetime) Between CAST(FLOOR(CAST(' + @DateReq + ' AS float)) AS datetime)
> > > > > AND CAST(FLOOR(CAST(' + @DateReq + ' AS float)) AS datetime) '
> > > > >         SELECT @SQL = @SQL + 'CAST(FLOOR(CAST(T.DateRequested AS float)) AS
> > > > > datetime) Between CAST(FLOOR(CAST(convert(datetime, ''' + @DateReq + ''',
> > > > > 102) AS float)) AS datetime) AND CAST(FLOOR(CAST(convert(datetime, ''' +
> > > > > @DateReq + ''', 102) AS float)) AS datetime) '
> > > > >     END
> > > > >
> > > > > IF @AssignedTo is not null
> > > > >     BEGIN
> > > > >         SELECT @SQL = @SQL + 'AND T.AssignedTo = ''' + @AssignedTo + ''' '
> > > > >     END
> > > > >
> > > > > IF @Status is not null
> > > > >     BEGIN
> > > > >         SELECT @SQL = @SQL + 'AND T.Status = ''' + @Status + ''' '
> > > > >     END
> > > > >
> > > > > IF @Priority is not null
> > > > >     BEGIN
> > > > >         SELECT @SQL = @SQL + 'AND T.Priority = ''' + @Priority + ''' '
> > > > >     END
> > > > >
> > > > > IF @ProbCat is not null
> > > > >     BEGIN
> > > > >         SELECT @SQL = @SQL + 'AND P.ProbCat = ''' + @ProbCat + ''' '
> > > > >     END
> > > > >
> > > > > IF @ProbType is not null
> > > > >     BEGIN
> > > > >         SELECT @SQL = @SQL + 'AND P.ProbType = ''' + @ProbType + ''' '
> > > > >     END
> > > > >
> > > > > PRINT @SQL
> > > > > exec (@SQL)
> > > > > /*
> > > > > SELECT     T.Summary, T.Priority, T.DateRequested, T.AssignedTo,
> > > > > P.ProblemType, T.Description, T.Location, P.ProblemCat, T.Status,
> > > > >                       T.RequestedBy, T.Dept, T.Email, IPAddress, EmailSent,
> > > > > P.ProblemType
> > > > > FROM         Ticket T INNER JOIN
> > > > >                       Problem P ON T.ProblemID = P.ProblemID
> > > > > WHERE CAST(FLOOR(CAST(T.DateRequested AS float)) AS datetime) Between
> > > > > CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > > > > AND
> > > > > CAST(FLOOR(CAST(convert(datetime, @DateReq, 102) AS float)) AS datetime)
> > > > > */
> > > > > GO
> > > > >