0

When I export my data from my Data Extensions the Date/Time fields print in the following format 11/16/2010 12:00:00 AM, how can I export them in this format YYYY-MM-DD HH:MM:ss?

(This is how they were originally inserted into the data extension)

Improve this question
4
  • This is likely because of how your CSV natively handles the Date/Time field from SFMC. Once it is exported, just open it in excel and change the date format there. There is no real way to work around this except to change the Date/Time field to be text in SFMC, which can then create issues when trying to filter or sort.
    – Gortonington
    Commented Jan 11, 2018 at 19:23
  • The files are stored in the later format I’ve mentioned above, this has been checked by opening the file in a text editor.
    – Deployment Failure
    Commented Jan 11, 2018 at 19:46
  • Due to the sheer size of the CSV file i will be unable to open the file in excel and try to reformat (more than 1M records)
    – Deployment Failure
    Commented Jan 11, 2018 at 19:47
  • so when checked in a txt editor it is the correct format?
    – Gortonington
    Commented Jan 11, 2018 at 21:07

3 Answers 3

Reset to default
2

I was able to achieve this by creating a new Data Extension that copies the fields from the source Data Extension. The fields which need to be in a certain date format I had used the Text field type instead of Date and casted it using SQL.

Improve this answer
1

Write a query activity to move the data from your Data Extension to another DE. In your query, use Date format conversion functions to get the date into desired format. In your target DE, get this value in text format

Improve this answer
3
  • Could you provide a sample query that includes this conversation function?
    – Deployment Failure
    Commented Jan 11, 2018 at 17:53
  • Can be done using Format() or Convert() function of SQL Server. I believe this one should meet your needs - SELECT convert(varchar, getdate(), 120)
    – Maneesh
    Commented Jan 11, 2018 at 18:17
  • Actually on second thoughts this won’t solve it, the issue is in my exported CSV file the format isn’t correct, by doing this to another DE won’t solve that.
    – Deployment Failure
    Commented Jan 11, 2018 at 18:38
0

When you view the data in the data extension, does it show as : 11/16/2010 12:00:00 AM or YYYY-MM-DD HH:MM:ss ?

Perhaps check the Date Format of your Business unit and try changing that and seeing if it changes the export format.

Improve this answer
1
  • They show up as Tuesday, October 10, 2017 4:16 AM which is neither format above.
    – Deployment Failure
    Commented Jan 11, 2018 at 16:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .