Convert yyyymmdd format to dd-mm-yyyy

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

Guest

Dear All,

I have table access database 2003 with one column have data in this format:
yyyymmdd eg 20071022 ( mean 22 Oct 2007), 20070914 (mean 14 Sept 2007)

My question is: Is there anyway that I can convert that format yyyymmdd (eg
20071022) into dd-mm-yyyy (22-10-2007) by creating query?

Highly appreciate your help,

Thanks & Regards

PA
 
Try:

To return it in a date format:
DateSerial(Left([FieldName],4),Mid([FieldName],5,2),Right([FieldName],2))

To add a format of "-" instead of "/" to the date:
Format(DateSerial(Left([FieldName],4),Mid([FieldName],5,2),Right([FieldName],2)),"dd-mm-yyyy")


Use it as a new field in the query
 
Perhaps easier:

Format(CDate(Format([FieldName], "0000-00-00")), "dd-mm-yyyy")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ofer Cohen said:
Try:

To return it in a date format:
DateSerial(Left([FieldName],4),Mid([FieldName],5,2),Right([FieldName],2))

To add a format of "-" instead of "/" to the date:
Format(DateSerial(Left([FieldName],4),Mid([FieldName],5,2),Right([FieldName],2)),"dd-mm-yyyy")


Use it as a new field in the query
--
Good Luck
BS"D


PA said:
Dear All,

I have table access database 2003 with one column have data in this
format:
yyyymmdd eg 20071022 ( mean 22 Oct 2007), 20070914 (mean 14 Sept 2007)

My question is: Is there anyway that I can convert that format yyyymmdd
(eg
20071022) into dd-mm-yyyy (22-10-2007) by creating query?

Highly appreciate your help,

Thanks & Regards

PA
Click to expand...
Click to expand...
 
Much better

Thanks

--
Good Luck
BS"D


Douglas J. Steele said:
Perhaps easier:

Format(CDate(Format([FieldName], "0000-00-00")), "dd-mm-yyyy")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ofer Cohen said:
Try:

To return it in a date format:
DateSerial(Left([FieldName],4),Mid([FieldName],5,2),Right([FieldName],2))

To add a format of "-" instead of "/" to the date:
Format(DateSerial(Left([FieldName],4),Mid([FieldName],5,2),Right([FieldName],2)),"dd-mm-yyyy")


Use it as a new field in the query
--
Good Luck
BS"D


PA said:
Dear All,

I have table access database 2003 with one column have data in this
format:
yyyymmdd eg 20071022 ( mean 22 Oct 2007), 20070914 (mean 14 Sept 2007)

My question is: Is there anyway that I can convert that format yyyymmdd
(eg
20071022) into dd-mm-yyyy (22-10-2007) by creating query?

Highly appreciate your help,

Thanks & Regards

PA
Click to expand...
Click to expand...
Click to expand...
 
It works well

Many Thanks for your help

PA

Ofer Cohen said:
Much better

Thanks

--
Good Luck
BS"D


Douglas J. Steele said:
Perhaps easier:

Format(CDate(Format([FieldName], "0000-00-00")), "dd-mm-yyyy")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ofer Cohen said:
Try:

To return it in a date format:
DateSerial(Left([FieldName],4),Mid([FieldName],5,2),Right([FieldName],2))

To add a format of "-" instead of "/" to the date:
Format(DateSerial(Left([FieldName],4),Mid([FieldName],5,2),Right([FieldName],2)),"dd-mm-yyyy")


Use it as a new field in the query
--
Good Luck
BS"D


:

Dear All,

I have table access database 2003 with one column have data in this
format:
yyyymmdd eg 20071022 ( mean 22 Oct 2007), 20070914 (mean 14 Sept 2007)

My question is: Is there anyway that I can convert that format yyyymmdd
(eg
20071022) into dd-mm-yyyy (22-10-2007) by creating query?

Highly appreciate your help,

Thanks & Regards

PA
Click to expand...
Click to expand...
Click to expand...
 
Back
Top