August 8, 2016 at 4:19 am
Hello,
SQL Server novice here, I'm looking for help to get through the SQL statement that I mentioned below. I have an SSAS cube that I can query with MDX through OpenQuery. I've setup a Linked Server and got the data through OpenQuery and loaded into a table. So, the table consists of the raw data that I'm planning to convert.
What I'm trying to do is, to convert the "Month" and "Week" columns into a format that will be useful to me in my future tasks. I've used RTRIM and LTRIM before to remove the spaces in strings, but this one is a little bit different than the things I've done so far.
1- I have the "Week" information as follows;
- Week 16/22 (30.05.16-05.06.16)
- Week 16/23 (06.06.16-12.06.16) etc...
Could you please tell me how I can replace the data as "16/22" or "1622" to simplify the way I store the data. Long story short, I need to trim certain characters from left and the right side of the string.
2- I have the "Month" information as follows;
- Jun 2016
- Aug 2015 etc...
So, I'm planning to convert the data as "0716", 0816"...
I've done some research, but I'm a bit confused. 🙂
Thanks,
seismicbeat
August 8, 2016 at 5:54 am
1. Use SUBSTRING function.
2. Use REPLACE function
August 8, 2016 at 5:55 am
seismicbeat (8/8/2016)
Hello,SQL Server novice here, I'm looking for help to get through the SQL statement that I mentioned below. I have an SSAS cube that I can query with MDX through OpenQuery. I've setup a Linked Server and got the data through OpenQuery and loaded into a table. So, the table consists of the raw data that I'm planning to convert.
What I'm trying to do is, to convert the "Month" and "Week" columns into a format that will be useful to me in my future tasks. I've used RTRIM and LTRIM before to remove the spaces in strings, but this one is a little bit different than the things I've done so far.
1- I have the "Week" information as follows;
- Week 16/22 (30.05.16-05.06.16)
- Week 16/23 (06.06.16-12.06.16) etc...
Could you please tell me how I can replace the data as "16/22" or "1622" to simplify the way I store the data. Long story short, I need to trim certain characters from left and the right side of the string.
2- I have the "Month" information as follows;
- Jun 2016
- Aug 2015 etc...
So, I'm planning to convert the data as "0716", 0816"...
I've done some research, but I'm a bit confused. 🙂
Thanks,
seismicbeat
OK, let's look at the weeks first. Try running this:
DECLARE @x VARCHAR(50) = 'Week 16/22 (30.05.16-05.06.16)';
SELECT @x
, Sub1 = SUBSTRING(@x, 6, 5)
, Sub2 = REPLACE(SUBSTRING(@x, 6, 5), '/', '');
Sub1 includes the slash, Sub2 does not.
Next one is a bit more difficult. The approach I've taken is to add '01' to the text string, then convert it to a date, then use date functions on that date to extract the bits we need.
Then, as you need leading zeros, I had to convert it back to text again and add a leading zero where required.
Note that your sorting will go wrong if you display multiple years' worth of data. Because of this, I would suggest that you consider switching years and months around and presenting the result as an integer (dt2, below)
DECLARE @y VARCHAR(50) = 'Aug 2015';
SELECT @y
, dt1 = RIGHT(CONCAT('0',
CAST(MONTH(CAST(CONCAT('01 ', @y) AS DATE)) * 100 + YEAR(CAST(CONCAT('01 ', @y) AS DATE))
- 2000 AS VARCHAR(4))), 4)
, dt2 = (YEAR(CAST(CONCAT('01 ', @y) AS DATE)) - 2000) * 100 + MONTH(CAST(CONCAT('01 ', @y) AS DATE));
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply