• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

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...
0
ayeen
Asked:
ayeen
  • 2
1 Solution
 
JimFiveCommented:
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'
0
 
ayeenAuthor Commented:
hi JimFive thanks for replying....

may i know what the "112" is for?

thanks!
0
 
JimFiveCommented:
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
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now