Date Problem

[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

[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?)

[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]