I receive records from a company that is in a different time zone.
Unfortunately, when I import the records my SQL Server 2008 changes the time.
The main problem is that the company doesn't use time and so it defaults to
midnight. When my system gets the data it changes it to the prior day and
makes it 10:00 PM. I need to keep the date as is, because the date is the
main information I need, but with it being a day behind my data is inaccurate.
Anyone know a way around this through SQL Server 2008? I thought
datetimeoffset might help, but when I import the data I get all kinds of
errors. I noticed that the other company uses a format of YYYY-MM-DD-TTTT
and -05:00. I believe the -05:00 is the time zone that the other company is
in.

So my main objective is to keep the date. If I can keep the midnight time
great, but it is not necessary. I was hoping to easily solve this through
SQL Server and not have to parse the original data.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...setup/201002/1

Hoardling1 via SQLMonster.com (u43783@uwe) writes:
It would certainly have helped if you had told us how you import the data.

I guess that you may be using the Import Wizard, of which I don't know
that much, but it is probably not that intelligent to deal with this.
After all, since the time zone is included, that is an indication of time,
so if you are in -07:00, and the data says 2010-03-01-05:00, it seems
correct to import it as 2010-02-28 22:00.

One approach would be to import the data into a staging table, and then
apply dateadd when you copy the data to the target table.

Another approach would be to use BCP, and use a format file where you
specify the date field to be 10 charcters followed by another field for
which you specify target column 0, which means that you don't import it.
Hm, maybe you can do something similar in the import wizard as well.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Import is based on OpenRowSet due to the data being in an XML format.

Erland Sommarskog wrote:
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...setup/201003/1

Hoardling1 via SQLMonster.com (u43783@uwe) writes:
Maybe then you could care to give more details? How does the XML
document look like? How does your code look like?




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

The XML format is datetime - 2010-03-06T00:37:48-05:00, which came from an
Oracle system we are just loading it into a datetime field into SQL Server
2008.
The code doesn't do any truncating or changing of data just importing it into
the datetime field.

I want to keep the time 2010-03-06T00:37:48 and not have it adjust to my time
zone.

Hopefully this information helps.

Erland Sommarskog wrote:
--
Message posted via http://www.sqlmonster.com

Hoardling1 via SQLMonster.com (u43783@uwe) writes:
You probably need to cut off the time zone.

Well, I am not the person in need of help. But the better you help me,
the better I can help you. If you want to know how to cut the timezone,
you need to show me your code. A sample XML document can help to.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx