Solved

Syntax error converting datetime from character string.

Posted on 2009-04-06
17
289 Views
Last Modified: 2012-05-06
Hello.  I am getting the following error message.  What am I doing wrong?  Thanks

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ALTER        PROCEDURE [dbo].[CreateDataCases]  --@fromdate datetime , @todate datetime
AS
BEGIN
DECLARE
@ROWID CHAR(10),
@SQL nvarchar(4000),
@SQLTEXT nvarchar(4000),
@RESULT CHAR(10),
@CNT int,
@caseID CHAR(10),
@CaseTypeID char(10),
@Parent_Case_Open_Date Datetime,
@Parent_Case_Close_Date Datetime,
@parentClassID CHAR(10),
@New_Open_Date CHAR(11),
@CaseCategoryID CHAR(10),
@OwnerID CHAR(10),
@New_Close_Date CHAR(11),
@RowID1 CHAR(10),
@NewCaseName CHAR(50)

/*** Get Cases to create new data case to ***/
 
DECLARE findCases_cursor CURSOR
FOR

select
cases.case_id as caseID
, cases.case_type_id as caseTypeID
, cases.open_date as Parent_Case_Open_Date
, cases.close_date as Parent_Case_Close_Date
from organization org
inner join collection_date cd_org on org.organization_id = cd_org.parent_id
inner join person p on p.person_id = cd_org.child_id
inner join participant_collection on p.person_id = participant_collection.child_id
and role_id = 'clientrole'
inner join contact on participant_collection.parent_id = contact_id
inner join contact_type on contact_type.contact_type_id = contact.contact_type_id
inner join cases on cases.parent_id = p.person_id
and cases.case_category_id = 'F00004GCQT'--Service case category
and cases.case_type_id = '_IQG000082'--Physical Therapy case type
--and cases.case_type_id in ('_IQG000084','_IQG000082','6WIQ000CFR','_IQG000086')
where p.person_id = 'V9B8000567'--Mary Breslin person
and org.organization_id = 'P3WD000F6P'--NY Physical Therapy Client Group organization
--and org.organization_id in ('P3WD000F6G','P3WD000F6P','P3WD000F76','P3WD000F80')
and contact_type.contact_type_id = 'F0000246LZ'--PT - Individual Therapy contact type
--and contact_type.contact_type_id in ('F0000246M0','F0000246LZ','F000024A93','F0000246NM')

--GROUP BY collection_date.child_id , collection_date.parent_id ,
--collection_date.start_date , collection_date.end_date,
--organization.organization_name

OPEN findCases_cursor

FETCH NEXT FROM findCases_cursor INTO @caseID, @Parent_Case_Open_Date, @Parent_Case_Close_Date, @caseTypeID

WHILE (@@FETCH_STATUS <> -1)
BEGIN
      IF (@@FETCH_STATUS <> -2) /*** Got One ***/
      BEGIN      
            
            INSERT INTO nextid_counter (next_when) VALUES(GETDATE())

            SELECT @OWNERID = 'PP'
            SELECT @ROWID = dbo.getID( ( SELECT SCOPE_IDENTITY() ) )
            SELECT @parentClassID = 'CASE'
                  
SET @NewCaseName = '2008 - Physical Therapy'

SELECT @New_Open_Date = (SELECT 'Parent_Case_Open_Date' = CASE WHEN @Parent_Case_Open_Date < '01-01-2008'
                THEN '01/01/08' ELSE @Parent_Case_Open_Date END)

SELECT @New_Close_Date = (SELECT 'Parent_Case_Close_Date' = CASE WHEN @Parent_Case_Close_Date > '12-31-2008'
                THEN '12/31/08' ELSE @Parent_Case_Close_Date END)

SET @CaseCategoryID = 'F00004GCQV'--Data case category

            SELECT @SQL = 'INSERT INTO [cases] ( [case_id],[parent_id],[parent_class_id],
                        [case_name],[open_date],[close_date],
                        [case_type_id],[case_category_id],[ts])'

            SELECT @SQL = @SQL + ' VALUES('''+ @ROWID + ''',''' + @caseID + ''',''' + @parentClassID + ''',''' +
                  @NewCaseName  + ''',''' +  @New_Open_Date  + ''',''' + @New_Close_Date + ''','''  +  
                  @CaseTypeID  + ''','''  +  @CaseCategoryID + ''',' + 'GETDATE() )'
 
            PRINT @SQL
            EXECUTE DynamicSQL @SQL

      EXECUTE RecordTransaction @ROWID, 'CASE', 'CENTRAL', 'INSERT', @SQL, '0000000000', NULL

END

FETCH NEXT FROM findCases_cursor INTO @caseID, @Parent_Case_Open_Date, @Parent_Case_Close_Date, @caseTypeID
END

CLOSE findCases_cursor
DEALLOCATE findCases_cursor

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
0
Comment
Question by:ReportsupportAbility
  • 5
  • 5
  • 3
  • +2
17 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24077208
Hello ReportsupportAbility,

Change:

SELECT @New_Open_Date = (SELECT 'Parent_Case_Open_Date' = CASE WHEN @Parent_Case_Open_Date < '01-01-2008'
                THEN '01/01/08' ELSE @Parent_Case_Open_Date END)

SELECT @New_Close_Date = (SELECT 'Parent_Case_Close_Date' = CASE WHEN @Parent_Case_Close_Date > '12-31-2008'
                THEN '12/31/08' ELSE @Parent_Case_Close_Date END)

to:

IF @Parent_Case_Open_Date < '01-01-2008'
      SET @New_Open_Date = '01/01/08'
ELSE
      SET @New_Open_Date = @Parent_Case_Open_Date

IF @Parent_Case_Close_Date > '12-31-2008'
      SET @New_Close_Date = '12/31/08'
ELSE
      SET @New_Close_Date = @Parent_Case_Close_Date

Regards,

Patrick
0
 

Author Comment

by:ReportsupportAbility
ID: 24077525
I changed it and I am still getting the same error message:

Server: Msg 241, Level 16, State 1, Procedure CreateDataCases, Line 69
Syntax error converting datetime from character string.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24077681
ReportsupportAbility,

The problem is that you declared @New_Open_Date and @New_Close_Date as varchar and not as datetime.

Why are you doing that?

In any event...

IF @Parent_Case_Open_Date < '01-01-2008'
      SET @New_Open_Date = '01/01/08'
ELSE
      SET @New_Open_Date = CONVERT(varchar, @Parent_Case_Open_Date, 1)

IF @Parent_Case_Close_Date > '12-31-2008'
      SET @New_Close_Date = '12/31/08'
ELSE
      SET @New_Close_Date = CONVERT(varchar, @Parent_Case_Close_Date, 1)

Regards,

Patrick
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24077764
This should help you out:

If you want to convert '-' to '/' use CONVERT function instead with appropriate styles.
SET @New_Open_Date = (SELECT 'Parent_Case_Open_Date' = CASE WHEN @Parent_Case_Open_Date < CONVERT(DATETIME,'01-01-2008', 110)
                THEN CONVERT(DATETIME,'01-01-08',110) ELSE @Parent_Case_Open_Date END)
 
SET @New_Close_Date = (SELECT 'Parent_Case_Close_Date' = CASE WHEN @Parent_Case_Close_Date > CONVERT(DATETIME,'12-31-2008', 110)
                THEN CONVERT(DATETIME,'12-31-08',110) ELSE @Parent_Case_Close_Date END)

Open in new window

0
 
LVL 12

Expert Comment

by:udayakumarlm
ID: 24077981
change all the date formats to YYYY-MM-DD format like
SET @New_Close_Date = '12/31/08' to SET @New_Close_Date = '2008/12/31'
0
 

Author Comment

by:ReportsupportAbility
ID: 24078196
I tried all the suggestions.  But none of them worked.  I even changed the @New_Open_Date and @New_Close_Date in the declared to datetime.  That didn't work either.  I even tried the following and it didn't work either:

SET @New_Open_Date = (SELECT CAST(@Parent_Case_Open_Date AS CHAR(11) ) )
SELECT @New_Open_Date = (SELECT 'Parent_Case_Open_Date' = CASE WHEN @New_Open_Date < '01-01-2008'
                THEN '01-01-2008' ELSE @New_Open_Date END)

SET @New_Close_Date = (SELECT CAST(@Parent_Case_Close_Date AS CHAR(11) ) )
SELECT @New_Close_Date = (SELECT 'Parent_Case_Close_Date' = CASE WHEN @New_Close_Date > '12-31-2008'
                THEN '12-31-2008' ELSE @New_Close_Date END)
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24078375
Ok.. try this one out:
SET @New_Open_Date = (SELECT 'Parent_Case_Open_Date' = CONVERT(CHAR,CASE WHEN @Parent_Case_Open_Date < CONVERT(DATETIME,'01-01-2008', 110)
                THEN CONVERT(DATETIME,'01-01-08',110) ELSE @Parent_Case_Open_Date END, 101))
 
SET @New_Close_Date = (SELECT 'Parent_Case_Close_Date' = CONVERT(CHAR,CASE WHEN @Parent_Case_Close_Date > CONVERT(DATETIME,'12-31-2008', 110)
                THEN CONVERT(DATETIME,'12-31-08',110) ELSE @Parent_Case_Close_Date END,101))

Open in new window

0
 

Author Comment

by:ReportsupportAbility
ID: 24078485
That didn't work either.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24078514
What error do you obtain when you used that Query.
Are you sure that you obtain error at that statement or in any other statement.
0
 

Author Comment

by:ReportsupportAbility
ID: 24078611
Server: Msg 241, Level 16, State 1, Procedure CreateDataCases, Line 51
Syntax error converting datetime from character string.

I am not sure on which statement I am obtaining the error message.  I looked at everything in the query and I can't figure it out.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24078741
From the error message you specified, its clear that the problem is with this part of the query:

Its the problem with your @sql statement.

Kindly try this one out.
SET @New_Open_Date = (SELECT 'Parent_Case_Open_Date' = CONVERT(CHAR,CASE WHEN @Parent_Case_Open_Date < CONVERT(DATETIME,'01-01-2008', 110)
                THEN CONVERT(DATETIME,'01-01-08',110) ELSE @Parent_Case_Open_Date END, 101))
 
SET @New_Close_Date = (SELECT 'Parent_Case_Close_Date' = CONVERT(CHAR,CASE WHEN @Parent_Case_Close_Date > CONVERT(DATETIME,'12-31-2008', 110)
                THEN CONVERT(DATETIME,'12-31-08',110) ELSE @Parent_Case_Close_Date END,101))
 
SET @CaseCategoryID = 'F00004GCQV'--Data case category
 
            SELECT @SQL = 'INSERT INTO [cases] ( [case_id],[parent_id],[parent_class_id],
                        [case_name],[open_date],[close_date],
                        [case_type_id],[case_category_id],[ts])'
 
            SELECT @SQL = @SQL + ' VALUES('''+ @ROWID + ''',''' + @caseID + ''',''' + @parentClassID + ''',''' +
                  @NewCaseName  + ''','cast('' +  @New_Open_Date  + '' as datetime)','cast('' + @New_Close_Date + '' as datetime)','''  +  
                  @CaseTypeID  + ''','''  +  @CaseCategoryID + ''',' + 'cast(''GETDATE()'' as datetime))'

Open in new window

0
 

Author Comment

by:ReportsupportAbility
ID: 24079334
I am getting an error message:

Server: Msg 170, Level 15, State 1, Procedure CreateDataCases, Line 81
Line 81: Incorrect syntax near 'cast'.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24079765
Small Mistake:
SET @New_Open_Date = (SELECT 'Parent_Case_Open_Date' = CONVERT(CHAR,CASE WHEN @Parent_Case_Open_Date < CONVERT(DATETIME,'01-01-2008', 110)
                THEN CONVERT(DATETIME,'01-01-08',110) ELSE @Parent_Case_Open_Date END, 101))
 
SET @New_Close_Date = (SELECT 'Parent_Case_Close_Date' = CONVERT(CHAR,CASE WHEN @Parent_Case_Close_Date > CONVERT(DATETIME,'12-31-2008', 110)
                THEN CONVERT(DATETIME,'12-31-08',110) ELSE @Parent_Case_Close_Date END,101))
 
SET @CaseCategoryID = 'F00004GCQV'--Data case category
 
            SELECT @SQL = 'INSERT INTO [cases] ( [case_id],[parent_id],[parent_class_id],
                        [case_name],[open_date],[close_date],
                        [case_type_id],[case_category_id],[ts])'
 
            SELECT @SQL = @SQL + ' VALUES('''+ @ROWID + ''',''' + @caseID + ''',''' + @parentClassID + ''',''' +
                  @NewCaseName  + ''','cast('' +  @New_Open_Date  + '' as datetime)','cast('' + @New_Close_Date + '' as datetime)','''  +  
                  @CaseTypeID  + ''','''  +  @CaseCategoryID + ''',' + 'cast('' + GETDATE() + '' as datetime))'

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24081785
If you want to keep varchar then use the ISO formats of yyyymmdd or yyyy-mm-dd (styles codes 112 and 120 respectively) and they can be dynamically understood as dates when used in a date context.

better to have the @date variables as a datetime, and use the convert function :


declare @parent_case_Open_Date datetime
declare @parent_case_Close_Date datetime
declare @New_Open_Date datetime
declare @New_Close_Date datetime

SET @New_Open_Date  = (SELECT CASE WHEN isnull(@Parent_Case_Open_Date,'') < '20080101' THEN '20080101' ELSE @Parent_Case_Open_Date END )
SET @New_Close_Date = (SELECT CASE WHEN isnull(@Parent_Case_Close_Date,'20990101') > '20081231' THEN '20081231' ELSE @Parent_Case_Close_Date END )

select @new_open_date, @new_close_date
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 125 total points
ID: 24292550
I think it is all to do with the unformatted date string and trying to dynamically convert.

Only two real solutions.  Use the convert to explicitly translate the character string, or use the implicitly understood ISO string of 'yyyymmdd'

Third solution is to match the string exactly with the default database language (ie date format), but normally asking for trouble if not explicitly managing.

Two parts to the problem.

1) setting up the @new_..._Date variables
2) decoding for the dynamic sql

Both parts require the same approach in so much as must convert to a recognisable date format. To spell it out more fully than my previous posting :




-- ...etc
declare @parent_case_Open_Date datetime
declare @parent_case_Close_Date datetime
declare @New_Open_Date datetime
declare @New_Close_Date datetime
-- ...etc
 
 
SET @New_Open_Date  = (SELECT CASE WHEN isnull(@Parent_Case_Open_Date,'') < '20080101' THEN '20080101' ELSE @Parent_Case_Open_Date END )
SET @New_Close_Date = (SELECT CASE WHEN isnull(@Parent_Case_Close_Date,'20990101') > '20081231' THEN '20081231' ELSE @Parent_Case_Close_Date END )
SET @CaseCategoryID = 'F00004GCQV'--Data case category
 
SELECT @SQL = 'INSERT INTO [cases] ( [case_id],[parent_id],[parent_class_id],[case_name],[open_date],[close_date],[case_type_id],[case_category_id],[ts])' 
 
SELECT @SQL = @SQL + ' VALUES ('''+ @ROWID + ''',''' + @caseID + ''',''' + @parentClassID + ''',''' + @NewCaseName  + 
                     ''','''+convert(varchar,@New_Open_Date,120)+
                     ''','''+convert(varchar,@New_Close_Date,120)+
                     ''','''  + @CaseTypeID  + ''','''  +  @CaseCategoryID + ''',GETDATE())'
 
print @sql

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24295651
ReportsupportAbility,

I can assure you that there is at least one solution that did work given the datatypes provided. However fields with a "ID" suffix tend to indicate numerics and use fixed length CHAR variables to populate. Be that as it may, the above converts the datetimes quite happily, and in test mode on my machine (not knowing datatypes, other than indicated by the variables) inserts data as expected. All too late I guess.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Read about achieving the basic levels of HRIS security in the workplace.
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

778 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