problems importing date variable from Access

[Cleland, Patricia (EDU)]

I have an Access data base which contains a time variable (time_of_occurrence) in hh:mm:ss AM (or PM) format.  When I import the data into SPSS, the AM/PM part is stripped off and a date in dd-mmm-yyyy format is pre-pended to the time.  (In all cases, the date pre-pended is 30-Dec-1899.) 

 

Here’s an example of the data in Access and in SPSS:

 

Value in Access	Value in SPSS
12:11:00 PM	30-Dec-1899 12:11:00
9:10:00 AM	30-Dec-1899 09:10:00
12:02:00 PM	30-Dec-1899 12:02:00
11:12:00 AM	30-Dec-1899 11:12:00

 

Here’s the syntax that I used: 

 

GET DATA

  /TYPE=ODBC

  /CONNECT='DSN=MS Access Database;DBQ=mydatabase;'

  /SQL='SELECT TIME_OF_OCCURRENCE  FROM [event By Date and Time]'

  /ASSUMEDSTRWIDTH=255.

CACHE.

EXECUTE.

 

I tried to export the time variable to Excel as a work around and for all cases the value for time_of_occurrence became 00/01/1900. Obviously, that wasn’t successful.

Any suggestions to import the data correctly would be appreciated.

Pat

[Rick Oliver-3]

You could use the XDATE function to extract just the time value and throw away the meaningless date portion.

From:	"Cleland, Patricia (EDU)" <[hidden email]>
To:	[hidden email]
Date:	09/22/2010 01:17 PM
Subject:	problems importing date variable from Access
Sent by:	"SPSSX(r) Discussion" <[hidden email]>

---

I have an Access data base which contains a time variable (time_of_occurrence) in hh:mm:ss AM (or PM) format.  When I import the data into SPSS, the AM/PM part is stripped off and a date in dd-mmm-yyyy format is pre-pended to the time.  (In all cases, the date pre-pended is 30-Dec-1899.)  
 
Here’s an example of the data in Access and in SPSS:
 

Value in Access	Value in SPSS
12:11:00 PM	30-Dec-1899 12:11:00
9:10:00 AM	30-Dec-1899 09:10:00
12:02:00 PM	30-Dec-1899 12:02:00
11:12:00 AM	30-Dec-1899 11:12:00

 
Here’s the syntax that I used:  
 
GET DATA
  /TYPE=ODBC
  /CONNECT='DSN=MS Access Database;DBQ=mydatabase;'
  /SQL='SELECT TIME_OF_OCCURRENCE  FROM [event By Date and Time]'
  /ASSUMEDSTRWIDTH=255.
CACHE.
EXECUTE.
 
I tried to export the time variable to Excel as a work around and for all cases the value for time_of_occurrence became 00/01/1900. Obviously, that wasn’t successful.

Any suggestions to import the data correctly would be appreciated.
Pat

[Rick Oliver-3]

It looks like Access doesn't have a concept of a time that doesn't include a date and so assumes a time without a date is the first day of it's internal notion of year 0.

So use xdate.time to remove the extraneous date, as in:

compute newvar=xdate.time(oldvar).
formats newvar (time12.2)

From:	Rick Oliver/Chicago/IBM
To:	"Cleland, Patricia (EDU)" <[hidden email]>
Cc:	[hidden email]
Date:	09/22/2010 01:55 PM
Subject:	Re: problems importing date variable from Access

---

You could use the XDATE function to extract just the time value and throw away the meaningless date portion.

From:	"Cleland, Patricia (EDU)" <[hidden email]>
To:	[hidden email]
Date:	09/22/2010 01:17 PM
Subject:	problems importing date variable from Access
Sent by:	"SPSSX(r) Discussion" <[hidden email]>

---

I have an Access data base which contains a time variable (time_of_occurrence) in hh:mm:ss AM (or PM) format.  When I import the data into SPSS, the AM/PM part is stripped off and a date in dd-mmm-yyyy format is pre-pended to the time.  (In all cases, the date pre-pended is 30-Dec-1899.)  
 
Here’s an example of the data in Access and in SPSS:
 

Value in Access	Value in SPSS
12:11:00 PM	30-Dec-1899 12:11:00
9:10:00 AM	30-Dec-1899 09:10:00
12:02:00 PM	30-Dec-1899 12:02:00
11:12:00 AM	30-Dec-1899 11:12:00

 
Here’s the syntax that I used:  
 
GET DATA
  /TYPE=ODBC
  /CONNECT='DSN=MS Access Database;DBQ=mydatabase;'
  /SQL='SELECT TIME_OF_OCCURRENCE  FROM [event By Date and Time]'
  /ASSUMEDSTRWIDTH=255.
CACHE.
EXECUTE.
 
I tried to export the time variable to Excel as a work around and for all cases the value for time_of_occurrence became 00/01/1900. Obviously, that wasn’t successful.

Any suggestions to import the data correctly would be appreciated.
Pat