Formatting yyyy-MM-ddTHH:mm:ss+00:00 into SQL Server datetime format

Question

I am getting back a date time from MailChimp that has the format yyyy-MM-ddTHH:mm:ss+00:00. An example is 2016-11-14T17:31:19+00:00.

I am not sure how I should reformat this to fit into the SQL Server Datetime format. I am coding in PowerShell.

#HTTP Get returns datetime from mailchimp in format yyyy-MM-ddTHH:mm:ss+00:00
$MailChimpTimeGMT = Invoke-RestMethod -Method Get -Uri "$URL$endpoint" -Headers $auth -ContentType "application/json"

$formattedTime = ((Get-Date).ToUniversalTime()).ToString("yyyy-MM-ddTHH:mm:ss+00:00")

if ( $formattedTime -lt $MailChimpTimeGMT ) {
  #$DBase and $DBSvr declared
  #
  #Need to convert $MailChimpTimeGMT in match SQL date time format without loss
  #
  $sqlcmd = "INSERT INTO dbo.MAILCHIMP_LISTSERVE (TIME_STAMP,MODIFY_SOURCE)" +
            " VALUES (" +  $MailChimpTimeGMT + ", powershell );"

  $data = Invoke-Sqlcmd -Query $sqlcmd -Database $DBase -ServerInstance $DBSvr 
}

Google cast and convert. The number you are looking for is 127. — Zohar Peled, Nov 22 '16 at 18:11
Btw, [date is not stored with it's display format in sql server.](http://stackoverflow.com/a/30033028/3094533) — Zohar Peled, Nov 22 '16 at 18:15

score 0 · Answer 1 · answered Nov 22 '16 at 21:50

You should be able to insert the timestamp if you change it to smalldatetime format. The simplest way would be to replace the T with a space, remove the timezone offset, and just insert the UTC timestamp:

$ts = $MailChimpTimeGMT -replace '^(.*?)T(.*?)\+.*', '$1 $2'
$sqlcmd = "INSERT INTO dbo.MAILCHIMP_LISTSERVE (TIME_STAMP, MODIFY_SOURCE)" +
          " VALUES ('$ts', 'powershell');"

If you want to be a little more diligent you'd use a more precise regular expression for matching a timestamp: ^(\d{4}-\d{2}-\d{2})T(\d{2}:\d{2}:\d{2})\+\d{2}:\d{2}$.

If you want to be really diligent you actually parse and re-format the timestamp.

$infmt   = 'yyyy-MM-ddTHH:mm:sszzz'
$outfmt  = 'yyyy-MM-dd HH:mm:ss'
$culture = [Globalization.CultureInfo]::InvariantCulture

$ts = [DateTime]::ParseExact($MailChimpTimeGMT, $infmt, $culture).ToString($outfmt)
$sqlcmd = "INSERT INTO dbo.MAILCHIMP_LISTSERVE (TIME_STAMP, MODIFY_SOURCE)" +
          " VALUES ('$ts', 'powershell');"

Formatting yyyy-MM-ddTHH:mm:ss+00:00 into SQL Server datetime format

1 Answers1