Sql Server Query Date Issue

Question

This executes correctly: (It is weird that I needed to use '' by the date for it to actually execute)

DECLARE 
@cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.statcolumnname) FROM [85137_PHY_Long_PG] c FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

set @query = 'SELECT statdate, ' + @cols + ' from 
        (
            select statdate, statcolumnname, statcolumnvalue
            from [85137_PHY_Long_PG]
       ) x
        pivot 
        (
             min(statcolumnvalue)
            for statcolumnname in (' + @cols + ')
        ) p WHERE statdate BETWEEN ''2012-04-01 12:15:00'' AND ''2012-04-01 12:45:00''      ORDER BY statdate'

execute(@query)

Now I want to replace the dates with variables:

DECLARE 
@cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX),
@from  AS NVARCHAR(MAX),
@to  AS NVARCHAR(MAX);

set @from = '2012-04-01 12:15:00'
set @to = '2012-04-01 12:45:00'

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.statcolumnname) FROM [85137_PHY_Long_PG] c FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

set @query = 'SELECT statdate, ' + @cols + ' from 
        (
            select statdate, statcolumnname, statcolumnvalue
            from [85137_PHY_Long_PG]
       ) x
        pivot 
        (
             min(statcolumnvalue)
            for statcolumnname in (' + @cols + ')
        ) p WHERE statdate BETWEEN ''+@from+'' AND ''+@to+'' ORDER BY statdate'

execute(@query)

I get the following error:Conversion failed when converting character string to smalldatetime data type

Changing the where statement to the following:

WHERE statdate BETWEEN ''+convert(smalldatetime,@from)+'' AND ''+convert(smalldatetime,@to)+'' ORDER BY statdate'

Still gives me the same error, just can't seem to replace the dates as variables

I have tried it multiple ways to no avail, the sp_executesql seems to work quite well — Thomas, May 28 '12 at 13:09

Nikola Markovinović · Accepted Answer · 2012-05-28T13:45:43.383

1

'' is not weird; it is a notation that enables apostrophes inside varchars.

When concatenating make sure that you are not trying to concatenate anything other than (n)varchars and (n)chars because Sql Server will attempt to convert them to other datatypes; in your case, in smalldatetime. You might avoid this trouble by explicitly converting your parameter dates to nvarchars before/during concatenation, but better solution is to use sp_executesql and parameters.

If you leave parameters inside query:

set @query = 'SELECT statdate, ' + @cols + ' from 
    (
        select statdate, statcolumnname, statcolumnvalue
        from [85137_PHY_Long_PG]
   ) x
    pivot 
    (
         min(statcolumnvalue)
        for statcolumnname in (' + @cols + ')
    ) p WHERE statdate BETWEEN @from AND @to ORDER BY statdate'

You can execute it with parameters:

exec sp_executesql @query, N'@from datetime, @to datetime', @from=@from_variable, @to=@to_variable

Where @from_variable and @to_variable are datetime variables defined earlier in batch.

UPDATE:

If your ultimate goal is to wrap this code in stored procedure, here is a template:

create proc MyProc (@dateFrom smalldatetime, @dateTo smalldatetime)
as
DECLARE 
@cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.statcolumnname) 
  FROM [85137_PHY_Long_PG] c 
   FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query = 'SELECT statdate, ' + @cols + ' from 
    (
        select statdate, statcolumnname, statcolumnvalue
        from [85137_PHY_Long_PG]
   ) x
    pivot 
    (
         min(statcolumnvalue)
        for statcolumnname in (' + @cols + ')
    ) p WHERE statdate BETWEEN @from AND @to ORDER BY statdate'

exec sp_executesql @query, N'@from smalldatetime, @to smalldatetime', @from=@dateFrom, @to=@dateTo

edited May 28 '12 at 13:45

answered May 28 '12 at 12:36

Nikola Markovinović

18,963
5
46
51

Oh ok, cool. Got it working, thanks man.Can get confusing...next challenge is to convert it to a stored procedure. Anyone bored... – Thomas May 28 '12 at 13:05
Is it possible to pass the actual dates e.g. 2012-04-01 12;:15:00 as parameters. Not having to set them as illustrated. – Thomas May 28 '12 at 13:27
Yes, you can pass constants as parameters: `@from='2012-12-01 13:33:12'` – Nikola Markovinović May 28 '12 at 13:46
@Thomas I've added sample stored procedure to get you started. – Nikola Markovinović May 28 '12 at 13:47
Thank you Nikola, I really appreciate your help. Learning a lot here. – Thomas May 29 '12 at 07:31
Quick question, if I wanted to pass the table name as a parameter, how would I go about doing that? I tried adding it as [at]tableName varchar. Then doing the replacements like you did with the date variables. I also tried modifying the [at]query string by adding it as '+[at]tableName+' (so just as a normal parameter). Can't get it to work :\ (I needed to replace the @ sign with [at], stackoverflow is trying to pick it up as a user) – Thomas May 29 '12 at 08:04
@Thomas Sorry for the delay. As with @ cols, you need to wrap @ tableName in quotename() because your table name starts with digit which is syntactically illegal. Don't forget to use sp_executesql on your first query, the one that returns all distinct values in certain column from that same table. Please [check my answer](http://stackoverflow.com/questions/9812764/converting-rows-to-columns-sql-server-t-sql/9813578#9813578) to see how to simplify a bit creation of query. – Nikola Markovinović May 29 '12 at 14:00

Thomas · Answer 2 · 2012-05-31T09:22:26.027

Herewith the solution:

DECLARE 
@cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX),
@internal_fromdate  AS SMALLDATETIME,
@internal_todate  AS SMALLDATETIME;

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.statcolumnname) FROM [85137_PHY_Long_PG] c FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

set @internal_fromdate = '2012-04-01 12:15:00';
set @internal_todate = '2012-04-01 12:45:00';

set @query = 'SELECT statdate, ' + @cols + ' from 
        (
            select statdate, statcolumnname, statcolumnvalue
            from [85137_PHY_Long_PG]
       ) x
        pivot 
        (
             min(statcolumnvalue)
            for statcolumnname in (' + @cols + ')
        ) p  WHERE statdate BETWEEN @FromDate AND @ToDate ORDER BY statdate'

exec sp_executesql @query, N'@FromDate SMALLDATETIME, @ToDate SMALLDATETIME', @FromDate=@internal_fromdate, @ToDate=@internal_todate

UPDATE

Ok, I have tried the following variations:

create proc MyProc9 (@tableName varchar,@dateFrom smalldatetime, @dateTo smalldatetime)
AS
DECLARE 
@cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.statcolumnname) 
FROM [85137_PHY_Long_MP] c 
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query = 'SELECT statdate, ' + @cols + ' from 
(
    SELECT statdate, statcolumnname, statcolumnvalue
    from @table
) x
pivot 
(
     min(statcolumnvalue)
    for statcolumnname in (' + @cols + ')
) p WHERE statdate BETWEEN @from AND @to ORDER BY statdate'

exec sp_executesql @query, N'@table varchar,@from smalldatetime, @to smalldatetime', @table=@tableName,@from=@dateFrom, @to=@dateTo

Error: Must declare the table variable "@table".

Tried '+@tableName+' in the @query string: Incorrect syntax near '8'.

Tried '+QUOTENAME(@tableName)+' in the @query string: Invalid object name '8'.

Tried ['+@tableName+'] in the @query string: Invalid object name '8'.

Tried QUOTENAME(@table) in the @query string: Invalid object name '8'.

Tried [85137_PHY_Long_MP] in the @query string: Works correctly, just want to replace this.

Tried [@tableName] in the @query string: Invalid object name '@tableName'.

Tried @tableName in the @query string: Must declare the table variable "@tableName".

I don't understand how to resolve the problem

Sql Server Query Date Issue

2 Answers2