Syntax error converting datetime from character string.

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
ReportsupportAbilityAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Patrick MatthewsCommented:
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
 
ReportsupportAbilityAuthor Commented:
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
 
Patrick MatthewsCommented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
 
udaya kumar laligondlaTechnical LeadCommented:
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
 
ReportsupportAbilityAuthor Commented:
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
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
 
ReportsupportAbilityAuthor Commented:
That didn't work either.
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
 
ReportsupportAbilityAuthor Commented:
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
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
 
ReportsupportAbilityAuthor Commented:
I am getting an error message:

Server: Msg 170, Level 15, State 1, Procedure CreateDataCases, Line 81
Line 81: Incorrect syntax near 'cast'.
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
Mark WillsTopic AdvisorCommented:
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

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Mark WillsTopic AdvisorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.