TSQL Convert SQL smalldatetime to string for MS Access Date/Time

Hi All,

I have a stored procedure which collects a recordset which populates and array. This array array in turns loops and populates an MS Access 2007 Database table.

Everything goes well right up to 'Report_DC' (Date Created). This either copies poorly or not at all. There are no NULL's all the reports have a value for this field which look like this: 2012-11-02 17:38:00

I have tries

,  CAST (rpt.Report_DC as varchar)

but it does not work. When I open the Access table I get 'XXXXXXXXXXXX'

Any ideas.

Kind regards

Abiel M de Groot Sanders
Abiel de GrootDeveloperAsked:
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
Convert ( VARCHAR(10), report_dc ,120)

See also my article
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
In the receiving table in Access, what is the Data type of the field receiving the data from "Report_DC"?

When I open the Access table I get 'XXXXXXXXXXXX'
So you are getting data into the field, but it's not what you expect? You're getting the XXXXXXXXXX string instead?
Abiel de GrootDeveloperAuthor Commented:
In the meantime I overlooked the fact there were some local solutions too. As I am using ASP I was simply able to use FormatDateTime(ArrReports(3,ki),2) to parse the string.

Kind regards

Abiel M de Groot Sanders
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.