• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

How do you create a SQL Select statement to read a field with a data type of text and a field value of 20110426 so that the result set is displayed as 04/26/2011?

I am working with Access 2003 using an MDB type file.

I want to SELECT fields from 2 tables and save these values into another table.

I SELECT from a table titled IMNYCashBreaks and a field titled "ValueDate" with a field type of TEXT and the value is 20110426.

I stored the SELECT results into a table titled tblOpenItems and a field titled "processdate"
with a field type of Text and the value should be stored as 04/26/2011.
I want to retain the field as a TEXT field because I am adding records to an existing table.

Do you know how to revise the following SELECT statement so that it converts a value of
20110426 for the field ValueDate into a converted value of 04/26/2011 as a text field?

SELECT a.[ValueDate]
FROM tblBanksConv AS b, IMNYCashBreaks AS a
WHERE left(a.BalancePool,3) & mid(a.BalancePool,5)=b.[GLAcct#];
0
zimmer9
Asked:
zimmer9
  • 3
  • 2
3 Solutions
 
shambaladCommented:
SELECT Format(Dateserial(mid([a].[ValueDate],1,4),mid([a].[ValueDate],5,2),mid([a].[ValueDate],7,2) ),"mm/dd/yyyy") AS yr
FROM tblBanksConv AS b, IMNYCashBreaks AS a
WHERE left(a.BalancePool,3) & mid(a.BalancePool,5)=b.[GLAcct#];
0
 
CurtHugoCommented:
Use the Mid, Right and Left functions to format the date, i.e. FormattedDate

SELECT a.ValueDate, Mid([a.ValueDate],5,2) & "/" & Right([a.ValueDate],2) & "/" & Left([a.ValueDate],4) AS FormattedDate
FROM tblBanksConv AS b, IMNYCashBreaks AS a
WHERE left(a.BalancePool,3) & mid(a.BalancePool,5)=b.[GLAcct#];
0
 
Gustav BrockCIOCommented:
Or just use Format twice:
SELECT 
  Format(Format(a.[ValueDate],"0000\/00\/00"),"mm\/dd\/yyyy")
FROM 
  tblBanksConv AS b, 
  IMNYCashBreaks AS a
WHERE 
  left(a.BalancePool,3) & mid(a.BalancePool,5)=b.[GLAcct#]; 

Open in new window


/gustav
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
shambaladCommented:
Gustav -
For my edification, could you point me towards some documentaion on that  Format(a.[ValueDate],"0000\/00\/00")? I've not seen that before.
Thanks,
Todd
0
 
shambaladCommented:
Never mind that last one. just another case of RTFM.
0
 
Gustav BrockCIOCommented:
Yes, Format is a powerful function.

First:
Format(a.[ValueDate],"0000\/00\/00")
returns the string "20110426" as "2011/04/26" which can be converted to a date/time value:

datDate = DateValue("2011/04/26")

However, that isn't necessary because the outer Format - using "mm\/dd\/yyyy" as the format - expects a date/time value and does the conversion.

Finally:
Format( <date value > ,"mm\/dd\/yyyy")
formats the date value to the desired string format: "04/26/2011".

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now