Jump to content
  • 0

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


RobStach
 Share

Question

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

 

 

 

 

 

Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 0

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)


 

Link to comment
Share on other sites

  • 0
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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...