Returning 05 from 20010522

[jasonmac]

Hello everyone and happy Friday. I'm using as SQL Pass Through query to bring in som information from one of out systems. The date is formatted year month day. to 05/22/2001 would looke like 20010522. I need ot get it into date format. My first though was that there had to be a function that would do that for me but I couldn't find one. Now I'm trying to take it apart and put it back together. Is ther a substring function I could use to extract the month?

Thanks in advance
Jason

 

[MakeItSo]

YOu could use the mid() function:
myMonth=Mid(datefield,5,2)

Background: datefield contains your 20010520 string. Mid function takes string value from 5th position on, length 2 characters.

if your datefield is always in this string form, the mid function shoud do fine.

Greetings,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[SgtJarrow]

so you have a string, 20020512, and need to make it look like 5/12/2002.

first try using Format...

Format([field], "m/d/yyyy&quot

should get what you want. But you may have to use:

Format(CDate([field]), "m/d/yyyy&quot

one of the above should work.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[jasonmac]

mstrmage, this is what I used in my SQL statement.

Format(Cdate(EPOSShiftProd.ShiftSeq), "m/d/yyyy&quot

The query does not error out but the output says:

#Error


EPOSShiftProd.ShiftSeq is originally formatted as a long integer. I also tried

Format(Cdate(Cstr(EPOSShiftProd.ShiftSeq)), "m/d/yyyy&quot

but I got the same result. Any Ideas on why this might be happening?

Thanks again.
Jason

 

[SgtJarrow]

No reason I can think of why it doesn't work....

If your values are always 8 characters (yyyymmdd) then you can try:

DateSerial(Left(EPOSShiftProd.ShiftSeq,4),Mid(EPOSShiftProd.ShiftSeq,5,2),Right(EPOSShiftProd.ShiftSeq,2))

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[jasonmac]

Thanks to everyone. It's working now