Solved

SQL 2005 - Syntax error converting datetime from character string.

Posted on 2009-05-04
3
357 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:Jeff S
3 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
Comment Utility
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
 
LVL 21

Assisted Solution

by:JestersGrind
JestersGrind earned 250 total points
Comment Utility
If a1.created is a datetime, you need to convert it to varchar like this:  CONVERT(VARCHAR, a1Created)

Greg


0
 
LVL 31

Expert Comment

by:RiteshShah
Comment Utility
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now