I'm looking for a way to turn a list of dates into a string of
abbreviations
10/9/2006
10/11/2006
Would Translate To
M-W
Any Ideas would be greatCheck into the use of date(). Perhaps with judicious truncation, you can get
what you desire.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>|||Check into the use of date(). Perhaps with judicious truncation, you can get
what you desire.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>|||Darn spellcheck.
Check into the use of datename().
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uJWcpaD7GHA.4500@.TK2MSFTNGP02.phx.gbl...
> Check into the use of date(). Perhaps with judicious truncation, you can
> get what you desire.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <Nate.Strack@.gmail.com> wrote in message
> news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
>|||Hi
DECLARE @.dt DATETIME
SET @.dt='20061010'
SELECT LEFT(DATENAME(weekday,@.dt),1)
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>|||This is clost to what i need idealy though it should be able to take
id, programdate
1, 1/1/2007
1,1/3/2007
1,1/6/2007
using syntax like
select Id, DateFunction(programdate)
from table1
group by id
and would return
1, '-M-W--S'
Uri Dimant wrote:[vbcol=seagreen]
> Hi
> DECLARE @.dt DATETIME
> SET @.dt='20061010'
> SELECT LEFT(DATENAME(weekday,@.dt),1)
>
>
> <Nate.Strack@.gmail.com> wrote in message
> news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...|||On 10 Oct 2006 18:08:31 -0700, Nate.Strack@.gmail.com wrote:
>This is clost to what i need idealy though it should be able to take
>id, programdate
>1, 1/1/2007
>1,1/3/2007
>1,1/6/2007
>using syntax like
>select Id, DateFunction(programdate)
>from table1
>group by id
>and would return
>1, '-M-W--S'
Hi Nate,
Try:
SELECT id,
MAX(CASE WHEN DATENAME(weekday, programdate) = 'Sunday' THEN
'S' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Monday' THEN
'M' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Tuesday' THEN
'T' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Wednesday' THEN
'W' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Thursday' THEN
'T' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Friday' THEN
'F' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Saturday' THEN
'S' ELSE '-' END)
FROM Test
GROUP BY id;
Hugo Kornelis, SQL Server MVP
Showing posts with label aggregate. Show all posts
Showing posts with label aggregate. Show all posts
Sunday, March 25, 2012
Day Of The Week Aggregate
I'm looking for a way to turn a list of dates into a string of
abbreviations
10/9/2006
10/11/2006
Would Translate To
M-W
Any Ideas would be great
Check into the use of date(). Perhaps with judicious truncation, you can get
what you desire.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegr oups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>
|||Check into the use of date(). Perhaps with judicious truncation, you can get
what you desire.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegr oups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>
|||Darn spellcheck.
Check into the use of datename().
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uJWcpaD7GHA.4500@.TK2MSFTNGP02.phx.gbl...
> Check into the use of date(). Perhaps with judicious truncation, you can
> get what you desire.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <Nate.Strack@.gmail.com> wrote in message
> news:1160461194.481594.286540@.i42g2000cwa.googlegr oups.com...
>
|||Hi
DECLARE @.dt DATETIME
SET @.dt='20061010'
SELECT LEFT(DATENAME(weekday,@.dt),1)
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegr oups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>
|||This is clost to what i need idealy though it should be able to take
id, programdate
1, 1/1/2007
1,1/3/2007
1,1/6/2007
using syntax like
select Id, DateFunction(programdate)
from table1
group by id
and would return
1, '-M-W--S'
Uri Dimant wrote:[vbcol=seagreen]
> Hi
> DECLARE @.dt DATETIME
> SET @.dt='20061010'
> SELECT LEFT(DATENAME(weekday,@.dt),1)
>
>
> <Nate.Strack@.gmail.com> wrote in message
> news:1160461194.481594.286540@.i42g2000cwa.googlegr oups.com...
|||On 10 Oct 2006 18:08:31 -0700, Nate.Strack@.gmail.com wrote:
>This is clost to what i need idealy though it should be able to take
>id, programdate
>1, 1/1/2007
>1,1/3/2007
>1,1/6/2007
>using syntax like
>select Id, DateFunction(programdate)
>from table1
>group by id
>and would return
>1, '-M-W--S'
Hi Nate,
Try:
SELECT id,
MAX(CASE WHEN DATENAME(weekday, programdate) = 'Sunday' THEN
'S' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Monday' THEN
'M' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Tuesday' THEN
'T' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Wednesday' THEN
'W' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Thursday' THEN
'T' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Friday' THEN
'F' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Saturday' THEN
'S' ELSE '-' END)
FROM Test
GROUP BY id;
Hugo Kornelis, SQL Server MVP
abbreviations
10/9/2006
10/11/2006
Would Translate To
M-W
Any Ideas would be great
Check into the use of date(). Perhaps with judicious truncation, you can get
what you desire.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegr oups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>
|||Check into the use of date(). Perhaps with judicious truncation, you can get
what you desire.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegr oups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>
|||Darn spellcheck.
Check into the use of datename().
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uJWcpaD7GHA.4500@.TK2MSFTNGP02.phx.gbl...
> Check into the use of date(). Perhaps with judicious truncation, you can
> get what you desire.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <Nate.Strack@.gmail.com> wrote in message
> news:1160461194.481594.286540@.i42g2000cwa.googlegr oups.com...
>
|||Hi
DECLARE @.dt DATETIME
SET @.dt='20061010'
SELECT LEFT(DATENAME(weekday,@.dt),1)
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegr oups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>
|||This is clost to what i need idealy though it should be able to take
id, programdate
1, 1/1/2007
1,1/3/2007
1,1/6/2007
using syntax like
select Id, DateFunction(programdate)
from table1
group by id
and would return
1, '-M-W--S'
Uri Dimant wrote:[vbcol=seagreen]
> Hi
> DECLARE @.dt DATETIME
> SET @.dt='20061010'
> SELECT LEFT(DATENAME(weekday,@.dt),1)
>
>
> <Nate.Strack@.gmail.com> wrote in message
> news:1160461194.481594.286540@.i42g2000cwa.googlegr oups.com...
|||On 10 Oct 2006 18:08:31 -0700, Nate.Strack@.gmail.com wrote:
>This is clost to what i need idealy though it should be able to take
>id, programdate
>1, 1/1/2007
>1,1/3/2007
>1,1/6/2007
>using syntax like
>select Id, DateFunction(programdate)
>from table1
>group by id
>and would return
>1, '-M-W--S'
Hi Nate,
Try:
SELECT id,
MAX(CASE WHEN DATENAME(weekday, programdate) = 'Sunday' THEN
'S' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Monday' THEN
'M' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Tuesday' THEN
'T' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Wednesday' THEN
'W' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Thursday' THEN
'T' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Friday' THEN
'F' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Saturday' THEN
'S' ELSE '-' END)
FROM Test
GROUP BY id;
Hugo Kornelis, SQL Server MVP
Day Of The Week Aggregate
I'm looking for a way to turn a list of dates into a string of
abbreviations
10/9/2006
10/11/2006
Would Translate To
M-W
Any Ideas would be greatCheck into the use of date(). Perhaps with judicious truncation, you can get
what you desire.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>|||Check into the use of date(). Perhaps with judicious truncation, you can get
what you desire.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>|||Darn spellcheck.
Check into the use of datename().
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uJWcpaD7GHA.4500@.TK2MSFTNGP02.phx.gbl...
> Check into the use of date(). Perhaps with judicious truncation, you can
> get what you desire.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <Nate.Strack@.gmail.com> wrote in message
> news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
>> I'm looking for a way to turn a list of dates into a string of
>> abbreviations
>> 10/9/2006
>> 10/11/2006
>> Would Translate To
>> M-W
>> Any Ideas would be great
>|||Hi
DECLARE @.dt DATETIME
SET @.dt='20061010'
SELECT LEFT(DATENAME(weekday,@.dt),1)
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>|||This is clost to what i need idealy though it should be able to take
id, programdate
1, 1/1/2007
1,1/3/2007
1,1/6/2007
using syntax like
select Id, DateFunction(programdate)
from table1
group by id
and would return
1, '-M-W--S'
Uri Dimant wrote:
> Hi
> DECLARE @.dt DATETIME
> SET @.dt='20061010'
> SELECT LEFT(DATENAME(weekday,@.dt),1)
>
>
> <Nate.Strack@.gmail.com> wrote in message
> news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
> > I'm looking for a way to turn a list of dates into a string of
> > abbreviations
> >
> > 10/9/2006
> > 10/11/2006
> > Would Translate To
> > M-W
> > Any Ideas would be great
> >|||On 10 Oct 2006 18:08:31 -0700, Nate.Strack@.gmail.com wrote:
>This is clost to what i need idealy though it should be able to take
>id, programdate
>1, 1/1/2007
>1,1/3/2007
>1,1/6/2007
>using syntax like
>select Id, DateFunction(programdate)
>from table1
>group by id
>and would return
>1, '-M-W--S'
Hi Nate,
Try:
SELECT id,
MAX(CASE WHEN DATENAME(weekday, programdate) = 'Sunday' THEN
'S' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Monday' THEN
'M' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Tuesday' THEN
'T' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Wednesday' THEN
'W' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Thursday' THEN
'T' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Friday' THEN
'F' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Saturday' THEN
'S' ELSE '-' END)
FROM Test
GROUP BY id;
--
Hugo Kornelis, SQL Server MVP
abbreviations
10/9/2006
10/11/2006
Would Translate To
M-W
Any Ideas would be greatCheck into the use of date(). Perhaps with judicious truncation, you can get
what you desire.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>|||Check into the use of date(). Perhaps with judicious truncation, you can get
what you desire.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>|||Darn spellcheck.
Check into the use of datename().
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uJWcpaD7GHA.4500@.TK2MSFTNGP02.phx.gbl...
> Check into the use of date(). Perhaps with judicious truncation, you can
> get what you desire.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <Nate.Strack@.gmail.com> wrote in message
> news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
>> I'm looking for a way to turn a list of dates into a string of
>> abbreviations
>> 10/9/2006
>> 10/11/2006
>> Would Translate To
>> M-W
>> Any Ideas would be great
>|||Hi
DECLARE @.dt DATETIME
SET @.dt='20061010'
SELECT LEFT(DATENAME(weekday,@.dt),1)
<Nate.Strack@.gmail.com> wrote in message
news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
> I'm looking for a way to turn a list of dates into a string of
> abbreviations
> 10/9/2006
> 10/11/2006
> Would Translate To
> M-W
> Any Ideas would be great
>|||This is clost to what i need idealy though it should be able to take
id, programdate
1, 1/1/2007
1,1/3/2007
1,1/6/2007
using syntax like
select Id, DateFunction(programdate)
from table1
group by id
and would return
1, '-M-W--S'
Uri Dimant wrote:
> Hi
> DECLARE @.dt DATETIME
> SET @.dt='20061010'
> SELECT LEFT(DATENAME(weekday,@.dt),1)
>
>
> <Nate.Strack@.gmail.com> wrote in message
> news:1160461194.481594.286540@.i42g2000cwa.googlegroups.com...
> > I'm looking for a way to turn a list of dates into a string of
> > abbreviations
> >
> > 10/9/2006
> > 10/11/2006
> > Would Translate To
> > M-W
> > Any Ideas would be great
> >|||On 10 Oct 2006 18:08:31 -0700, Nate.Strack@.gmail.com wrote:
>This is clost to what i need idealy though it should be able to take
>id, programdate
>1, 1/1/2007
>1,1/3/2007
>1,1/6/2007
>using syntax like
>select Id, DateFunction(programdate)
>from table1
>group by id
>and would return
>1, '-M-W--S'
Hi Nate,
Try:
SELECT id,
MAX(CASE WHEN DATENAME(weekday, programdate) = 'Sunday' THEN
'S' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Monday' THEN
'M' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Tuesday' THEN
'T' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Wednesday' THEN
'W' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Thursday' THEN
'T' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Friday' THEN
'F' ELSE '-' END)
+ MAX(CASE WHEN DATENAME(weekday, programdate) = 'Saturday' THEN
'S' ELSE '-' END)
FROM Test
GROUP BY id;
--
Hugo Kornelis, SQL Server MVP
Friday, February 24, 2012
datepart problem with week extraction (T-SQL)
I'm using datepart combined with a count aggregate to count the number of
w
s in a certain time period. Problem is, my employer starts each w on
Saturday. The T-SQL version of Datepart does not support a StartOfW
parameter. This defect is screwing up my reports.
Does anyone have a workaround?
Thanks,
Randall ArnoldLook up SET DATEFIRST in BOL to control the first day of the w for date
functions
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:e$odFORVGHA.2704@.tk2msftngp13.phx.gbl...
> I'm using datepart combined with a count aggregate to count the number of
> ws in a certain time period. Problem is, my employer starts each w
> on Saturday. The T-SQL version of Datepart does not support a StartOfW
> parameter. This defect is screwing up my reports.
> Does anyone have a workaround?
> Thanks,
> Randall Arnold
>|||Another alternative is to use a calendar table, whcih gives you the
flexibility of using multiple calendars associated with the same date.
Stu|||But be aware that SQL Server doesn't calculate w number the way that the
majority of the world
does.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dave Frommer" <anti@.spam.com> wrote in message news:uG7g6vSVGHA.736@.TK2MSFTNGP12.phx.gbl..
.
> Look up SET DATEFIRST in BOL to control the first day of the w for date
functions
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:e$odFORVGHA.2704@.tk2msftngp13.phx.gbl...
>|||Apparently not.
First, I tried setting Datefirst in a view, and got a syntax error. Using
the exact same SQL in a stored procedure didn't result in an error, but it
had no effect, either. No matter what value I set Datefirst to, the w is
still calculated wrong. For my purposes, 4/2/2005 needs to show as w 15
(first day of w = Saturday), but it always shows as w 14.
I don't see how a date lookup table will solve this... so, any other ideas?
This shortcoming in T-SQL is producing invalid results in my datasets...
Randall Arnold
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%235j4iOWVGHA.5012@.TK2MSFTNGP10.phx.gbl...
> But be aware that SQL Server doesn't calculate w number the way that
> the majority of the world does.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dave Frommer" <anti@.spam.com> wrote in message
> news:uG7g6vSVGHA.736@.TK2MSFTNGP12.phx.gbl...
>|||> I don't see how a date lookup table will solve this...
You have a table with one row per day. One of the columns in this table is t
he w correct number.
Or, read in Books Online about the ISOW function, install and use that in
stead of DATEPART().
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:OoTFjvyVGHA.5592@.TK2MSFTNGP09.phx.gbl...
> Apparently not.
> First, I tried setting Datefirst in a view, and got a syntax error. Using
the exact same SQL in a
> stored procedure didn't result in an error, but it had no effect, either.
No matter what value I
> set Datefirst to, the w is still calculated wrong. For my purposes, 4/
2/2005 needs to show as
> w 15 (first day of w = Saturday), but it always shows as w 14.
> I don't see how a date lookup table will solve this... so, any other ideas
? This shortcoming in
> T-SQL is producing invalid results in my datasets...
> Randall Arnold
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%235j4iOWVGHA.5012@.TK2MSFTNGP10.phx.gbl...
>|||Ok, thanks Tibor. I should have realized how the date (w) lookup would
work (I've done a similar thing for Periods), my bad. I'm just already
linking to so many tables on this query it's become a nightmare (due to bad
database design by the original dba). I'll get familiar with with ISOWEEK
and if that doesn't work, I have a Period lookup table already and I'll just
add a w column to it.
Randall Arnold
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6BX%230yVGHA.4960@.TK2MSFTNGP12.phx.gbl...
> You have a table with one row per day. One of the columns in this table is
> the w correct number.
> Or, read in Books Online about the ISOW function, install and use that
> instead of DATEPART().
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:OoTFjvyVGHA.5592@.TK2MSFTNGP09.phx.gbl...
>|||I decided just to add the W column to my existing Period table. It meant
changing several queries, as well as more manual maintenance, but it works.
Thanks again.
Randall Arnold
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6BX%230yVGHA.4960@.TK2MSFTNGP12.phx.gbl...
> You have a table with one row per day. One of the columns in this table is
> the w correct number.
> Or, read in Books Online about the ISOW function, install and use that
> instead of DATEPART().
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:OoTFjvyVGHA.5592@.TK2MSFTNGP09.phx.gbl...
>
w
s in a certain time period. Problem is, my employer starts each w onSaturday. The T-SQL version of Datepart does not support a StartOfW
parameter. This defect is screwing up my reports.
Does anyone have a workaround?
Thanks,
Randall ArnoldLook up SET DATEFIRST in BOL to control the first day of the w
for datefunctions
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:e$odFORVGHA.2704@.tk2msftngp13.phx.gbl...
> I'm using datepart combined with a count aggregate to count the number of
> w
s in a certain time period. Problem is, my employer starts each w> on Saturday. The T-SQL version of Datepart does not support a StartOfW
> parameter. This defect is screwing up my reports.
> Does anyone have a workaround?
> Thanks,
> Randall Arnold
>|||Another alternative is to use a calendar table, whcih gives you the
flexibility of using multiple calendars associated with the same date.
Stu|||But be aware that SQL Server doesn't calculate w
number the way that the majority of the world
does.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dave Frommer" <anti@.spam.com> wrote in message news:uG7g6vSVGHA.736@.TK2MSFTNGP12.phx.gbl..
.
> Look up SET DATEFIRST in BOL to control the first day of the w
for datefunctions
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:e$odFORVGHA.2704@.tk2msftngp13.phx.gbl...
>|||Apparently not.
First, I tried setting Datefirst in a view, and got a syntax error. Using
the exact same SQL in a stored procedure didn't result in an error, but it
had no effect, either. No matter what value I set Datefirst to, the w
isstill calculated wrong. For my purposes, 4/2/2005 needs to show as w
15(first day of w
= Saturday), but it always shows as w 14.I don't see how a date lookup table will solve this... so, any other ideas?
This shortcoming in T-SQL is producing invalid results in my datasets...
Randall Arnold
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%235j4iOWVGHA.5012@.TK2MSFTNGP10.phx.gbl...
> But be aware that SQL Server doesn't calculate w
number the way that> the majority of the world does.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dave Frommer" <anti@.spam.com> wrote in message
> news:uG7g6vSVGHA.736@.TK2MSFTNGP12.phx.gbl...
>|||> I don't see how a date lookup table will solve this...
You have a table with one row per day. One of the columns in this table is t
he w
correct number.Or, read in Books Online about the ISOW
function, install and use that instead of DATEPART().
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
news:OoTFjvyVGHA.5592@.TK2MSFTNGP09.phx.gbl...
> Apparently not.
> First, I tried setting Datefirst in a view, and got a syntax error. Using
the exact same SQL in a
> stored procedure didn't result in an error, but it had no effect, either.
No matter what value I
> set Datefirst to, the w
is still calculated wrong. For my purposes, 4/2/2005 needs to show as
> w
15 (first day of w = Saturday), but it always shows as w 14.> I don't see how a date lookup table will solve this... so, any other ideas
? This shortcoming in
> T-SQL is producing invalid results in my datasets...
> Randall Arnold
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%235j4iOWVGHA.5012@.TK2MSFTNGP10.phx.gbl...
>|||Ok, thanks Tibor. I should have realized how the date (w
) lookup wouldwork (I've done a similar thing for Periods), my bad. I'm just already
linking to so many tables on this query it's become a nightmare (due to bad
database design by the original dba). I'll get familiar with with ISOWEEK
and if that doesn't work, I have a Period lookup table already and I'll just
add a w
column to it.Randall Arnold
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6BX%230yVGHA.4960@.TK2MSFTNGP12.phx.gbl...
> You have a table with one row per day. One of the columns in this table is
> the w
correct number.> Or, read in Books Online about the ISOW
function, install and use that> instead of DATEPART().
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:OoTFjvyVGHA.5592@.TK2MSFTNGP09.phx.gbl...
>|||I decided just to add the W
column to my existing Period table. It meantchanging several queries, as well as more manual maintenance, but it works.
Thanks again.
Randall Arnold
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6BX%230yVGHA.4960@.TK2MSFTNGP12.phx.gbl...
> You have a table with one row per day. One of the columns in this table is
> the w
correct number.> Or, read in Books Online about the ISOW
function, install and use that> instead of DATEPART().
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Randall Arnold" <randall.nospam.arnold@.nokia.com.> wrote in message
> news:OoTFjvyVGHA.5592@.TK2MSFTNGP09.phx.gbl...
>
Subscribe to:
Posts (Atom)