how do i convert an access date/time field value to integer?

I imported a  text file from a lotus approach database into ms access.  the date field is 1/27/1995 and i would like to convert it to an integer date field where it will read 12795.  From there i will export the data to a sql table where it is defined as an integer.  Please help.
shwelosaAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
Too much confusion here.

1. The integer value requested is not the numeric value of a date value but a value from the formatted date. Thus values will fall between 10100 and 123199 and the data type of the new field must be Long.

2. The conversion is taking place in Access. Thus an update query will use Access SQL.

3. Data export will happen later.

So all that is needed is a simple update query in Access:

UPDATE
  tblYourTable
SET
  YourLongField = Val(Format([YourDateField], mmddyy));

/gustav
0
 
peter57rCommented:
What datatype does it show as in the Access table?
0
 
shwelosaAuthor Commented:
some of the date fields show as date/time and some as text.  
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.

 
BrandonGalderisiCommented:
you REALLY don't want to store dates as integers.
0
 
shwelosaAuthor Commented:
i know.  however, it is what i have to deal with.  The program that is reading this table is a combination of cobol and sql.  sorry.
0
 
BrandonGalderisiCommented:
Well the SQL to convert that would be

select cast(replace(convert(varchar(10), YourDateField, 1) ,'/','') as int)

I don't know how you are loading it into SQL though
0
 
Gustav BrockCIOCommented:
Use the expression

Val(Format([YourDateField], mmddyy))

It will return an integer.

/gustav
0
 
GRayLCommented:
Use CInt(fldDate) - is all you need
0
 
GRayLCommented:
Sorry, the date now exceeds Integer limitations.  Use CLng(fldDate).  In your table create a Long field named lDate and run this query:

Update myTable SET lDate= CLng(fldDate)


0
 
GRayLCommented:
Sorry, CLng() rounds up.  use Int(fldDate)

UPDATE myTable SET iDate = Int(fldDate);


0
 
shwelosaAuthor Commented:
hi Brandon,

when i used the cast suggestion in access, i get an error.  it is syntax error (missing operator) in query expression tablename.cast(replace(convert(varchar(10), mydadefield, 1) ,'/','') as int).  pls help.
0
 
shwelosaAuthor Commented:
hi GrayL,

what is idate?
0
 
shwelosaAuthor Commented:
hi Cactus data,

How would i code the val stmt?
0
 
GRayLCommented:
It was supposed to be the new field you created in the table.  Just so all prefixes add up, we create a new field in the table named fldDateLng (Integer won't cut it).  Then we run this query:

UPDATE myTable SET fldDateLng=Int(fldDate);

Note the Int() function produces the integer value of a number, not an Integer datatype.
0
 
shwelosaAuthor Commented:
hi GrayL,
it is asking me to enter the value for flddatelng and if i tried to enter the value of 1 record, it says that the field is not updateable.
0
 
GRayLCommented:
Can you post the SQL of your update query?
0
 
GRayLCommented:
You have to use your own table and field names, replacing myTable, fldDate and fldDateLng accordingly
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.

All Courses

From novice to tech pro — start learning today.