Dates / DateSerial

Dates / DateSerial in MS Access Interactive topic (part of our Microsoft Access group).

Quick Search: DateSerial   Dates /   Dates / DateSerial  
 Dates / DateSerial Post ID #6441, 2 replies 6/3/2002 8:37:00 AM  
KCRABBE
OTTAWA, ON, CANADA
I imported a .txt file into Access 2000.

One file has a Date of Birth (DOB) field. In the .txt file it is a text field with the structure: yyyymmdd

If I change the data field type to Date/Time when I import (through the specifications), I generate errors for every record.

If I leave the data field as text, import the file and then attempt to change the field type in the table structure, I get the same set of errors.

One person suggested using the DateSerial in a query and create a new field which is of the type date and breaks the date into its component parts separated with a - (2001-01-01). I used the code sent and indeed it did split the yyyymmdd into yyyy-mm-dd (format is great), only problem is that it changed the dates. . . 20010101 became 2008-10-18. My guess is that I am using the DateSerial function incorrectly in the query (well, that's pretty obvious).

Any suggestions? Or, can anyone tell me how to use the DateSerial function properly so that it translates yyyymmdd to yyyy-mm-dd and changes the field type from text to date?

Thanks, KC
About KCRABBE
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
Post ID #6441, 2 replies
Thread Started 6/3/2002 8:37:00 AM
View Counter=1741
Location=OTTAWA, ON, CANADA  
Joined=11/17/2000   MB Posts=32  
Most Recent Post
 Re: Dates / DateSerial 6/3/2002 12:19:00 PM  
KCRABBE
OTTAWA, ON, CANADA
And now I can sleep tonight!

Thanks so much for your reply . . .take care, kc
About KCRABBE
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
Post ID #6458 (Level 1.1)  Reply to 6441
Thread Started 6/3/2002 12:19:00 PM
View Counter=2
Location=OTTAWA, ON, CANADA  
Joined=11/17/2000   MB Posts=32  
 Re: Dates / DateSerial 6/3/2002 10:31:00 AM  
jdschram
MN
to change them to yyyy-mm-dd format from yyyymmdd you can do the following

assuming you have a table named Table1 and the text column named date2

SELECT Left([table1]![date2],4) & "-" & Left(Right([table1]![date2],4),2) & "-" & Right([table1]![date2],2) AS Expr1, table1.date2
FROM table1;

this is just manipulating the string takeing the left 4 characters (the year) and adding a - character then takinge the right 4 characters and taking the left 2 characters (the month) adding a - and then the right 2 charactars (the day)

its now just a matter of making this an update SQL statement instead of a select

HTH
JS
About jdschram
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
Post ID #6452 (Level 1.2)  Reply to 6441
Reply Posted 6/3/2002 10:31:00 AM
Location=MN  
Joined=3/6/2001   MB Posts=259  
www.prestwood.com For service: 916-726-5675
or support@prestwood.com
Copyright (C) Prestwood IT Solutions.
All Rights Reserved.		 Printed 9/14/2026
↑