We help IT Professionals succeed at work.

SQL 2005 - Syntax error converting datetime from character string.

425 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

Comment
Watch Question

Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.