covert string into integer - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

covert string into integer

Author

Topic

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2012-06-19 : 10:04:58

Hi there,

I don't know whether it is possible, but a solution would help me alot:)

Ok, I've got a SQL Table with a column named "period". The values are year and month. When I do a select on that table and get as value "year", is there a chance to convert it to the number 365? I need a number to calculate an end date...

Kind regards,

Lara

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-19 : 10:08:04

quote:
Originally posted by musclebreast

Hi there,

I don't know whether it is possible, but a solution would help me alot:)

Ok, I've got a SQL Table with a column named "period". The values are year and month. When I do a select on that table and get as value "year", is there a chance to convert it to the number 365? I need a number to calculate an end date...

Kind regards,

Lara

May be this?

SELECT
  CAST(Period AS INT)*365
FROM YourTable

Post some sample input data and the expected output, and I am sure someone on the forum would be able to offer more concrete advice. This might help: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2012-06-20 : 07:38:53

Hi,

Thanks for your help. Yes converting is an idea...but the problem is, that in the column is the value "Month" or "year"...it is a string. Logicially, I need to check if the value is month or year...if it is year I can use 365 days and if it's month than i can use 30 days.

Are such things possible in SQL? Hopefully, I haven't described my problem too bad!?

Cheers,

Lara

quote:
Originally posted by sunitabeck

quote:
Originally posted by musclebreast

Hi there,

I don't know whether it is possible, but a solution would help me alot:)

Ok, I've got a SQL Table with a column named "period". The values are year and month. When I do a select on that table and get as value "year", is there a chance to convert it to the number 365? I need a number to calculate an end date...

Kind regards,

Lara
May be this?
SELECT
CAST(Period AS INT)*365
FROM YourTable
Post some sample input data and the expected output, and I am sure someone on the forum would be able to offer more concrete advice. This might help: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-20 : 08:52:52

quote:
Originally posted by musclebreast

Hi,

Thanks for your help. Yes converting is an idea...but the problem is, that in the column is the value "Month" or "year"...it is a string. Logicially, I need to check if the value is month or year...if it is year I can use 365 days and if it's month than i can use 30 days.

Are such things possible in SQL? Hopefully, I haven't described my problem too bad!?

Cheers,

Lara

Can you post sample data?

Are there two columns, one indicating whether it is month or year, and another with the value, or is it just a single column? What is the format of the data? Is it 2-digit year, or 4-digit year? If you had to do it on paper, are there systematic steps that can be used to identify whether it is year or month, or is it heuristic?

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2012-06-24 : 07:48:07

Hi,

thanks for your patience:

This is my table:

time:

number(column1) period (column2)
1........................year
2........................month
3..........................day

I hope you can see the problem. If it is a day I can use the number from column 1 to calculate an end date...but it is a year or a month I have to multiply the first with a value (30 or 365) to calculate an end date.Are such things possible with SQL?

The format of the end date is not too important because I can convert it easily:)

Kind regards,

Lara

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-24 : 12:16:23

SELECT EndDate = CASE Period
WHEN 'Year' then Number
WHEN 'Month' then Number *12
WHEN 'day' then Number *365
END
From yourTable

Jim

Everyday I learn something that somebody else already knew

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2012-06-24 : 18:01:23

Thanks jim,

I know we are in the SQl Server forum...but is "WHEN" not only used for oracle?

Kind regards,

Lara

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-24 : 19:42:05

I don't know about Oracle, but t-SQL has 2 slightly different WHEN expressions. The expression I gave you could have been written as a searched CASE, like this:
SELECT EndDate = CASE
WHEN Period = 'Year' then Number
WHEN Period = 'Month' then Number *12
WHEN Period = 'day' then Number *365
END
From yourTable

The difference is in the latter where I could also add other conditions like
WHEN Period = 'Year' and State = 'GA THEN number*12

Jim

Everyday I learn something that somebody else already knew

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-06-26 : 05:27:46

CASE expression is supported in almost all RDBMSs

Madhivanan

Failing to plan is Planning to fail

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-06-26 : 08:35:12

quote:
Originally posted by musclebreast

Thanks jim,

I know we are in the SQl Server forum...but is "WHEN" not only used for oracle?

Kind regards,

Lara

Yes, Oracle has an exact flow statement CASE expression.

--------------------------
Get rich or die trying
--------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources