Solved

SQL 2005 - Syntax error converting datetime from character string.

Posted on 2009-05-04
3
367 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB.net Duplicating a table - primary key not created 3 31
Trouble with <> 2 20
Update one table with results from another table in SQL 6 22
Parse this column 6 25
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.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

840 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