Prev: bat script in dts
Next: DeadLock
From: Roger on 31 May 2010 08:40
We are in the process of upgrading our systems entirely. Making huge changes
to the hardware and software.

One change is going from SQL Server 2000 to 2008.

2008 has the new datetimeoffset data type, which for us could be a God-send.

In reading online however, it seems that the date time is always passed in
as a string. Is there no function that allows us to pass in a datetime and
an offset or do we actually have to do as I have seen all over the the
Internet and convert our dateTime values to a string, then concatonate the
offset and pass it in that way?

If we DON'T have to do that, then how can we do it? (I have been trying to
figure this out and so far no luck. I cannot imagine you have to deal with
strings all the time.)

Currently we have tables that use DateTimes, and we want to add a
DateTimeOffset field for each, then convert using an SQL update.


From: Erland Sommarskog on 31 May 2010 09:15
Roger (rogerdev(a)vnet.on.ca) writes:
> 2008 has the new datetimeoffset data type, which for us could be a
> God-send.
>
> In reading online however, it seems that the date time is always passed in
> as a string. Is there no function that allows us to pass in a datetime and
> an offset or do we actually have to do as I have seen all over the the
> Internet and convert our dateTime values to a string, then concatonate the
> offset and pass it in that way?

Depends a little on what you mean. There are no datetime literals in
SQL Server, but there are string literals, and string literals can be
implicitly or explicitly converted to datetimeoffset.

> If we DON'T have to do that, then how can we do it? (I have been trying to
> figure this out and so far no luck. I cannot imagine you have to deal with
> strings all the time.)
>
> Currently we have tables that use DateTimes, and we want to add a
> DateTimeOffset field for each, then convert using an SQL update.

You can use ALTER TABLE to change the data type, but the important
thing to understand that in lieu of any other information will assume
that your datetime values are UTC, look at this example:

create table roger (d datetime)
go
insert roger values (getdate())
go
alter table roger alter column d datetimeoffset
go
select getdate(), d from roger
go
drop table roger

If your date values are in some other time zone, for instance -04:00, you
will need run a dateadd operation to change the values. Note that if you
have just used "local time", then you will also have to adjust for Daylight
Savings Time.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: John Bell on 31 May 2010 13:53
On Mon, 31 May 2010 08:40:18 -0400, "Roger" <rogerdev(a)vnet.on.ca>
wrote:

>We are in the process of upgrading our systems entirely. Making huge changes
>to the hardware and software.
>
>One change is going from SQL Server 2000 to 2008.
>
>2008 has the new datetimeoffset data type, which for us could be a God-send.
>
>In reading online however, it seems that the date time is always passed in
>as a string. Is there no function that allows us to pass in a datetime and
>an offset or do we actually have to do as I have seen all over the the
>Internet and convert our dateTime values to a string, then concatonate the
>offset and pass it in that way?

>
>If we DON'T have to do that, then how can we do it? (I have been trying to
>figure this out and so far no luck. I cannot imagine you have to deal with
>strings all the time.)
>
>Currently we have tables that use DateTimes, and we want to add a
>DateTimeOffset field for each, then convert using an SQL update.
>

Have you looked at the TODATETIMEOFFSET function?

John
From: Roger on 31 May 2010 14:28
That's what I am looking for!

Thank you!

"John Bell" <jbellnewsposts(a)hotmail.com> wrote in message
news:klt706don4fp3jp89c3kj0h5fm6pv1m6ka(a)4ax.com...
> On Mon, 31 May 2010 08:40:18 -0400, "Roger" <rogerdev(a)vnet.on.ca>
> wrote:
>
>>We are in the process of upgrading our systems entirely. Making huge
>>changes
>>to the hardware and software.
>>
>>One change is going from SQL Server 2000 to 2008.
>>
>>2008 has the new datetimeoffset data type, which for us could be a
>>God-send.
>>
>>In reading online however, it seems that the date time is always passed in
>>as a string. Is there no function that allows us to pass in a datetime and
>>an offset or do we actually have to do as I have seen all over the the
>>Internet and convert our dateTime values to a string, then concatonate the
>>offset and pass it in that way?
>
>>
>>If we DON'T have to do that, then how can we do it? (I have been trying to
>>figure this out and so far no luck. I cannot imagine you have to deal with
>>strings all the time.)
>>
>>Currently we have tables that use DateTimes, and we want to add a
>>DateTimeOffset field for each, then convert using an SQL update.
>>
>
> Have you looked at the TODATETIMEOFFSET function?
>
> John


 | 
Pages: 1
Prev: bat script in dts
Next: DeadLock