Solved

Syntax error converting datetime from character string.

Posted on 2009-04-06
17
293 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
[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
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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:udaya kumar laligondla
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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

728 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