format not recognized - SQL Server Forums

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

Transact-SQL (2008)

format not recognized

Author

Topic

slimjen
Yak Posting Veteran

61 Posts

Posted - 2012-07-16 : 13:54:13

Hi all,
I converted a query to pass thru and got this error:

odbc sql server driver[sql server] 'format' is not a recognized built in function

Here is the query:

SELECT dbo_tblDTFInvoice_test.txtINVOICENUM AS INVOICENUM,
 dbo_tblDTFInvoice_test.txtCARRIERID AS CARRIERID,
 dbo_tblDTFInvoice_test.txtCARRIERNAME AS CARRIERNAME,
 dbo_tblDTFInvoice_test.txtCARRIERACCTNUM AS CARRIERACCTNUM,
 Format([dteDATAENTDTE], "mm-dd-yyyy") AS DATAENTRYDATE,
 Format([dteINVOICEDTE], "mm-dd-yyyy") AS INVOICEDATE,
 Format([dteMONTHPAID], "mm-dd-yyyy") AS MONTHPAID,
 dbo_tblDTFInvoice_test.memCOMMENT AS COMMENT,
 dbo_tblDTFShipInfo_test.txtAWBNUM AS AWBNUM,
 Format([dteIMP/EXPENTDTE], "mm-dd-yyyy") AS SHIPDATE,
 Format([dteIMP/EXPENTDTE], "mmmm yyyy") AS MONTHPROCESSED,
 dbo_tblDTFShipInfo_test.txtSHIPTYPE AS SHIPTYPE,
 dbo_tblDTFShipInfo_test.numTOTALCHGS AS TOTALCHGS,
  dbo_tblDTFShipInfo_test.txtDESTNZIP AS RECIPIENTZIP,
 dbo_tblDTFShipInfo_test.numWEIGHT AS WEIGHT,
 [numTOTALCHGS]/[numWEIGHT] AS CostPerPound,
 dbo_tblDTFShipInfo_test.txtSERVICETYPE AS SERVICETYPE,
 dbo_tblDTFShipInfo_test.txtCOSTCENTER AS COSTCENTER,
FROM dbo_tblDTFInvoice_test 
INNER JOIN dbo_tblDTFShipInfo_test ON dbo_tblDTFInvoice_test.txtINVOICENUM = dbo_tblDTFShipInfo_test.txtINVOICENUM
ORDER BY dbo_tblDTFInvoice_test.txtINVOICENUM;

Can anyone help me?
Thanks

edit: reformatted to avoid long lines

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-16 : 13:57:15

What it says - format isn't an sql server function.
change
Format([dteIMP/EXPENTDTE],"mm-dd-yyyy")
to
convert(varchar(10),[dteIMP/EXPENTDTE],110)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.