DateTime with TimeZone ?

Are there any plans to enhance the DateTime datatype to be able to store a timezone, and provide timezone aware arithmetic functions ?

The lack of timezone support seems a glaring omission - especially given that Microsoft's biggest DB competitor (Oracle) has a timestamp with timezone datatype. At present, you have to code all this yourself in SQL 2005. Is this not something that should be built into the DBMS ?

Thanks,

Andy Mackie

You can store it as UTC.

HTH, Jens Suessmeyer.

datetime vs varchar

If I insert datatime values in a varchar datatype as opposed to datetime,
what am I losing out on ?
Would I be able to perform the same functions against a varchar datatype as
opposed to datetime such as datepart, datediff,etc. ?
Hi Hassan
Why don't you try it and see? Something like this should give you a start:
declare @.today varchar(30)
select @.today = getdate()
select @.today
select dateadd(mm, 1, @.today)
And then read Tibor's excellent article:
http://www.karaszi.com/sqlserver/info_datetime.asp
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
> If I insert datatime values in a varchar datatype as opposed to datetime,
> what am I losing out on ?
> Would I be able to perform the same functions against a varchar datatype
> as opposed to datetime such as datepart, datediff,etc. ?
>
>
|||Kalen,
Does not seem to be any difference with that example.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ODKGXIFbIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hi Hassan
> Why don't you try it and see? Something like this should give you a start:
> declare @.today varchar(30)
> select @.today = getdate()
> select @.today
> select dateadd(mm, 1, @.today)
> And then read Tibor's excellent article:
> http://www.karaszi.com/sqlserver/info_datetime.asp
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
>
|||Hassan would have found all that out if he read the article I pointed him
to.
;-)
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uB0OLMIbIHA.1376@.TK2MSFTNGP02.phx.gbl...
> Downsides of using varchar include:
> There's nothing stopping you from inserting invalid date (like Feb 30), or
> time values.
> Performing various datetime calculation might mean bad performance, since
> you might end up with a convert on the column side in your predicate.
> You need to decide on a format. If you want to retrieve the value and
> present it in a different format from what it is stored with you have more
> work to do.
> ...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Hassan" <hassan@.test.com> wrote in message
> news:evp1jaFbIHA.4180@.TK2MSFTNGP06.phx.gbl...
>
|||On Feb 11, 6:46Xam, "Hassan" <has...@.test.com> wrote:
> If I insert datatime values in a varchar datatype as opposed to datetime,
> what am I losing out on ?
> Would I be able to perform the same functions against a varchar datatype as
> opposed to datetime such as datepart, datediff,etc. ?
Nothing but you would end up with too much convertions if you want to
manipulate varchars
(ex order by, usage of datediff, dateadd,etc)
Always use proper DATETIME datatype to store dates and let your front
end application do the formation
sql

datetime vs varchar

If I insert datatime values in a varchar datatype as opposed to datetime,
what am I losing out on ?
Would I be able to perform the same functions against a varchar datatype as
opposed to datetime such as datepart, datediff,etc. ?Hi Hassan
Why don't you try it and see? Something like this should give you a start:
declare @.today varchar(30)
select @.today = getdate()
select @.today
select dateadd(mm, 1, @.today)
And then read Tibor's excellent article:
http://www.karaszi.com/sqlserver/info_datetime.asp
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
> If I insert datatime values in a varchar datatype as opposed to datetime,
> what am I losing out on ?
> Would I be able to perform the same functions against a varchar datatype
> as opposed to datetime such as datepart, datediff,etc. ?
>
>|||Kalen,
Does not seem to be any difference with that example.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ODKGXIFbIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hi Hassan
> Why don't you try it and see? Something like this should give you a start:
> declare @.today varchar(30)
> select @.today = getdate()
> select @.today
> select dateadd(mm, 1, @.today)
> And then read Tibor's excellent article:
> http://www.karaszi.com/sqlserver/info_datetime.asp
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
>> If I insert datatime values in a varchar datatype as opposed to datetime,
>> what am I losing out on ?
>> Would I be able to perform the same functions against a varchar datatype
>> as opposed to datetime such as datepart, datediff,etc. ?
>>
>|||Downsides of using varchar include:
There's nothing stopping you from inserting invalid date (like Feb 30), or time values.
Performing various datetime calculation might mean bad performance, since you might end up with a
convert on the column side in your predicate.
You need to decide on a format. If you want to retrieve the value and present it in a different
format from what it is stored with you have more work to do.
...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.test.com> wrote in message news:evp1jaFbIHA.4180@.TK2MSFTNGP06.phx.gbl...
> Kalen,
> Does not seem to be any difference with that example.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:ODKGXIFbIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> Hi Hassan
>> Why don't you try it and see? Something like this should give you a start:
>> declare @.today varchar(30)
>> select @.today = getdate()
>> select @.today
>> select dateadd(mm, 1, @.today)
>> And then read Tibor's excellent article:
>> http://www.karaszi.com/sqlserver/info_datetime.asp
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
>> If I insert datatime values in a varchar datatype as opposed to datetime, what am I losing out
>> on ?
>> Would I be able to perform the same functions against a varchar datatype as opposed to datetime
>> such as datepart, datediff,etc. ?
>>
>>
>|||Hassan would have found all that out if he read the article I pointed him
to.
;-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uB0OLMIbIHA.1376@.TK2MSFTNGP02.phx.gbl...
> Downsides of using varchar include:
> There's nothing stopping you from inserting invalid date (like Feb 30), or
> time values.
> Performing various datetime calculation might mean bad performance, since
> you might end up with a convert on the column side in your predicate.
> You need to decide on a format. If you want to retrieve the value and
> present it in a different format from what it is stored with you have more
> work to do.
> ...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Hassan" <hassan@.test.com> wrote in message
> news:evp1jaFbIHA.4180@.TK2MSFTNGP06.phx.gbl...
>> Kalen,
>> Does not seem to be any difference with that example.
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:ODKGXIFbIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> Hi Hassan
>> Why don't you try it and see? Something like this should give you a
>> start:
>> declare @.today varchar(30)
>> select @.today = getdate()
>> select @.today
>> select dateadd(mm, 1, @.today)
>> And then read Tibor's excellent article:
>> http://www.karaszi.com/sqlserver/info_datetime.asp
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
>> If I insert datatime values in a varchar datatype as opposed to
>> datetime, what am I losing out on ?
>> Would I be able to perform the same functions against a varchar
>> datatype as opposed to datetime such as datepart, datediff,etc. ?
>>
>>
>>
>|||On Feb 11, 6:46=A0am, "Hassan" <has...@.test.com> wrote:
> If I insert datatime values in a varchar datatype as opposed to datetime,
> what am I losing out on ?
> Would I be able to perform the same functions against a varchar datatype a=s
> opposed to datetime such as datepart, datediff,etc. ?
Nothing but you would end up with too much convertions if you want to
manipulate varchars
(ex order by, usage of datediff, dateadd,etc)
Always use proper DATETIME datatype to store dates and let your front
end application do the formation|||> Hassan would have found all that out if he read the article I pointed him to.
Ah, that ol' article ;-)
Thanks Kalen :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OkINH5MbIHA.1376@.TK2MSFTNGP02.phx.gbl...
> Hassan would have found all that out if he read the article I pointed him to.
> ;-)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uB0OLMIbIHA.1376@.TK2MSFTNGP02.phx.gbl...
>> Downsides of using varchar include:
>> There's nothing stopping you from inserting invalid date (like Feb 30), or time values.
>> Performing various datetime calculation might mean bad performance, since you might end up with a
>> convert on the column side in your predicate.
>> You need to decide on a format. If you want to retrieve the value and present it in a different
>> format from what it is stored with you have more work to do.
>> ...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Hassan" <hassan@.test.com> wrote in message news:evp1jaFbIHA.4180@.TK2MSFTNGP06.phx.gbl...
>> Kalen,
>> Does not seem to be any difference with that example.
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:ODKGXIFbIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> Hi Hassan
>> Why don't you try it and see? Something like this should give you a start:
>> declare @.today varchar(30)
>> select @.today = getdate()
>> select @.today
>> select dateadd(mm, 1, @.today)
>> And then read Tibor's excellent article:
>> http://www.karaszi.com/sqlserver/info_datetime.asp
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
>> If I insert datatime values in a varchar datatype as opposed to datetime, what am I losing out
>> on ?
>> Would I be able to perform the same functions against a varchar datatype as opposed to
>> datetime such as datepart, datediff,etc. ?
>>
>>
>>
>>
>

datetime functions...

Hi all,

I have a problem with date functions...

In my program I use weeks, and with this variable I need to know which is the first day of the selected week...

For example... I put week 52 in my program... how I can obtain the first day of this week? -> (22/12/2003).

I have proved with this functions...

SET DATEFIRST 1 (to configure Monday as first day of week)

SELECT DATEADD(ww,DATEDIFF(ww,0,GETDATE()),0)

With this I have the day of the current week... but I can't put my week in this function... aarrgggg.

Please help me...

Thanks a lot.I played around with this for a little bit, and this should get you what you are looking for:

ET DATEFIRST 7 -- set this back to the default of 7 -- shouldn't need to mess with this for this calculation
DECLARE @.firstDayOfCurrentWeek DateTime
DECLARE @.firstDayOfSelectedWeek DateTime
DECLARE @.WeeksToSubtract Integer
DECLARE @.myWeek Integer
SET @.myWeek = 52
SET @.firstDayOfCurrentWeek = DATEADD(ww,DATEDIFF(ww,0,GETDATE()),0)
SET @.WeeksToSubtract = @.myWeek - DATEPART(ww,@.firstDayOfCurrentWeek)
SET @.firstDayOfSelectedWeek = DATEADD(ww,@.WeeksToSubtract,@.firstDayOfCurrentWeek)
SELECT @.firstDayOfSelectedWeek

Terri

Datetime function glitch -- same settings, different results

hi guys,

i am encountering a pretty weird problem on our SQL database stored procs, particularly those concerning datetime functions. we have two setups, one is on london, and another one is here in singapore. both servers have the same regional settings, the same database tables, stored procs and functions, almost the same in every aspect. the only difference we know is that one is a direct database cut (sql 7.0), while the other used database migration in win2k.

here goes: all stored proc functions work just fine on the singapore setup. the london setup however, is not working at all! it does all the functions like change flags and status, but it doesn't update dates nor search for data by date. i have also checked on the collision names, and they are the same. where else could i start searching for discrepancies between the two?

hope to hear something from you guys, i'm getting desperate. thanks in advance!

What do you mean by "but it doesn't update dates nor search for data by date". The date representation is a session setting which can be different depending on the user which is using the SQL Server. YOu have to be aware that an application which was not prepared to handle that might encounter problem during data retrieval or manipulation.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Here's an example: I have a datetime column that contains the date when info on a particular row gets altered. Ideally, everytime the user changes something from the front end, this corresponding date info gets updated as well. The weird thing here is that my setup on the Singapore database works just fine, the date gets updated. However, the London setup doesn't do this. I have checked the language and other regional settings, and they are the same across these two databases. I also checked the user profiles, and they have the same settings as well.

My stored procs use the datetime variable, and so does the application that calls the stored procs.

What have I overlooked?

|||

Can you provide some example of the method used that's supposed to execute this date-update operation?

Exactly what is the 'error'? Is it that nothing happens, or is it that the expected rows aren't found?
There are many subtle differences between 'not working' and 'not working' =;o)

/Kenneth

|||I would suggest you use Profiler to trace the code that gets generated and then you can isolate whether it is the code generated or "user error"...

Check out my SQL Server 2005 Video Tutorials: http://www.learnsqlserver.com/