DB2 SQL Query to group by week

Question

I am writing an ASP.NET web form page using VB.Net. I am writing code to use on a line graph, but I can't seem to get my query to group by week. Here is my query:

SELECT F42119LA.SDMCU || '-' || F42119LA.SDLNTY AS BranchCode,
       AVG(F42119la.SDIVD-F42119LA.SDDRQJ) AS Days,
       WEEK(SDTRDJ) AS Day 
FROM KAI400.KAIPRDDTA.EXCHBYDATE EXCHBYDATE,
     KAI400.KAIPRDDTA.F42119L14 F42119LA 
WHERE F42119LA.SDBCRC = EXCHBYDATE.CXCRCD
  AND EXCHBYDATE.EXCHDATE = F42119LA.SDTRDJ
  AND F42119LA.SDTRDJ>='118006'
  AND F42119LA.SDTRDJ<='118096'
  AND F42119LA.SDNXTR<>'999'
  AND SDIVD <> 0
  AND SDDRQJ <> 0
  AND F42119LA.SDAEXP <> 0
  AND EXCHBYDATE.CXCRDC='USD'
  AND F42119LA.SDLNTY IN ('S','W')
  AND (SDMCU LIKE '%100' OR SDMCU LIKE '%150') 
GROUP BY SDMCU,
         SDLNTY,
         SDIVD,
         F42119LA.SDMCU || '-' || F42119LA.SDLNTY,
         WEEK(SDTRDJ) 
ORDER BY SDIVD,
         SDMCU,
         SDLNTY

and this is the code the sql string runs through:

Public Shared Function GetMyDataTableString(SqlString As String, Optional IncDb As Integer = 0) As DataTable
    Dim MyConn As OleDbConnection = GetMyConn(IncDb)
    Dim DbCmd As New OleDbCommand(SqlString, MyConn)
    Dim ReturnDataTable As New DataTable
    Try
        If Not MyConn.State = ConnectionState.Open Then
            MyConn.Open()
        End If
        Dim Reader As OleDbDataReader = DbCmd.ExecuteReader(CommandBehavior.CloseConnection)
        Using Reader
            ReturnDataTable.Load(Reader)
        End Using

    Catch ex As Exception
        LogSqlErrors(SqlString, "GetMyDataTableString " & ex.Message.ToString(), IncDb)
        If HttpContext.Current.Session("SITEADMIN") = "True" Then
        HttpContext.Current.Response.Write("<b>OleFun.GetMyDataTableString, datatable failed</b>---<br />" & ex.ToString)
        End If
    Finally 'Happens regardless of failing or succeeding
        MyConn.Close()
    End Try
    Return ReturnDataTable
End Function

Whenever I use WEEK(), it gives me this error:

Value in date, time, or timestamp string not valid

ONDATE is a date field in format MM/DD/YYYY. Does anyone know another way to group by week or what might can be giving me this error? Thanks in advance for your responses.

Are you sure you're using SQL Server? SQL Server does not use `||` for concatenation, and that error message doesn't look like ones from SQL Server — James Z, Apr 06 '18 at 14:19
Your query isn't valid syntax for SQL Server at all, and what is `DATEPART(wl`? — Tab Alleman, Apr 06 '18 at 14:21
Can you copy-paste the actual code that's giving you the error, so we can eliminate typos as possible reasons for the error? And an OLEDB reader doesn't catch syntax errors, so that doesn't mean anything. — Tab Alleman, Apr 06 '18 at 14:23
I've removed the SQL Server tag here, as it's clearly not (as James has said, SQL Server doesn't use `||` for concatenation, it uses `+`). @kodym you'll need to tag the correct RDBMS. — Thom A, Apr 06 '18 at 14:24
On a different note, avoid using implicit joins: `FROM KAI400.KAIPRDDTA.EXCHBYDATE EXCHBYDATE,KAI400.KAIPRDDTA.F42119L14 F42119LA`. You should be using `JOIN` syntax, it's been around for decades, and unless you're using an RDBMS from the 1980's it's going to support that syntax. — Thom A, Apr 06 '18 at 14:25
At this point, we really need to determine your RDBMS. If you don't know, can you post the code of the `GetMyConn` function? You can obfuscate any username/password data for safety. — Tab Alleman, Apr 06 '18 at 14:34
Are you sure you use proper function? See [How to use date functions on DB2](http://www.dbatodba.com/db2/how-to-do/how-to-use-date-functions-on-db2/) — IvanH, Apr 06 '18 at 19:34

score 1 · Answer 1 · answered Apr 06 '18 at 14:15

1

-- date part takes the part of the date as first paramater:
-- you have:
DATEPART(SDTRDJ, wk)

-- needs to be:
DATEPART(wk, SDTRDJ)

answered Apr 06 '18 at 14:15

Brad

3,454
3
27
50

That's the way I tried it first and it gives me the same error – kodym Apr 06 '18 at 14:18
Are you sure this is SQL Server then because I run this and I get 14: SELECT DATEPART(wk, GETDATE()) – Brad Apr 06 '18 at 14:21

score 0 · Answer 2 · answered Apr 06 '18 at 15:03

0

DATEPART() might be a function in SQL Server, but it not a function in Db2. You could use WEEK() or WEEK_ISO() which are Db2 functions. https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0005481.html

You could also use EXTRACT if you are on a recent version of Db2 LUW https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0053629.html

db2 "values extract(WEEK FROM  current date)"

1          
-----------
         14

1 record(s) selected.

BTW you don't need to group by the concatenation of F42119LA.SDMCU || '-' || F42119LA.SDLNTY, you can group by those columns individually, and only concat in the SELECT.

answered Apr 06 '18 at 15:03

Paul Vernon

3,818
1
10
23

Although as you have e.g. `F42119LA.SDTRDJ>='118006'` which imples that `SDTRDJ` is not a `DATE` so you would need to convert it to a date to apply the week functions above – Paul Vernon Apr 06 '18 at 15:06
I was able to make it an actual date and use the WEEK()/WEEK_ISO() functions, but it gave me an error, "failed to enable constraints" – kodym Apr 06 '18 at 15:21
That does not sound like a DB2 error. A stackoverflow search comes up with suggestions that it is an asp.net error https://stackoverflow.com/questions/40986121/asp-net-c-binding-two-generic-lists-to-a-datagrid-view-failed-to-enable-constr . You are grouping by columns not in your select list, so you might well get duplicate rows in the output.. is that the cause of the error? – Paul Vernon Apr 06 '18 at 17:21
I was able to figure out the constraints problem, now it is giving me this error: Value in date, time, or timestamp string not valid – kodym Apr 09 '18 at 17:29
You probably need to check that all your values are convertable to dates. E.g. check you don't have any values such as `000000` or `999999` in your SDTRDJ. Or use something like this to find any values that can't be cast as date https://stackoverflow.com/questions/37055675/validate-dates-before-conversion-aka-isdate-equivalent – Paul Vernon Apr 10 '18 at 09:22

score 0 · Answer 3 · answered Aug 18 '22 at 11:26

In DB2, you can group days in same week using following methods:

DATE_PART('WEEK', dateColumn) -> week starts at Saturday
WEEK(dateColumn) -> week starts at Sunday
WEEK_ISO(dateColumn) -> week starts at Monday
DAYS(dateColumn) / 7 -> week starts at Sunday

Notes:

I believe that they work with columns of type DATE as will as TIMESTAMP.
"DAYS(dateColumn) / 7" doesn't get you week number, however it is helpful in grouping by week.
Kindly check your week start day as they differ in their results as following:

DB2 SQL Query to group by week

3 Answers3