All geek questions in one place

Help translate this query for a week from Oracle PL / SQL to SQL Server 2008

I have the following query that runs on my Oracle database and I want to have an equivalent for SQL Server 2008 database:

SELECT TRUNC( /* Midnight Sunday */
         NEXT_DAY(SYSDATE, 'SUN') - (7*LEVEL)
       ) AS week_start,
       TRUNC( /* 23:59:59 Saturday */
         NEXT_DAY(NEXT_DAY(SYSDATE, 'SUN') - (7*LEVEL), 'SAT') + 1
       ) - (1/(60*24)) + (59/(60*60*24)) AS week_end
FROM DUAL
CONNECT BY LEVEL <= 4 /* Get the past 4 weeks */

What the request does is get the beginning of the week and the end of the week in the last 4 weeks. The number of weeks is arbitrary and should be easily changed in the SQL Server query I want. It generates data as follows:

WEEK_START          WEEK_END
2010-03-07 00:00:00 2010-03-13 23:59:59
2010-02-28 00:00:00 2010-03-06 23:59:59
...

The part I'm stuck with in translation right now CONNECT BY LEVEL, since SQL Server 2008 has no equivalent. I would rather just adjust the type string CONNECT BY LEVEL <= 4and make the request generate more or less weeks (i.e. I don't need to configure multiple statements UNION ALL).

: , , :

   SELECT week_start,
          DATEADD(SECOND, -1, DATEADD(DAY, 7, week_start)) AS week_end
   FROM (
          SELECT CAST(
                   CONVERT(
                     VARCHAR(10),
                     DATEADD(DAY, 1-DATEPART(DW, GETDATE()), GETDATE()),
                     111
                   ) AS DATETIME
                 ) AS week_start
        ) AS week_start_view

, .

+3
5

OMG Ponies ', , week_end , . :

WITH dates AS (
  SELECT DATEADD(
           DD, 
           1 - DATEPART(DW, CONVERT(VARCHAR(10), starting_date, 111)), 
           CONVERT(VARCHAR(10), starting_date, 111)
         ) AS midnight
  FROM (
         SELECT DATEADD(WEEK, -3, GETDATE()) AS starting_date
       ) AS starting_date_view

  UNION ALL

  SELECT DATEADD(DD, 7, midnight)
  FROM dates
  WHERE DATEADD(DD, 7, midnight) < GETDATE()
) SELECT midnight AS week_start,
         DATEADD(SS, -1, DATEADD(DAY, 7, midnight)) AS week_end
  FROM dates

4 :

week_start                 week_end
2010-02-14 00:00:00.000    2010-02-20 23:59:59.000
2010-02-21 00:00:00.000    2010-02-27 23:59:59.000
2010-02-28 00:00:00.000    2010-03-06 23:59:59.000
2010-03-07 00:00:00.000    2010-03-13 23:59:59.000

, , , , . : 3 SELECT DATEADD(WEEK, -3, GETDATE()) AS starting_date. , , .

,

: , :

WITH dates AS (
  SELECT DATEADD(
           DD,
           1 - DATEPART(DW, CONVERT(VARCHAR(10), starting_date, 111)), 
           CONVERT(VARCHAR(10), starting_date, 111)
         ) AS midnight_sunday
  FROM (
         SELECT DATEADD(WEEK, -4, GETDATE()) AS starting_date
       ) AS starting_date_view

  UNION ALL

  SELECT DATEADD(DD, 7, midnight_sunday)
  FROM dates
  WHERE DATEADD(DD, 7, midnight_sunday) <
        DATEADD(
          DD,
          1 - DATEPART(DW, CONVERT(VARCHAR(10), GETDATE(), 111)), 
          CONVERT(VARCHAR(10), GETDATE(), 111)
        )
) SELECT midnight_sunday AS week_start,
         DATEADD(SS, -1, DATEADD(DAY, 7, midnight_sunday)) AS week_end
  FROM dates

:

week_start                 week_end
2010-02-07 00:00:00.000    2010-02-13 23:59:59.000
2010-02-14 00:00:00.000    2010-02-20 23:59:59.000
2010-02-21 00:00:00.000    2010-02-27 23:59:59.000
2010-02-28 00:00:00.000    2010-03-06 23:59:59.000

. :

WITH dates AS (
  SELECT CAST(
        FLOOR(CAST(starting_date AS DECIMAL(12, 5))) -
        (DAY(starting_date) - 1) AS DATETIME
      ) AS month_start
  FROM (
         SELECT DATEADD(MONTH, -3, GETDATE()) AS starting_date
       ) AS starting_date_view

  UNION ALL

  SELECT DATEADD(MONTH, 1, month_start)
  FROM dates
  WHERE DATEADD(MONTH, 1, month_start) < GETDATE()
) SELECT month_start,
         DATEADD(SS, -1, DATEADD(MONTH, 1, month_start)) AS month_end
  FROM dates
  ORDER BY month_start DESC
+3

( ):

WITH dates AS (
    SELECT DATEADD(DD, 
                   1 - DATEPART(DW, CONVERT(VARCHAR(10), starting_date, 111)), 
                   CONVERT(VARCHAR(10), starting_date, 111)
                   ) AS midnight
      FROM (SELECT DATEADD(WEEK, -3, GETDATE()) AS starting_date) AS starting_date_view
    UNION ALL
    SELECT DATEADD(DD, 7, midnight)
      FROM dates
     WHERE DATEADD(DD, 7, midnight) < GETDATE()) 
SELECT midnight AS week_start,
       DATEADD(SS, -1, DATEADD(DAY, 7, midnight)) AS week_end
  FROM dates

:

  • SET DATEFIRST:

    SET DATEFIRST 7

  • SQL Server 2005 + Oracle CONNECT BY LEVEL - CTE ( ANSI). :

    WITH dates AS (
   SELECT DATEADD(DD, 
                  1 - DATEPART(DW, CONVERT(VARCHAR(10), GETDATE(), 111)), 
                  CONVERT(VARCHAR(10), GETDATE(), 111)) AS date
   UNION ALL
   SELECT DATEADD(dd, 7, d.date)
     FROM dates d
    WHERE DATEADD(dd, 7, d.date) <= DATEADD(dd, 4*7, GETDATE()))
SELECT t.date AS week_start,
       DATEADD(ss, -1, DATEADD(DAY, 7, t.date)) AS week_end
  FROM dates t

. , . , DATEADD(dd, 4*7, GETDATE()), 4 , .

+2

, , :

;WITH cte(n) AS
(
    SELECT 0
    UNION ALL SELECT 1
    UNION ALL SELECT 2
    UNION ALL SELECT 3
)
  SELECT week_start,
          DATEADD(SECOND, -1, DATEADD(DAY, 7, week_start)) AS week_end
   FROM (
          SELECT CAST(
                   CONVERT(
                     VARCHAR(10),
                     DATEADD(WEEK, -n, DATEADD(DAY, 1-DATEPART(DW, GETDATE()), GETDATE())),
                     111
                   ) AS DATETIME
                 ) AS week_start
                           FROM cte
        ) AS week_start_view;

, , , . >= 03/07 < 03/14. , , 23:59:59 ; , .

+1

Here is what I came up with. This is a little annoying that it relies on at least one of my tables with rows> = the number of weeks I want to select. I could tweak it a bit to include the current week.

   SELECT week_start,
          DATEADD(SECOND, -1, DATEADD(DAY, 7, week_start)) AS week_end
   FROM (
          SELECT CAST(
                   CONVERT(
                     VARCHAR(10),
                     DATEADD(
                       DAY,
                       1-DATEPART(DW, GETDATE()),
                       DATEADD(DAY, -7*level, GETDATE())
                     ),
                     111
                   ) AS DATETIME
                 ) AS week_start
          FROM (
                 SELECT level
                 FROM (
                        SELECT ROW_NUMBER() OVER(ORDER BY RAND()) AS level
                        FROM my_table_with_at_least_21_rows
                      ) AS all_rows_view
                 WHERE level <= 21
               ) AS level_view
        ) AS week_start_view

This has happened in the last 21 weeks, starting from last week. Here are some sample data:

week_start                  week_end
2010-02-28 00:00:00.000     2010-03-06 23:59:59.000
2010-02-21 00:00:00.000     2010-02-27 23:59:59.000
2010-02-14 00:00:00.000     2010-02-20 23:59:59.000
2010-02-07 00:00:00.000     2010-02-13 23:59:59.000
...
0
source

based on your code:

SELECT DATEADD(day, weeks.week * -7, week_start) AS week_start,
       DATEADD(SECOND, -1, DATEADD(DAY, (weeks.week-1) * -7, week_start)) AS week_end
FROM (
      SELECT CAST(
               CONVERT(
                 VARCHAR(10),
                 DATEADD(DAY, 1-DATEPART(DW, GETDATE()), GETDATE()),
                 111
               ) AS DATETIME
             ) AS week_start
     ) AS week_start_view,
     ( SELECT 0 AS week UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) AS weeks
0
source

Source: https://habr.com/ru/post/1735991/

More articles:


All Articles