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
  • 78 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by KeiHimekawa
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.