Member Avatar for saurabh_kanwar

Hi Guys
I've got a bit of problem with date.
When I transfer data of a table to txt file the date column that appears in the table like "28/03/2001" changes to "28/3/2001 0:00:00" in the txt file. I've tried to change the format of date in the table but it doesn't help.I'm using the following code.

Private Sub Command2_Click()
DoCmd.TransferText acExportDelim, "PESALES Export Specification", "PESALES", "J:\Power Equipment\Common\SK\PESALES.TXT"
End Sub

Thanks for your time

  • 3 Contributors
  • 2 Replies
  • 93 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by KeiHimekawa

Dani AI

Generated

The value you see in Access (28/03/2001) is a display format. When Access writes a table out it serializes the underlying Date/Time value, so the exported file often shows the raw value (including the time part). was right that this is an export/code-side issue, and was right that converting the value before export solves it — but do that in the export query or on the source server so the file contains a text date exactly the way you want.

A simple, reliable approach is to export a query that returns a text-formatted date. Create a query that replaces the date field with a formatted text expression, save it, and export the query instead of the table. Example expression (Access):

SELECT [OtherField], Format([SaleDate], 'dd/mm/yyyy') AS SaleDateText
FROM PESALES;

Save the query and call TransferText (or use the Export Wizard) with the query name. The date will be written as the text string "28/03/2001" rather than "28/3/2001 0:00:00".

If the table is on SQL Server, do the conversion on the server side to reduce round trips. Use CONVERT with the right style (103 = dd/mm/yyyy, 23 = yyyy-mm-dd) and export the result set. Example (SQL Server):

SELECT OtherField, CONVERT(VARCHAR(10), SaleDate, 103) AS SaleDateText
FROM PESALES;

Notes and troubleshooting: an export specification can override types, so check it if you still see time stamps. Prefer an unambiguous export format (yyyy-mm-dd) if the CSV will be read on different regional settings. Avoid changing the table column type just for export; format-to-text in a query or in code is safer and reversible.

Recommended Answers

All 2 Replies

Member Avatar for campkev

this seems more like a code problem than an sql problem. try posting in the appropriate forum for the language you are using. (vbscript?)

Member Avatar for KeiHimekawa

I've been having such problem before. The reason why it chages "28/03/2001" to "28/3/2001 0:00:00" is because SQL requires most date/time parameter to be changed to such format. What I do is use CAST and SUBSTRING to convert the date intolong date.

Example: From "28/3/2001 0:00:00" I chaged it into "March 28, 2001"

Here's the code:

SELECT [whatever's the name of your date field], 
     CASE WHEN [date field] IS NOT NULL THEN 
           (SELECT SUBSTRING(CAST([date field] AS VARCHAR), 1, 11)) 
END AS [new date field name]  
FROM [whatever table you're using]
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.