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

11 Years
Discussion Span
Last Post by KeiHimekawa

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


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]
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.