Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Syntax error converting datetime from character string.

Posted on 2009-04-06
17
Medium Priority
?
298 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 93

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 93

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 375 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

636 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