Simultaneous trim and format in Access SQL? 1

[merlynsdad]

I have a WHERE statement with a string of numbers representing a date, e.g. 1110708 = 7/8/2011. I need to trim the left digit, then format it as DDMMYY, preferably in the same WHERE statement. Can I trim and format simultaneously in Access SQL, and if so, how?

If the square peg won't fit in the round hole, sand off the corners.

 

[merlynsdad]

Actually, what I need to do is to convert the number in the WHERE statement in the Date field of SELECT, rather than in the WHERE statement. Sorry about that.

If the square peg won't fit in the round hole, sand off the corners.

 

[Andrzejek]

So, what do you have now (as a SELECT statement) and what would you like to have? An example goes a long way.

Have fun.

---- Andy

 

[SkipVought]

Hmmmmm???

You posted 1110708

Breaking it apart visually...
[tt]
111 07 08
[/tt]
What is the [red]111[/red] supposed to be?


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[merlynsdad]

I have no idea what the leading 1 is for, but the 1's in the 2nd and 3rd positions represent 2011. I'm trimming that leftmost 1 before I start. What I have now is:

SELECT right(trim(atlanta_agent.uday),6) AS [Date]

The "uday" is the 1110708. The [Date] field will currently show me 110708 if it's a text field, but I'd prefer it as date/time.

If the square peg won't fit in the round hole, sand off the corners.

 

[PHV]

SELECT DateSerial(1900+Left(atlanta_agent.uday,2),0+Mid(atlanta_agent.uday,4,2),0+Right(atlanta_agent.uday,2)) AS [Date]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886