Converting Datetime from Character String

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Converting Datetime from Character String

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2007-12-13 : 09:40:39

Hello All,

I wrote the below script to pull data from an Oracle 9i to Server 2000 via linked server. When I execute the script I get the following error code:

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

I will to pull date based on the SIGN_IN_TIME field. That field in Oracle is a DATE data type and is a datetime in SQL Server.

Does anyone know how to solve this problem? Thanks.

SCRIPT:
DECLARE @dtRptDateDetail datetime
set @dtRptdateDetail='12/12/2007'

DECLARE @dtRptDateDetailPlusOne datetime
SET @dtRptDateDetailPlusOne=dateadd(d,1,@dtRptDateDetail)

DECLARE @sqlstring nvarchar(4000)

--** DELETE POSSIBLE DUPLICATES **--
PRINT 'DELETING DUPLICATE DATA'
DELETE FROM IGS_VW_EVT_AGENT_PERF_APPL_2
WHERE RPT_DATE_DETAIL = @dtRptDateDetail

--** QUERY AND LOAD DATA **--
SET @sqlstring='INSERT INTO IGS_VW_EVT_AGENT_PERF_APPL_2 SELECT *, ''' + CONVERT(VARCHAR(12), @dtRptDateDetail, 20) + ''' as RPT_DATE_DETAIL, ''IGS_GA'' as SWITCH
FROM OPENQUERY(IGS_GA,''SELECT EAPA_SITE, EAPA_STAFF, EAPA_STAFF_VERSION, EAPA_SIGN_IN_TIME, EAPA_APPL, EAPA_APPL_VERSION, EAPA_IN_CALLS_HNDLD, EAPA_IN_CALL_HLD_TIME,
EAPA_IN_CALL_WRK_TIME, EAPA_IN_CALL_TLK_TIME, EAPA_LOAD_TIME, EAPA_AVG_TALK_TIME, EAPA_AVG_WRK_TIME, EAPA_AVG_HOLD_TIME, EAPA_AVG_HNDL_TIME, EAPA_CALLS_PER_HOUR
FROM IGS.VW_EVT_AGENT_PERF_APPL WHERE EAPA_SIGN_IN_TIME >= TO_DATE('''''+ CONVERT(VARCHAR(12),@dtRptDateDetail,101) + ''''',''''MM/DD/YYYY'''') AND
EAPA_SIGN_IN_TIME < TO_DATE('''''+ CONVERT(VARCHAR(12),@dtRptDateDetailplusone,101) + ''''',''''MM/DD/YYYY'''')'')'
print 'LOADING IGS_GA'

EXEC sp_executesql @sqlstring

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-12-13 : 09:54:04

On your delete, you need:


DELETE FROM IGS_VW_EVT_AGENT_PERF_APPL_2
WHERE RPT_DATE_DETAIL = CONVERT(VARCHAR(12),@dtRptDateDetail,101)

pras2007
Posting Yak Master

216 Posts

Posted - 2007-12-13 : 11:08:04

Thanks for the quick response, I tried your suggestion, but it did not work. Thanks once again.

Does anyone have any other suggestion? Thanks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-13 : 11:08:33

Why don' tyou use sp_executeSQL correctly and use parameters? Then none of this formatting is an issue. see:

http://technet.microsoft.com/en-us/library/ms188001.aspx

You should never concatenate data and variables into SQL statements, always simply use parameters when building dynamic strings. That way delimiting and formatting and all that is never an issue.

Even better, of course, is don't use dynamic sql at all -- I don't see any reason to use it there. You are making things much more comlicated on yourself by varying the columns returned in this result. A single stored proc should always return the same structure, and the front-end app (report, web page, etc) can determine which columns to display or show.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources