July 22, 2009 at 9:49 am
I have a varchar field that stores the week of a year as 'year-week':
2008-1
2008-2
2008-51
2008-52
etc...
I'm trying to extract the first DateTime for each value; for example:
2008-1 = '2008-01-01'
2008-2 = '2008-01-06'
2008-3 = '2008-01-13'
I have legacy sales data that is grouped by week for each customer that I need to stash in an order processing system using a real datetime. The date doesn't necessarily have to be the first day of the week provided.
Not really sure where to start on this one. Is there something like DateName/DatePart that I could use in reverse?
TIA,
JB
July 22, 2009 at 10:01 am
I have used a date table in the past. This would be an auto-generated table that had dates with week numbers etc. in different columns. I have a spreadsheet that I downloaded from someplace long ago. Let me know if you would like to see that. Otherwise after a little searching I found this:
July 22, 2009 at 10:06 am
Date table, of course!
PM'd.
November 18, 2010 at 4:21 pm
In SQL Server 2008 R2 you could use a UDF like this:
=============================================
-- Author: Guus Endeveld
-- Create date: 18-11-2010
-- Description: Determine start datetime of a year and ISO weeknumber
-- Assumes SET DATEFIRST 1
-- Usage: SELECT dbo.IsoWeek2DateTime('201001')
-- =============================================
CREATE FUNCTION [dbo].[StartIsoWeek] (@yyyyww char(6))
RETURNS datetime
AS
BEGIN
DECLARE @StartDatetime datetime
DECLARE @yyyy char(4)= Left(@yyyyww,4)
DECLARE @ww tinyint= Right(@yyyyww,2)
SET @StartDatetime = Cast(@yyyy + '0104' as datetime) -
Datepart("DW", Cast(@yyyy + '0104' as datetime)) + 1 + (@ww - 1) * 7 -- begin ISO week
RETURN @StartDatetime
END
GO
or:
-- =============================================
-- Author: Guus Endeveld
-- Create date: 18-11-2010
-- Description: Determine end datetime of a year and ISO weeknumber
-- Assumes SET DATEFIRST 1
-- Usage: SELECT dbo.IsoWeek2DateTime('201001')
-- =============================================
CREATE FUNCTION [dbo].[EndIsoWeek] (@yyyyww char(6))
RETURNS datetime
AS
BEGIN
DECLARE @EndDatetime datetime
DECLARE @yyyy char(4)= Left(@yyyyww,4)
DECLARE @ww tinyint= Right(@yyyyww,2)
SET @EndDatetime = Cast(@yyyy + '0104' as datetime) -
Datepart("DW", Cast(@yyyy + '0104' as datetime)) + 1 + @ww * 7 - 0.00000004-- end ISO week
RETURN @EndDatetime
END
GO
November 18, 2010 at 4:24 pm
That's awesome, except that you posted it in the SQL Server 2005 section, regarding a question asked in July of 2009.
November 18, 2010 at 4:43 pm
Also, it breaks miserably if the week is less than 10 because it makes an assumption that the format is yyyy-ww while in reality it could be yyyy-w. This code looks like it could be used as a fine example of how not to do something.
Oleg
November 18, 2010 at 5:49 pm
On ASK side we have a badge named Necromancer, which is given to someone who dusts out a question older than 60 days, answers it and gets 5 upvotes. To date, we still have no contributors who earned the badge. There are no badges on SSC side, but at least I will try to play a necromancer here :hehe:
-- Begin test data generation
declare @t table (year_week varchar(7));
insert into @t values ('2008-1');
insert into @t values ('2008-2');
insert into @t values ('2008-3');
insert into @t values ('2008-51');
insert into @t values ('2008-52');
insert into @t values ('2008-53');
insert into @t values ('2010-1');
insert into @t values ('2010-2');
insert into @t values ('2010-3');
insert into @t values ('2010-51');
insert into @t values ('2010-52');
insert into @t values ('2010-53');
-- end test data generation
/*
The solution assumes EN-US settings, week begins on Sunday.
First of January is the earliest day of the year, so yyyy-1
should always reasult in yyyy-01-01, next week begins from
coming Sunday, which makes the first week shorter than
others most of the time (unless New Year comes on Sunday.
*/
;with dates(year_week, week_num, d) as
(
select
year_week, cast(substring(year_week, 6, 2) as int) week_num,
dateadd(week, cast(substring(year_week, 6, 2) as int) - 1,
dateadd(year, left(year_week, 4) - 1900, 0))
from @t
)
select
year_week,
case week_num
when 1 then d
else dateadd(day, 1 - datepart(weekday, d), d)
end WeekStart
from dates;
-- results:
year_week WeekStart
--------- -----------------------
2008-1 2008-01-01 00:00:00.000
2008-2 2008-01-06 00:00:00.000
2008-3 2008-01-13 00:00:00.000
2008-51 2008-12-14 00:00:00.000
2008-52 2008-12-21 00:00:00.000
2008-53 2008-12-28 00:00:00.000
2010-1 2010-01-01 00:00:00.000
2010-2 2010-01-03 00:00:00.000
2010-3 2010-01-10 00:00:00.000
2010-51 2010-12-12 00:00:00.000
2010-52 2010-12-19 00:00:00.000
2010-53 2010-12-26 00:00:00.000
Oleg
November 19, 2010 at 1:11 am
Oleg Netchaev (11/18/2010)
Also, it breaks miserably if the week is less than 10 because it makes an assumption that the format is yyyy-ww while in reality it could be yyyy-w. This code looks like it could be used as a fine example of how not to do something.Oleg
Oleg,
Thanks for your reply, but I think you could be a bit more positive in your reaction on my basic solution. Quite some programmers will be able to adjust this code to their needs, if for example the length of the input parameter is not the same, and use the substring() in stead of right() function.
We in Europe work mostly with ISO standards, so week 1 starts with the week with most of the days in it that year (4) and the week starts on Monday.
Although the reply is posted in de SQL Server 2005 section, I think that the algorithmes could be useful, by not using a table with dates which must be maintained.
Guus
November 19, 2010 at 11:03 am
guus.endeveld (11/19/2010)
Oleg,Thanks for your reply, but I think you could be a bit more positive in your reaction on my basic solution.
Guus
I am terribly sorry if my answer offended you. I should probably limit my comment to the simple facts and then elaborate on them. I will do this now. Here is what found in your solution:
It expects the input in the different format from the one requested in the original post.
Even if the input is adjusted to comply with the input expected by your UDF, the latter unfortunately does not return desired result. For example, feeding '200801' input in your UDF returns 30th of December 2007 while in reality it should return 1st of January 2008. The question was to return the datetime matching the Sunday of the week number in question, but return 1st of January for the first week. Considering few rows from the question sample, input 200801 should return January 1st (Tuesday), input 200802 should return January 6th (Sunday), input 200803 should return January 13th (Sunday) of that year etc.
Implementation of the scalar UDFs has a potential of carrying a performance hit when compared with inline implementation of the same. I don't have any numbers in front of me yet, but will try to compare yours and my solutions this weekend to see if there is a difference between your UDF and my inline manipulations.
Though this is a minor problem, your UDF uses a syntax specific to SQL Server 2008, so it simply does not work in 2005 and it is T-SQL 2005 forum as Wesley has already noted in his post (variables are declared and immediately set withing the same statement).
My main concern though, which actually prompted my ugly reply, was triggered when I saw manipulations like this in you code:
Datepart("DW", Cast(@yyyy + '0104' as datetime)) + 1 + @ww * 7 - 0.00000004
The last part is a big suspect to say the least considering how the datetime is stored internally. While not exactly related to this discussion, I remember once getting in the hot seat when I answered a simple question on this forum about converting datetime values into a suitable format. I knew that using convert is evil, and generally speaking, any formatting manipulations should be always outsourced to the presentation level, databases should have little or nothing to do with any formatting, but I still went for it. There was a simple question to which I typed a seemingly simple answer. I was criticized very heavily for this by Jeff Moden. While I was honoured that it was by Jeff, whom I, just like many others, hugely respect, I still felt pretty upset initially but then I realized that he is so right and I am so wrong. I am no Jeff Moden of course :hehe:, but I believe that I still can express my opinion if I see something I know is not right. Here are some musings about datetime internals I wrote in one of the answers on the ASK side:
Datetime internally is stored as a couple of ints (4 + 4 = 8 bytes in total). First int (4 bytes) is used to store the number of days from the zero date (1900-01-01). Second int (4 bytes) is used to store the number of ticks from midnight of the same day. This part ranges from 0 to 25919999 because there are 86400 seconds per day and every tick is about 3.33 milliseconds. Thus, the accuracy of the datetime is ~ 3.33 milliseconds. To enforce this, the rightmost digit of the milliseconds part of the datetime value can have only 3 possible values, namely 0, 3 and 7. In other words, if date1 is '2010-07-27 22:50:00.997' and the cave main attempt is made to add 1 millicecond to it to make it date1 equal to '2010-07-27 22:50:00.998', the attempt will fail, date1 is still equal to date1 is '2010-07-27 22:50:00.997'. The attempt to add 2 milliseconds to it will result it to be equal to '2010-07-27 22:50:01.000' though.
Here is the small script to show some details related to the datetime storage:
-- press Ctrl+T (results to text before executing)
set nocount on;
go
declare @d datetime; -- current date and time
declare @d_internal binary(8); -- how it is stored
declare @days_part binary(4); -- left 4 bytes of storage
declare @time_part binary(4); -- right 4 bytes of storage
declare @days_int int; -- number of days from zero date as int
declare @time_int int; -- number of ticks from midnight today
select
@d = current_timestamp, -- or use getDate(), same thing
@d_internal = cast(@d as varbinary(8)),
@days_part = substring(@d_internal, 1, 4),
@time_part = substring(@d_internal, 5, 4),
@days_int = cast(@days_part as int),
@time_int = cast(@time_part as int);
select
@d today, @d_internal stored_as;
select
@days_part left_part, @time_part right_part,
@days_int day_count, @time_int ticks_from_midnight;
print 'press Ctrl+D if you usually set results to grid';
set nocount off;
go
The above shows results similar to the ones displayed below:
today stored_as
----------------------- ------------------
2010-11-19 12:02:41.217 0x00009E3300C67DED
left_part right_part day_count ticks_from_midnight
---------- ---------- ----------- -------------------
0x00009E33 0x00C67DED 40499 13008365
While ill advised, both + and - operators are unfortunately legal. For example, if @date1 is today and @date2 is 2 days ago same time then @date1 - @date2 is equal to 3d of January 1900 at midnight. How? Here is explanation: @date1 is internally converted to number of days from January 1st 1900 until today (40385), @date2 is converted to 40383, the difference is 2 which is then converted back from int to datetime resulting in 1900-01-03
The datetime values should fit inside of the legal limit (from 1753-01-01 to 9999-12-31).
If one tries to cast string to datetime then cast will succeed only if the string is formatted correctly (or is spelled in 'YYYYMMDD' format which is independent of server settings). Additionally, because both int and varchar are convertible to datetime, it is essential not to forget the single quotes if the subject to cast is meant to be a string. cast(2010-07-28 as datetime) without quotes will be translated to 2010 minus 7 minus 28 = 1975, which is an int. When 1975 is attempted to be cast to datetime, SQL Server will simply add 1975 to the left int (number of days from zero date) resulting in unexpected cast result of May 30th, 1905:
select
cast(2010-07-28 as datetime) wrong,
cast('2010-07-28' as datetime) correct;
wrong correct
----------------------- -----------------------
1905-05-30 00:00:00.000 2010-07-28 00:00:00.000
To make this part of the long story short, dateadd(millisecond, -3, your_date) is probably better than to do something like your_date - 0.00000004 because when the engine will process the first, it will simply leave the left int untouched and subtract 1 tick from the right int representing the time part. To process your_date - 0.00000004 the engine will have to bunch BOTH ints into one bigint, implicitly convert it to the decimal of required precision and scale, subtract 0.00000004 from it, and then convert it back to the pair of integers.
Once againg, please accept my apologies if I offended you in my last post.
Oleg
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply