plz tell me how can solve this type error

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 2005 Forums

Transact-SQL (2005)

plz tell me how can solve this type error

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-29 : 10:19:04

deepak writes "i am working on VB application and using sql server 2005 as data base so i get this type error

"TO_CHAR' is not a recognized built-in function name"

my query is
UPDATE DER_SAUDA_BOOK
SET STT_TAX = ROUND(TRD_QTY*TRD_PRICE*0.017*.01,2),E_I_FLAG = 'M'
WHERE TRD_INST_TYPE IN ('FUTIDX', 'FUTSTK')
AND TRD_BUY_SELL = 2 AND TO_CHAR(TRD_ENTER_DATE_TIME,'RRRRMMDD')='20070125';

plz tell me what i do.

thanks"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-29 : 10:29:55

UPDATE DER_SAUDA_BOOK
SET STT_TAX = ROUND(TRD_QTY*TRD_PRICE*0.017*.01,2),E_I_FLAG = 'M'
WHERE TRD_INST_TYPE IN ('FUTIDX', 'FUTSTK')
AND TRD_BUY_SELL = 2 AND convert(varchar, TRD_ENTER_DATE_TIME, 112) = '20070125'

But if TRD_ENTER_DATE_TIME already is datetime, just write

UPDATE DER_SAUDA_BOOK
SET STT_TAX = ROUND(TRD_QTY*TRD_PRICE*0.017*.01,2),E_I_FLAG = 'M'
WHERE TRD_INST_TYPE IN ('FUTIDX', 'FUTSTK')
AND TRD_BUY_SELL = 2 AND TRD_ENTER_DATE_TIME = '20070125'

Peter Larsson
Helsingborg, Sweden

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2007-01-29 : 13:09:46

Sounds like you've inherited some code from a different database system (like Oracle?) which uses TO_DATE.

The trick is to consider whether you want to include rows where trd_enter_date_time is later in the day of the 25th too. So you might want to look for rows where trd_enter_date_time >= '20070125' and trd_enter_date_time < '20070126'

Rob Farley
President - Adelaide SQL Server User Group
http://msmvps.com/blogs/robfarley

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources