SQL 2005 - Syntax error converting datetime from character string.

Please note, the SQL is handled dynamically by the SQL Server, therefore some things in my WHERE clause will look odd to you, please disregard them.

I am getting the following error with my SQL Query below (in Snippet):

Msg 241, Level 16, State 1, Line 39
Syntax error converting datetime from character string.
/* PILog */
SET NOCOUNT ON 
--need to convert a single quote enclosed, comm-separated list to a table to build a join on
-- tol filter values
 
DECLARE @groupby1 varchar(60)
SET @groupby1 = convert(varchar(60), 'Patient Name')
 
CREATE TABLE #listtable ( value VARCHAR(25) )
 
DECLARE @csList VARCHAR(500)
 
SET @csList = NULL
 
IF ISNULL(@csList , '') <> '' 
   BEGIN
         DECLARE
                 @i INT ,
                 @j INT ,
                 @tempstr VARCHAR(25)	
         SET @csList = @csList + ','
         SET @i = 1
         SET @j = 1
         WHILE @j <> 0
               BEGIN
                     SET @j = CHARINDEX(',' , @csList , ( @i ))
                     IF @j = 0 
                        BREAK
                     SET @tempstr = SUBSTRING(@csList , @i , ( @j - @i ))
                     INSERT INTO
                        #ListTable ( value )
                        SELECT
                            ( @tempstr )
                     SET @i = @j + 1
               END
   END
--- End of first filter table block
 
SELECT
    	CASE al.action
    	  WHEN 1 THEN 'Add'
    	  WHEN 2 THEN 'Update'
    	  WHEN 3 THEN 'Delete'
    	  WHEN 4 THEN 'Print'
    	  ELSE 'Other'
    	END AS ActionDesc ,
    	mod.description AS ModuleDesc ,
    	act.description AS ActivityDesc ,
    	al.FunctionName ,
    	al.TableName ,
    	al.RecordID ,
    	al.Value1 AS Info1 ,
    	al.value2 AS Info2 ,
    	al.created AS LogEntryDate ,
    	al.Createdby AS Username ,
    	pp.last + ', ' + pp.first + ' ' + ISNULL(pp.middle , '') AS PatientName ,
    	pp.patientID AS PatientID ,
	CASE @groupby1
		WHEN 'Patient Name' THEN pp.last + ', ' + pp.first + ' ' + ISNULL(pp.middle , '')
		WHEN 'TableName' THEN al.TableName
		WHEN 'UserName' THEN al.Createdby
		WHEN 'Log Entry Date' THEN al.created
		ELSE 'None'
	END AS GroupBy
INTO
    	#T1
FROM
    	activitylog al
    	LEFT JOIN (SELECT * FROM medlists WHERE tablename = 'ActivityLogModules') mod ON al.modulemlc = mod.joinID
    	LEFT JOIN (SELECT * FROM medlists WHERE tablename = 'ActivityLogActivities') act ON al.activityMLC = act.joinID AND al.modulemlc = act.otherlong
    	INNER JOIN patientprofile pp ON al.patientprofileID = pp.patientprofileID
 
WHERE
    	mod.joinID IN ( 1 , 2 , 4 )
    	AND -- Filter on activity log entry create date
	(
	(al.Created >= ISNULL(NULL,'1/1/1900') AND al.Created < dateadd(d,1,ISNULL(NULL,'1/1/3000')))
	)
    	AND  --Filter on patient
	(
	(NULL IS NOT NULL AND al.PatientProfileId IN (NULL)) OR
	(NULL IS NULL)
	)
    	AND  --Filter on Action
	(
	(NULL IS NOT NULL AND al.action IN (NULL)) OR
	(NULL IS NULL)
	)
    	AND  --Filter on Module
	(
	(Null IS NOT NULL AND mod.joinID IN (Null)) OR
	(Null IS NULL)
	)
 
--If we had a string to parse into our table then we need to join on it
--If not, then just return all of the main table 
 
IF ISNULL(@csList , '') <> '' 
   BEGIN 
         SELECT
            	t.ActionDesc ,
            	t.ModuleDesc ,
            	t.ActivityDesc ,
            	t.FunctionName ,
            	t.Tablename ,
            	t.RecordID ,
            	t.Info1 ,
            	t.Info2 ,
            	t.LogEntryDate ,
            	t.Username ,
            	t.PatientName ,
            	t.PatientId ,
				t.Groupby
         FROM
            	#T1 t
            	INNER JOIN #listtable lt ON t.username = lt.value
         ORDER BY
            	LogEntryDate    
   END
ELSE 
   BEGIN
         SELECT
           	t.ActionDesc ,
           	t.ModuleDesc ,
            	t.ActivityDesc ,
            	t.FunctionName ,
            	t.Tablename ,
            	t.RecordID ,
            	t.Info1 ,
            	t.Info2 ,
            	t.LogEntryDate ,
            	t.Username ,
            	t.PatientName ,
            	t.PatientId ,
				t.Groupby
         FROM
            	#T1 t
         ORDER BY
            	LogEntryDate
   END
 
DROP TABLE #T1 , #listtable

Open in new window

LVL 7
Jeff SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
Think it is because of this statement:

CASE @groupby1
                WHEN 'Patient Name' THEN pp.last + ', ' + pp.first + ' ' + ISNULL(pp.middle , '')
                WHEN 'TableName' THEN al.TableName
                WHEN 'UserName' THEN al.Createdby
                WHEN 'Log Entry Date' THEN al.created
                ELSE 'None'
        END AS GroupBy

If first entry uses 'Log Entry Date' for example then created will be used as groupby making the data type datetime.  On next records, the name, tablename, username, 'None' would be invalid dates.

Try converting the created date to text.

e.g. CONVERT(VARCHAR, al.created, 121)


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JestersGrindCommented:
If a1.created is a datetime, you need to convert it to varchar like this:  CONVERT(VARCHAR, a1Created)

Greg


0
RiteshShahCommented:
try this



/* PILog */
SET NOCOUNT ON 
--need to convert a single quote enclosed, comm-separated list to a table to build a join on
-- tol filter values
 
DECLARE @groupby1 varchar(60)
SET @groupby1 = convert(varchar(60), 'Patient Name')
 
CREATE TABLE #listtable ( value VARCHAR(25) )
 
DECLARE @csList VARCHAR(500)
 
SET @csList = NULL
 
IF ISNULL(@csList , '') <> '' 
   BEGIN
         DECLARE
                 @i INT ,
                 @j INT ,
                 @tempstr VARCHAR(25)   
         SET @csList = @csList + ','
         SET @i = 1
         SET @j = 1
         WHILE @j <> 0
               BEGIN
                     SET @j = CHARINDEX(',' , @csList , ( @i ))
                     IF @j = 0 
                        BREAK
                     SET @tempstr = SUBSTRING(@csList , @i , ( @j - @i ))
                     INSERT INTO
                        #ListTable ( value )
                        SELECT
                            ( @tempstr )
                     SET @i = @j + 1
               END
   END
--- End of first filter table block
 
SELECT
        CASE al.action
          WHEN 1 THEN 'Add'
          WHEN 2 THEN 'Update'
          WHEN 3 THEN 'Delete'
          WHEN 4 THEN 'Print'
          ELSE 'Other'
        END AS ActionDesc ,
        mod.description AS ModuleDesc ,
        act.description AS ActivityDesc ,
        al.FunctionName ,
        al.TableName ,
        al.RecordID ,
        al.Value1 AS Info1 ,
        al.value2 AS Info2 ,
        al.created AS LogEntryDate ,
        al.Createdby AS Username ,
        pp.last + ', ' + pp.first + ' ' + ISNULL(pp.middle , '') AS PatientName ,
        pp.patientID AS PatientID ,
        CASE @groupby1
                WHEN 'Patient Name' THEN pp.last + ', ' + pp.first + ' ' + ISNULL(pp.middle , '')
                WHEN 'TableName' THEN al.TableName
                WHEN 'UserName' THEN al.Createdby
                WHEN 'Log Entry Date' THEN al.created
                ELSE 'None'
        END AS GroupBy
INTO
        #T1
FROM
        activitylog al
        LEFT JOIN (SELECT * FROM medlists WHERE tablename = 'ActivityLogModules') mod ON al.modulemlc = mod.joinID
        LEFT JOIN (SELECT * FROM medlists WHERE tablename = 'ActivityLogActivities') act ON al.activityMLC = act.joinID AND al.modulemlc = act.otherlong
        INNER JOIN patientprofile pp ON al.patientprofileID = pp.patientprofileID
 
WHERE
        mod.joinID IN ( 1 , 2 , 4 )
        AND -- Filter on activity log entry create date
        (
        (cast(al.Created as varchar) >= ISNULL(NULL,'1/1/1900') AND al.Created < dateadd(d,1,ISNULL(NULL,'1/1/3000')))
        )
        AND  --Filter on patient
        (
        (NULL IS NOT NULL AND al.PatientProfileId IN (NULL)) OR
        (NULL IS NULL)
        )
        AND  --Filter on Action
        (
        (NULL IS NOT NULL AND al.action IN (NULL)) OR
        (NULL IS NULL)
        )
        AND  --Filter on Module
        (
        (Null IS NOT NULL AND mod.joinID IN (Null)) OR
        (Null IS NULL)
        )
 
--If we had a string to parse into our table then we need to join on it
--If not, then just return all of the main table 
 
IF ISNULL(@csList , '') <> '' 
   BEGIN 
         SELECT
                t.ActionDesc ,
                t.ModuleDesc ,
                t.ActivityDesc ,
                t.FunctionName ,
                t.Tablename ,
                t.RecordID ,
                t.Info1 ,
                t.Info2 ,
                t.LogEntryDate ,
                t.Username ,
                t.PatientName ,
                t.PatientId ,
                                t.Groupby
         FROM
                #T1 t
                INNER JOIN #listtable lt ON t.username = lt.value
         ORDER BY
                LogEntryDate    
   END
ELSE 
   BEGIN
         SELECT
                t.ActionDesc ,
                t.ModuleDesc ,
                t.ActivityDesc ,
                t.FunctionName ,
                t.Tablename ,
                t.RecordID ,
                t.Info1 ,
                t.Info2 ,
                t.LogEntryDate ,
                t.Username ,
                t.PatientName ,
                t.PatientId ,
                                t.Groupby
         FROM
                #T1 t
         ORDER BY
                LogEntryDate
   END
 
DROP TABLE #T1 , #listtable

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.