Solved

SQL 2005 - Syntax error converting datetime from character string.

Posted on 2009-05-04
3
373 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 60

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mssql 7 32
SQL Query 9 30
SQL Server Agent "Access Denied" Error 3 39
Sql server query 8 21
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

710 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