0

We're trying to migrate our DWH from current SQL Server 2016 to AWS Redshift by AWS SCT (Schema Conversion Tool).

Our SQL Server tables have the columns of 'datetime'(YYYY-MM-DD hh:mm:ss.000). They are normally converted to Redshift timestamp columns by SCT, at schema level. But data copy to Redshift was failed by SCT Data Extract Agent. (Extracted data was successfully uploding to S3)

I suppose that's due to datetime type difference, even though I believe Redshift timestamp allows until 6 digits in second scale.

If you have any workaround for this, kindly let me know how to convert them without any issues.

Sincerly, Sachiko

Julie
  • 123
  • 8
Sachiko
  • 808
  • 1
  • 12
  • 31
  • 1
    what - specifically - makes it believe it is the datetime/timestamp that causes this? are there error messages? – Paul Maxwell Sep 14 '18 at 04:12
  • Thanks for your attention, @Used_By_Already. SCT completed extract and copy to Redshift but I can't select timestamp data by sql with following error, that's maybe due to format difference mentioned below answer : [Amazon](500339) Unexpected error consuming date type from server, source data: "09/14/2018" 1 statement failed. – Sachiko Sep 14 '18 at 05:13
  • 1
    mm/dd/yyyy is the most insanely bizarre and useless string representation of dates ever.... less then 10% of the worlds population use it at all and why have a set of measurements that starts with a middle size unit, then a smaller unit then a larger unit back to much snaller units - totally illogical. Good Luck. – Paul Maxwell Sep 14 '18 at 05:48
  • You're totally right, @Used_By_Already. Date time format issues always drive me mad, as our date format is bit different from mm/dd/yyyy, official ones :) Anyway, I really appreciate all of your considerations, good day! – Sachiko Sep 14 '18 at 06:04

1 Answers1

2

The equivalent column is most likely timestamp without the timezone, and it'll be stored in UTC. The format of the dumped values may cause problems if it's not one of the formats that RedShift/Postgres expects, though, too.

  • 1
    Many, many thanks for your prompt answer, @The UNIX Man. I understand Redshift 'timestamp' is appropriate data type for SQL Server datetime. And thanks to your useful link, we might need to dump datetime data in format 'yyyymmdd hh:mm:ss.sss', not current 'YYYY-MM-DD hh:mm:ss.sss' as SQL Server locale. (Just I hope I could find any way to convert this format automatically by SCT...!) Thank you so much. – Sachiko Sep 14 '18 at 05:10