?
Solved

SQL 2005 - Syntax error converting datetime from character string.

Posted on 2009-05-04
3
Medium Priority
?
378 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 1000 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 1000 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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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

719 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