Solved Date Format Changed When Exporting to CSV

[AngelSpeaks]

I have a table, tblCPUpload. The two date columns have dates stored as mm/dd/yyyy without time stamp. An image of the table in datasheet view is below. PayrollStart and PayrollEnd, Date/Time columns

My code to extract rows, based on project number (the code below is using Job number), and create a CSV file for each project is as follows;

 Do While Not rsJobs.EOF
        Job = rsJobs!Job
        Dim rsExportSQL As String
        
        'Set up from/to for Job Selection
        TempVars.Add "Job", Job
        TempVars.Add "JobTo", Job
        
        'Note: Job is a text field in tblCPUpload
        rsExportSQL = "SELECT * FROM tblCPUpload " _
         & "WHERE (ProjectNumber= " & "'" & Job & "'  AND ProjectState = " & "'" & gstrState & "')"
        Dim rsExport As DAO.QueryDef

        Set rsExport = CurrentDb.CreateQueryDef("myExportQueryDef", rsExportSQL)
        
        filename = directoryName & "\State " & gstrState & " Job " & Job & " Start Date " & sDate & " - CP Upload.csv"
        
        sFile = filename
        DoCmd.TransferText acExportDelim, , "myExportQueryDef", filename, True
        rsJobs.MoveNext
    Loop
     msgText = "Extract Completed, look at folder " & gExportPath & " for extracted CSV files"
     Response = MsgBox(msgText, vbOKOnly, msgApp)
End Sub

The second image is of the resulting CSV file with time as part of the date.

What am I doing wrong here? Thanks.

 

[CJ_London]

you are exporting to a .csv file - open the file in notepad, not excel. What do you see there? And do this before you open in Excel, Excel will often reformat data (format has nothing to do with the underlying data, just what it looks like).

The same goes for Access

the two date columns have dates stored as mm/dd/yyyy without time stamp

Are you sure that is what they are stored as and not just formatted to look like that, i.e. hide any time element? Remove any formatting from your table so you can see what you actually have.

 

[Pat Hartman]

The two date columns have dates stored as mm/dd/yyyy

Dates are NEVER stored as strings. Dates are stored as double precision numbers. What you are seeing is an illusion.
NEVER format fields at the table level. That just obfuscates the actual contents.

Usually when exporting to Excel or .csv, you would use the Format() function in the query to format dates and money fields to get them to look the way you want them to.

Select ..., Format(MyDate, "mm/dd/yyyy") as FormattedMyDate from Yourtable

 

[AngelSpeaks]

Dates are NEVER stored as strings. Dates are stored as double precision numbers. What you are seeing is an illusion.
NEVER format fields at the table level. That just obfuscates the actual contents.

Usually when exporting to Excel or .csv, you would use the Format() function in the query to format dates and money fields to get them to look the way you want them to.

Select ..., Format(MyDate, "mm/dd/yyyy") as FormattedMyDate from Yourtable

Thanks! It worked!

 

[Pat Hartman]

You're welcome