CODEWITHSUNDEEP
c#sqlbiztalkpsqlbiztalk-2013
user4912134
user4912134

Reputation: 1043

Convert string in to the DateTimeoffset in SQL Server

I am trying to pull data from the psql database and insert them into the SQL Server database using BizTalk. There is a column in psql called createddate of type TimeStamp with time zone like 6/30/2016 12:00:00 AM

I want to insert that data into the SQL Server column called DateCreated of type datetimeoffset. Since I am using BizTalk all the data are handled as sting so I am using the following script like

public string ConvertDateCreated(string dateCreated)
{
     System.Globalization.CultureInfo provider = System.Globalization.CultureInfo.InvariantCulture;
     return DateTime.ParseExact(dateCreated, "MMddyyyy", provider).ToString("yyyyMMdd");
}

enter image description here

But it is throwing an error:

String was not recognized as a valid DateTime.

Exception type: FormatException
Source: mscorlib
Target Site: System.DateTime ParseExact(System.String, System.String, System.Globalization.DateTimeFormatInfo, System.Globalization.DateTimeStyles)

The following is a stack trace that identifies the location where the exception occurred

at System.DateTimeParse.ParseExact(String s, String format, DateTimeFormatInfo dtfi, DateTimeStyles style)
at System.Xml.Xsl.CompiledQuery.Script1.ConvertDateCreated(String dateCreated)
at (XmlQueryRuntime {urn:schemas-microsoft-com:xslt-debug}runtime, XPathNavigator {urn:schemas-microsoft-com:xslt-debug}current)
at (XmlQueryRuntime {urn:schemas-microsoft-com:xslt-debug}runtime, XPathNavigator {urn:schemas-microsoft-com:xslt-debug}current)
at Root(XmlQueryRuntime {urn:schemas-microsoft-com:xslt-debug}runtime)
at Execute(XmlQueryRuntime {urn:schemas-microsoft-com:xslt-debug}runtime)
at System.Xml.Xsl.XmlILCommand.Execute(Object defaultDocument, XmlResolver dataSources, XsltArgumentList argumentList, XmlSequenceWriter results)
at System.Xml.Xsl.XmlILCommand.Execute(Object defaultDocument, XmlResolver dataSources, XsltArgumentList argumentList, XmlWriter writer)
at System.Xml.Xsl.XslCompiledTransform.Transform(IXPathNavigable input, XsltArgumentList arguments, XmlWriter results, XmlResolver documentResolver)

Upvotes: 1

Views: 1496

Answers (3)

Ed Bangga
Ed Bangga

Reputation: 13006

First is you need to get the datetime format of your psql datetime "M/d/yyyy hh:mm:ss tt"

Then use DateTimeOffset to parse your psql datetime

public static string ConvertDateCreated(string dateCreated)
    {
        System.Globalization.CultureInfo provider =  System.Globalization.CultureInfo.InvariantCulture;
        return DateTimeOffset.ParseExact(dateCreated, "M/d/yyyy hh:mm:ss tt", provider).ToString();
    }

To check the result, cast the output using your sql datetimeoffset

select cast('6/30/2016 12:00:00 AM +00:00' as datetimeoffset)

Try .NET fiddle https://dotnetfiddle.net/ZdpLFA

Upvotes: 0

rupweb
rupweb

Reputation: 3328

The format that worked for me for a timezone sql server datetimeoffset type was:

c.CreationDateTime = DateTime.ParseExact(rdr["CreationDateTime"].ToString(),
                     "dd/MM/yyyy HH:mm:ss zzz", 
                     CultureInfo.InvariantCulture);

Upvotes: 0

Serkan Arslan
Serkan Arslan

Reputation: 13393

For ParseExact, your source date format should be like "M/dd/yyyy hh:mm:ss tt".

public static string ConvertDateCreated(string dateCreated)
{
    System.Globalization.CultureInfo provider = 
         System.Globalization.CultureInfo.InvariantCulture;
    return DateTime.ParseExact(dateCreated, "M/dd/yyyy hh:mm:ss tt", provider)
             .ToString("yyyyMMdd");
}

Upvotes: 1

Related Questions