asked on

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott McDaniel (EE MVE )

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?

Guy Hengel [angelIII / a3]

https://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don't-be-scared-and-do-it-right-the-first-time.html

Abiel de Groot

ASKER

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