locked
What is the equivalents of IW , W in sql server? RRS feed

  • Question

  • Hi All,

    i am migrating the oracle database into the sql server database.

    can any suggest the equivalent quires in sql server.

    SELECT TRUNC (TO_DATE('02-MAY-19'), 'IW') from dual;

    SELECT TRUNC (TO_DATE('02-MAY-19'), 'W') from dual;

    currently i am using the Microsoft SQL Server 2017  standard edition...

    Thanks in advance,

    Ramesh Reddy Mallidi.

    Tuesday, April 30, 2019 9:22 AM

Answers

  • I think Olaf was a little quick when he did this post. You can do this with the various date functions in SQL Server, but it will be quite complicated. To the extent that I don't really feel like trying, but rather I'm thinking that you may be better off with a calendar table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, May 2, 2019 9:29 PM

All replies

  • W and IW are date format Options and I guess they stand for week & iso week =>

    select datepart(week, getdate()), datepart(iso_week, getdate())


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Puzzle_Chen Wednesday, May 1, 2019 2:16 AM
    Tuesday, April 30, 2019 9:27 AM
  • Hi Olaf,

    In Oracle 

    IW-Same day of the week as the first day of the ISO year
    W-Same day of the week as the first day of the month

    Ref:

    https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions201.htm

    https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions230.htm

    Thanks,

    Ramesh Reddy.

    Tuesday, April 30, 2019 9:58 AM
  • Then you can use the SQL that I posted.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, April 30, 2019 10:11 AM
  • Hi Olaf,

    when i ran the following queries in oracle  server,

    SELECT TRUNC (Sysdate, 'IW') from dual;

    OutPut: 29-APR-19

    SELECT TRUNC (Sysdate, 'W') from dual;

    Output: 01-MAY-19

    In oracle, the sysdate function gives the current date in server which is 02-May-2019.

    can i get the same output in sql server by using week and iso_week along with convert function in sql server.

    Thanks,

    Ramesh.

    Thursday, May 2, 2019 7:05 AM
  • I think Olaf was a little quick when he did this post. You can do this with the various date functions in SQL Server, but it will be quite complicated. To the extent that I don't really feel like trying, but rather I'm thinking that you may be better off with a calendar table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, May 2, 2019 9:29 PM