Hi, I have a question dealing with moving data with a Date datatype from ms-sql to snowflake using Singer Taps. • Singer suggests that you should create dates in a datetime format https://github.com/singer-io/getting-started/blob/master/docs/BEST_PRACTICES.md . This would mean a date 2017/1/1 would be represented as this 2017-01-01T000000Z (January 1, 2017 120000AM UTC) • But really the time is artificial it is really a date in MS SQL (with no time component). • My issue is if I store this with a time component with a UTC offset, it is going to appear to be a different time in a different timezone. I live in New Zealand so it will be either out by 12 or 13 hours depending on daylight savings. It will make my querying difficult with a strange 12 hour offset. • Given the issue with timezone for dates and the preference for date to be stored in UTC time. How should you represent dates if the time component is irrelevant - especially when you consider the source system may not be UTC aware?

That doc is a bit misleading. Change "dates" in that section to "date-times". JSON schema supports strings in

date

format and most targets should be able to use that to create the right column type. So the tap may send a schema

{
  "type": "object",
  "properties": {
    "some_date": {
      "type": "string",
      "format": "date"
    }
  }
}

and the record might look like

{
  "some_date": "2021-09-20"
}

+1

for not converting dates to datetime, for the reason you mention that adding and then reversing the process by converting back into a date can have the averse effect of actually changing the original value.

Perfect, thank you very much @edgar_ramirez_mondragon and @aaronsteers. I really didn't want to set my dates as datetimes. My target is Snowflake, I was surprised that the current loaders don't seem to support the Date datatype. Dates get loaded as a string as a result. Perhaps I need to create a pull request to support dates?

@steve_clarke Maybe. Are you using the pipelinewise variant?

@edgar_ramirez_mondragon I believe I was using the default version "variant: datamill-co". Does the the pipelinewise variant support dates? Do you recommend the pipelinewise variant?

in general yes, I would recommend it, but it doesn't support

date

either 😅

Okay, good to know. It sounds like whatever option I choose I need to make a small adjustment. I'll give the pipelinewise variant a go. Thanks for your assistance @edgar_ramirez_mondragon much appreciated.

A contribution should be rather easy. Put the

date

type here and add to the test cases

Thanks @edgar_ramirez_mondragon, I will go ahead for this as it will solve my problem of dealing with dates. As you mentioned, it is really just a small change.