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)

  • 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 Jan 11 '18 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 Jan 11 '18 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 Jan 11 '18 at 19:47
  • so when checked in a txt editor it is the correct format? – Gortonington Jan 11 '18 at 21:07
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

  • Could you provide a sample query that includes this conversation function? – Deployment Failure Jan 11 '18 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 Jan 11 '18 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 Jan 11 '18 at 18:38
1

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.

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.

  • They show up as Tuesday, October 10, 2017 4:16 AM which is neither format above. – Deployment Failure Jan 11 '18 at 16:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.