Thanks Alastair. The delimiter is not a space but there is a space
between the date and the rest of the text - that's why I used the "-1"
in the SSUB (a negative number separates items based on a blank rather
than the current delimiter). Unfortunately, not all the date are
exactly 10 characters in length: 12/20/2008 is, 1/1/2009 isn't, so the
SSUB gave me that flexibility. Some dates don't have the year, e.g.
"9/7" so I originally skipped those with WHERE
(SLEN(SSUB(StockArriveDateT,-1))) >= 8. Hmm......
[SET Dumbness off]
{lightbulb moment}
[SET NoteToSelf ON]
Using Razzak's method I can test the text var's length and if less than
6 (e.g. 12/20), assume the year needs to be added. I know, there are
other pitfalls (if they did enter a year, but only used 2 digits). But
this is for a potential client so I'm not going to get to extravagant
right now. It'll be fun to bring some order to their spreadsheet world.
Doug
[SET BillableTime ON]
Alastair Burr wrote:
Doug,
SSUB won't work in your example unless your DELIMIT character is a
space - unlikely!
Assuming that the position of the date in the string is constant you
could use SGET:
UPDATE MyTableTemp SET +
StockArriveDateD = (SGET(StockArriveDateT, 10, 1)))
which will give you the ten characters starting from position 1.
The update should work then assuming that the data matches your date
formats properly.
Regards,
Alastair.
----- Original Message ----- From: "Doug Hamilton" <[email protected]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Saturday, December 20, 2008 1:59 AM
Subject: [RBASE-L] - Converting Text to Date
Happy Snowy Friday from WI, list.
I would like to extract part of a text column and insert into a date
column.
The data looks like: 4/9/2008 confirmed
So I've tried:
UPDATE MyTableTemp SET +
StockArriveDateD = (SSUB(StockArriveDateT,-1)))
(StockArriveDateD is a date col, StockArriveDateT is a text 30 col)
That gets the first word of the text column, 4/9/2008.
But, the "Expression does not return a valid date"
I tried adding DEXTRACT, but apparently DEXTRACT can't be fooled into
converting text, it wants a DATETIME datatype.
So, how do I convert a date-looking text into a valid date? I looked
through the functions and tried several, but to no avail.
In the Christmas spirit, check this video for some fascinating vocals:
http://www.youtube.com/watch?v=2Fe11OlMiz8
Doug
--------------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG - http://www.avg.com
Version: 8.0.176 / Virus Database: 270.9.19/1857 - Release Date:
19/12/2008 10:09
