We help IT Professionals succeed at work.

how to convert this Oracle sql statement to that of MS SQL

i'm not very versed on SQL server...can someone help me convert the sql statement below to something that MS SQL server would understand? right now im using Oracle.....

select my_table.*, my_doc_search.doc_type from my_table, my_doc_search where my_table.idx_id=my_doc_search.doc_id and (( TRUNC(my_table.invoice_date,'DD') <> TO_DATE('2008-01-28','YYYY-MM-DD') ) ) order by my_doc_search.doc_type

thanks in advance...
Comment
Watch Question

Commented:
It looks like the only thing you need to changes is:
(( TRUNC(my_table.invoice_date,'DD') <> TO_DATE('2008-01-28','YYYY-MM-DD') ) )

I assume that TRUNC is removing the time from a datetime field, and To_Date is converting a char to a date, both of these can be done using convert, easiest is probably.

CONVERT(varchar(10), my_table.invoice_Date, 112) <> '20080128'

Author

Commented:
hi JimFive thanks for replying....

may i know what the "112" is for?

thanks!

Commented:
112 indicates the date format of yyyymmdd
anything over 100 indicates to include the century. The remaining numbers indicate which date format to use. If you have SQL Server Books online installed you can look up CONVERT to get the list of codes.

I used 112 because it is the ISO standard format and there doesn't seem to be a code for yyyy-mm-dd that doesn't include the time.
--
JimFive

Explore More ContentExplore courses, solutions, and other research materials related to this topic.