How do I convert a field from yyyymmdd hhmmss to yymmddhhmm

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I convert a field from yymmdd hhmmss to yymmddhhmm ?
for example:2/16/05 7:45 AM into 200502160745.

How do I remove the 0 but I want to put pipe delimited file when I export
that field to txt file.
for example: 0|0|0|0|0 into | | | | | |
 
For your first question, I would use the date functions (Year, Month, Day,
etc.) together with the format to concatenate the string that you desire.

For example:

Year(Now()) & Format(Now(),"mm") & Day(Now()) & Format(Now(),"hhmm")

For your second question, you may want to look at the Replace function,
although I am not certain exactly what you are attempting to do.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


in message How do I convert a field from yymmdd hhmmss to yymmddhhmm ?
for example:2/16/05 7:45 AM into 200502160745.

How do I remove the 0 but I want to put pipe delimited file when I export
that field to txt file.
for example: 0|0|0|0|0 into | | | | | |
 
"=?Utf-8?B?TmF0YXN5YSBMaW5nZ2FuYW5kYQ==?=" <Natasya
(e-mail address removed)> wrote in (e-mail address removed):
How do I convert a field from yymmdd hhmmss to yymmddhhmm ?
for example:2/16/05 7:45 AM into 200502160745.
Click to expand...

Your question is not quite consistent. A date like 2/16/05 7:45 AM is
already a valid date, so you can simply coerce it and reformat it:

SELECT FORMAT(CDATE("2/16/05 7:45 AM"), "yyyymmddhhnn") AS NewDate

Note that the placeholder for miNutes is an n, not an m.

If you really mean you are starting with a date format like "yymmdd
hhmmss" you'll have to chop it up using string functions first:

tempDate = DateSerial( 2000 + CInt(Mid(sMyDate,1,2)), _
CInt(Mid(sMyDate,3,2)), CInt(Mid(sMyDate,5,2))) + _
TimeSerial(CInt(Mid(sMyDate,8,2)), CInt(Mid(sMyDate,10,2)),0)

sFormattedDate = Format(tempDate, "yyyymmddhhnn")

which can be all plastered into a single line, but you get the picture.

Hope that helps


Tim F
 
Back
Top