Sql convert string of numbers to int

Question

I want to select either week days or a full week depending on a parameter.

I was looking at using a case statement to do the following, but I don't know how to convert a string of number to a value that can be passed as integers.

Im probably doing this all wrong but any help would be appreciated.

This is where im setting the param value:

set @days = (select case when FullWeek = 1 then cast('1,2,3,4,5,6,7' as Numeric(38,0))
                        when fullweek = 0 then cast('2, 3,4,5,6' as Numeric(38,0)) end 
            from Reports)

And this is how I want to call this, its part of a where statement:

where datepart(dw,date) in (@days)

Why not put hardcoded values in a table and usw another column like isWeekday ti identify the case — Guanxi
– Guanxi, Commented May 8, 2015 at 10:22
Ive used a one column reference as its meant to let clients select if they want reports by week days or full week, so made sense just to use one column as he reference and set the code to do the workings out than store the data. — user3691566
– user3691566, Commented May 8, 2015 at 11:11

Christian Barron · Accepted Answer · 2015-05-08 10:20:37Z

4

Why not simplify it and do it this way:

Where (Fullweek = 1) -- Will get all days of week
   or 
      (Fullweek = 0 and datepart(dw,date) in (2,3,4,5,6))

answered May 8, 2015 at 10:20

Christian Barron

2,7551 gold badge16 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3691566 Over a year ago

Fab this worked brilliantly and so simple in the end, I knew I was probably making it harder for myself. Thanks

Christian Barron Over a year ago

No problem, glad it helped you out!

Zohar Peled · Accepted Answer · 2015-05-08 10:20:09Z

1

This isn't even a problem with sql, it's a conveptual problem. You can't convert such a value to a numeric. What do you expect the Numeric value to be in each case?

You are making a very popular beginer's mistake, when you try to convert a delimited string into an array. What you should do in this case is this:

where datepart(dw,date) in 
    case 
        when FullWeek = 1 then
            (1,2,3,4,5,6,7)
        else -- if fullweek is a bit. otherwise use when fullweek = 0 then
            (1,2,3,4,5,6,7)
        end

answered May 8, 2015 at 10:20

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

2 Comments

user3691566 Over a year ago

I tried the case statement in the where statement originally and I just get a syntax error which is why I then tried using the case in a select parameter. Tried your example and this also gives me a syntax error near the keyword "Case"

Zohar Peled Over a year ago

Actually, Christian Barron's code is way better then mine (got my vote up). I'm leaving this answer as well because I think that the text matters as much as the code itself. Seems like there is no way to use case instead of int.

Guanxi · Accepted Answer · 2015-05-08 12:11:05Z

0

I would prefer not to do hard-coding of values in a query. I would rather do it this way:

Create a table to categorize days:

  Day    isWeekday   biWeeklyReportDays
  1        0              0
  2        1              1
  3        1              0
  4        1              0
  5        1              1
  6        1              0
  7        0              0

And my query would be something like this.

IF FullWeek = 0
BEGIN
SELECT @days = days FROM theTable where isWeekDay = 1
END
ELSE
BEGIN
SELECT @days = days FROM theTable
END

----
where datepart(dw,date) in @days

answered May 8, 2015 at 12:11

Guanxi

3,14124 silver badges38 bronze badges

Collectives™ on Stack Overflow

Sql convert string of numbers to int

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related