Date Conversion

[Tarbuza]

Hi All,

I have a date in one field as follows:

mm/dd/yy = 03/12/07

I have a blank field that I want to update from this above field as follows:


MNTH. dd, yy = March 12, 2007

I want to come up with an update query to updat the field as mentioned above.

Any help would be greatly appreciated.

Thanks.
Tar

 

[SkipVought]

Hi,

Dates are Dates. By that I mean that if you have a REAL DATE and not some text represention of a date, its a NUMBER.

http://support.microsoft.com/kb/210276

What you SEE is a Display Format.

Now if you want to convert the Datevalue to TEXT, check out the Format function. But the TEXT value renders the Date much less useful than a dateValue.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[Tarbuza]

Hi Skip,

I went through the URL that you listed but it essentially shows the numeric value.

My question is quite different than what this URL is trying to illustrate.

Take care,
Tar

 

[SkipVought]

Is the field that you are updating a TEXT field?

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[lespaul]

Kudos to MajP for this awesome explanation of the DATE/TIME field type:
[tt]The issue is formatting. Format the table, form, or query fields/control as you want to see the information displayed.

Todays date (9/26/2007) is the number 39351 in every table or variable in access regardless of any formatting. It is 39351 since there are 39351 days since 30 Dec 1899. The baseline date is 30 dec 1899 which is the value 0. It is now 10:32:17 PM which is a value of .9390856481. Because 10:32 is about 22.5 hours of a complete day which is about .939 of a day. So 9/26/2007 10:32:17 PM is the number
39351.9390856481. So the difference between now and date is
39351 vs 39351.9390856481. But it just does not matter because I can show these numbers any way I want in a field, table, query, or form.

Public Sub testDate()
Debug.Print CDate(1)
Debug.Print CDate(-1)
Debug.Print Date
Debug.Print CDbl(Date)
Debug.Print Now
Debug.Print CDbl(Now())
Debug.Print #9/26/2007# - #12/31/1899#
Debug.Print Format(CDate(39351.9381), ShortDate)
Debug.Print Format(CDate(39351.9381), "dd mmm yyyy")
Debug.Print Format(CDate(39351.9381), "hh:NN:SS AM/PM")
End Sub

12/31/1899
12/29/1899
9/26/2007
39351
9/26/2007 10:32:17 PM
39351.9390856481
39350
9/26/2007 10:30:52 PM
26 Sep 2007
10:30:52 PM[/tt]

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins