CODEWITHSUNDEEP
sqlsql-serverstringint
user3691566
user3691566

Reputation: 303

Sql convert string of numbers to int

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)

Upvotes: 1

Views: 506

Answers (3)

Guanxi
Guanxi

Reputation: 3131

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

Upvotes: 0

Christian Barron
Christian Barron

Reputation: 2755

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))

Upvotes: 4

Zohar Peled
Zohar Peled

Reputation: 82524

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

Upvotes: 1

Related Questions