Solved

Syntax error converting datetime from character string.

Posted on 2009-04-06
17
287 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
That didn't work either.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 57

Expert Comment

by:Raja Jegan R
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now