Convert yyyymmdd format to dd-mm-yyyy

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
 
G

Guest

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
 
D

Douglas J. Steele

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
 
G

Guest

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
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top