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

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.
0
shwelosa
Asked:
shwelosa
  • 6
  • 6
  • 2
  • +2
1 Solution
 
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
 
BrandonGalderisiCommented:
you REALLY don't want to store dates as integers.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
Gustav BrockCIOCommented:
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
 
GRayLCommented:
You have to use your own table and field names, replacing myTable, fldDate and fldDateLng accordingly
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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