473,324 Members | 2,511 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

How to format a date in a query to switch the digits

anoble1
245 128KB
Right now when I run my query the default date reads: 20140519

I would like it to read: 05/19/2014
Is that possible? thanks,
Aug 25 '14 #1

✓ answered by twinnyfo

So, your "date" is actually the "number" 20,140,519?

Then, I would recommend converting to Text (using CStr() Function).

But this is really not your problem. Although "20140519" looks like a date it is actually completely different, so, unless you want to convert this value every time you encounter it, you should probably look into getting the original data converted to a Date format.

Sooooooooo............. this might work:

Expand|Select|Wrap|Line Numbers
  1. Format(CDate(Left(CStr(20140519),4) & "-" & Mid(CStr(20140519),5,2) & "-" & Right(CStr(20140519),2)),"mm/dd/yyyy")
But it is a kinda silly way to do it. But it works.

8 1151
twinnyfo
3,653 Expert Mod 2GB
anoble,

Is your "date" a Date data type or is it a Text data type.

There are several places to start, of which
Expand|Select|Wrap|Line Numbers
  1. CDate()
function could probably transform your "text" date into a date data type. Then it is just an issue of format, which would be easily solved by the
Expand|Select|Wrap|Line Numbers
  1. Format()
function.

In your case:

Expand|Select|Wrap|Line Numbers
  1. Format(CDate([YourDateString]),"mm/dd/yyyy")
Hope this helps.
Aug 25 '14 #2
anoble1
245 128KB
Here is what I have: "BD_BDT" is the Field name. Table name is "QS36F_MNBDD"

I tired this below. But got a data type mismatch
Expand|Select|Wrap|Line Numbers
  1. Expr1: CDate([BD_BDT])
Also tried:
Expand|Select|Wrap|Line Numbers
  1. Expr1: Format(CDate([BD_BDT]),"mm/dd/yyyy")
Aug 25 '14 #3
zmbd
5,501 Expert Mod 4TB
anoble1:
Open your table in design view.
Select the field that contains your data.
In that row, their is a column titled: "Data Type:
Please tell us what datatypecast is shown there.
Aug 25 '14 #4
anoble1
245 128KB
Data Type is Number. This is a linked table
Aug 25 '14 #5
twinnyfo
3,653 Expert Mod 2GB
So, your "date" is actually the "number" 20,140,519?

Then, I would recommend converting to Text (using CStr() Function).

But this is really not your problem. Although "20140519" looks like a date it is actually completely different, so, unless you want to convert this value every time you encounter it, you should probably look into getting the original data converted to a Date format.

Sooooooooo............. this might work:

Expand|Select|Wrap|Line Numbers
  1. Format(CDate(Left(CStr(20140519),4) & "-" & Mid(CStr(20140519),5,2) & "-" & Right(CStr(20140519),2)),"mm/dd/yyyy")
But it is a kinda silly way to do it. But it works.
Aug 25 '14 #6
anoble1
245 128KB
Yes, that worked great. Good idea about switching the Main data source.
Aug 25 '14 #7
twinnyfo
3,653 Expert Mod 2GB
Glad I could help! Hope you have a great day!
Aug 25 '14 #8
NeoPa
32,556 Expert Mod 16PB
You could try (for the date value) :
Expand|Select|Wrap|Line Numbers
  1. CDate(Format(lngDateVal,"0000\/00\/00"))
This assumes your date is stored in lngDateVal in the format specified.
Aug 25 '14 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: praba kar | last post by:
Dear All, In Php we can print RFC 2822 formatted date by date('r') with parameter r. Then it will print the below format date. "Thu, 7 Apr 2005 01:46:36 -0300". I want to print same RFC 2822...
2
by: amith | last post by:
hi I have written javascript for comparing two dates in US format and finding out whether the start date is greater than the end date and vice versa. In this attempt i have instantiated the...
4
by: peashoe | last post by:
I have an asp page that uses a calendar.js (pop-up) file to add an exact date format in the text field (txtDDate). My problem is I need some javascript that sets an alert that does not allow them...
0
by: lsy | last post by:
i wonder how can i order my statement interm of "ORDER BY FORMAT(Date, 'mmmm')" with using this it will order by alphabethical but i want it order by month... i had try "ORDER BY FORMAT(Date, 'mm')"...
3
by: Jim | last post by:
I'm having a problem with a date query..im trying to pull customer data based on a date specified from a form to 3 months prior to the date specified. So lets say in the form I specified 1/2/2004....
3
by: Don Sealer | last post by:
I'd like to have a query that would pull data only for the current month. Can you tell me how I would write that? Thanks, Don.............
1
by: G Gerard | last post by:
Hello I am having some problem comparing dates with the SQL statement below MySQL = "SELECT Format(Date, 'yyyy/mmmm/dd') as FROM TblDates WHERE _ Format(Date, 'yyyy/mmmm/dd') =...
9
by: samuelberthelot | last post by:
Hi, Here is what I'm trying to achieve: The user enters in the textbox a date. It can be: 02.05.2006 or any other separator... 02052006 020506 any other formats should be forbidden. When...
8
by: libsfan01 | last post by:
how can i use regular expressions to ensure a mysql format date entry in a text field? thanks marc
3
by: Robert | last post by:
I need to set up a query that will pick out records for the current winter season. I.e., each season runs from October 1 until March 31. The catch is, the year can't be hard coded. So, if the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.