Part 2: Progres and Roadblocks - number string conversion to a date?

[RobStach]

Well, I kind of figured out the answer to my question from the other day (with the help of some of you - thank you!)

 

I did manage my own work-around .... kind of.

 

I was finally able to extract the last 6 numbers of the SKUs we use for events. These numbers represent the date of that event ... it would look like XXX091021 for example. The XXX would represent the abbreviation of the location of the event.

Anyway - there were some great tips sent my way but ultmately (and may not correctly), I ended up using a formula to bring those last six numbers of the sku (the date) into a new column.

New column entries look like this: 091021

Now I need to convert that (or at least represent it on a datapage) as an actual DATE. 

Why? I need to show all events that will take place between two given dates. 

 

I know this is an insanely difficult route to take but the way that our orders are processed does not allow for the import of the event date. Painful. 

 

ANYWAY - anyone?  I know Concatenate was suggested but I have no idea how to concat within a string.

 

THANK YOU ALL IN ADVANCE!

 

R

 

 

 

 

 

[NiceDuck]

Okay, lets I'm gong suggest some step here part by part. 

first thing you need to have is the SQL formula for isolating the 6 digit number you have. Once you have it isolated, you can use something like this. On my case, the isolated 6 digit number is on the virtual 1. 

CONVERT(DATETIME, SUBSTRING('[@cbParamVirtual1]', 1, 2) + '/' + SUBSTRING('[@cbParamVirtual1]', 3, 2) + '/20' + SUBSTRING('[@cbParamVirtual1]', 5, 2), 101)


 

[RobStach]

15 hours ago, NiceDuck said:

Okay, lets I'm gong suggest some step here part by part. 

first thing you need to have is the SQL formula for isolating the 6 digit number you have. Once you have it isolated, you can use something like this. On my case, the isolated 6 digit number is on the virtual 1. 

CONVERT(DATETIME, SUBSTRING('[@cbParamVirtual1]', 1, 2) + '/' + SUBSTRING('[@cbParamVirtual1]', 3, 2) + '/20' + SUBSTRING('[@cbParamVirtual1]', 5, 2), 101)


 

THANK YOU so much!

Forgive my ignorance - I'm on the uphill side of the learning curve here...

a) I did manage to isolate the 6 digits via a formula. Those six digits sit in a column I titled Extracted_Date.

Since this column is a formula, it doesn't appear (to me) that I can run a formula like yours in a new column against the extracted date.

 

So - are you doing this on a datapage or in the table itself? 

Thank you again!

 

Rob

[NiceDuck]

The one I have provided to you is for datapage level (specifically, form types such as submission form, SRUpdate and report details page since they are the only one that can have a virtual parameter).

It can be done directly on the formula field as well but you will have to significantly change the formula.