Having Problems with DATEDIFF in derived column expression

-

hi all

i''m using sql server 2008. 'm using derived column in package , have 2 date fields (dtstartdate,dtenddate) coming flat file source. used derived column difference between 2 dates , add new field. i'm using following function in expression part.

datediff("dd",dtstartdate,dtenddate) , reason highlighted in red , when clicked ok showing me following error message:

title: microsoft visual studio
------------------------------

error @ data flow task [compute inactive days [370]]: function "datediff" not support data type "dt_wstr" parameter number 2. type of parameter not implicitly cast compatible type function. perform operation, operand needs explicitly cast cast operator.

error @ data flow task [compute inactive days [370]]: evaluating function "datediff" failed error code 0xc0047089.

error @ data flow task [compute inactive days [370]]: computing expression "datediff("dd", dtwarrantstart,dtwarrantend )" failed error code 0xc00470c5. expression may have errors, such divide zero, cannot detected @ parse time, or there may out-of-memory error.

error @ data flow task [compute inactive days [370]]: expression "datediff("dd", dtwarrantstart,dtwarrantend )" on "output column "inactivedays" (384)" not valid.

error @ data flow task [compute inactive days [370]]: failed set property "expression" on "output column "inactivedays" (384)".

------------------------------
additional information:

exception hresult: 0xc0204006 (microsoft.sqlserver.dtspipelinewrap)

------------------------------
buttons:

ok
------------------------------

i checked data file , not see nulls or zero's in either of datefields. can please point me i'm going wrong?

then tried following in expression "(dtwarrantstart > 0 ? (dtstartdate - dtenddate) >= 0 ? (dtwarrantstart - dtwarrantend) : -1) : 0)" , still no luck.

thanks

sv


the date fields flat file unicode (dt_wstr) , need cast them datetime before applying datediff function. try this: datediff("dd",(dt_dbtimestamp)dtstartdate,(dt_dbtimestamp)dtenddate)

note: above expression not checking nulls or invlaid dates. in order handle these checks please modify expression


nitesh rai- please mark post answered if answers question




SQL Server  >  SQL Server Integration Services



Comments

Post a Comment

Popular posts from this blog

Motherboard replacement

-
running windows 10 technical preview, need replace motherboard can run the  sysprep  command restoring pc ? or there easier  or more preferred way ? thanks if replace motherboard within same family of chipsets (i.e. intel > intel or amd > amd), should okay restarting pc twice. carey frisch Windows 10 Insider Preview  >  Windows 10 Insider Preview General
Read more

Cannot create Full Text Search catalog after upgrading to V12 - Database is not fully started up or it is not in an ONLINE state

-
in order access new full text search capiblities of sql azure, upgraded our azure sql v12. (a day later), when try create new catalog: create fulltext catalog mycatalog default i following message: msg 9972, level 16, state 100, line 1 database not started or not in online state. try full-text ddl command again after database started , becomes online. hi scott, apologies inconvenience caused you. known issue on service side , fix going included in next update. specific case mitigated, should able use full-text search syntax now. thanks, mihaela Microsoft Azure  >  Azure SQL Database
Read more

Remote Desktop App - Error 0x207 or 0x607

-
hi all, i manage windows 2012 r2 network, including connection broker, session hosts , remote apps virtual servers.  i've used remote desktop app on own android phone, no issues @ - connects published gateway url , can login , access network. we looking move business onto nokia handsets ms operating system (currently on blackberry), not able connect via remote desktop app on nokia lumia handset. setup, per android config , brings remote apps , remote desktop screen, know can connect.  when select remote desktop, shows following process: initiating remote connection securing remote connection certificate can't verified - choose connect it digitally signed cert (digicert sha2), pc name different physical connection broker name (inherited domain name old gov project - can't publish publicly) initiating establishing securing connection error have been 0x207 getting 0x607 a bit baffled, work on android (and ipad @ home using app). assume ms use d...
Read more