Solved

SQL 2005 - Syntax error converting datetime from character string.

Posted on 2009-05-04
3
359 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
ID: 24297095
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
ID: 24297131
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
ID: 24297172
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

911 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

22 Experts available now in Live!

Get 1:1 Help Now