Showing posts with label turn. Show all posts
Showing posts with label turn. 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 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
Posted by inh at 10:09 PM 0 comments
Labels: , , , , , , , , , , , , , ,

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
Posted by inh at 10:09 PM 0 comments
Labels: , , , , , , , , , , , , , ,

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
Posted by inh at 10:08 PM 0 comments
Labels: , , , , , , , , , , , , ,

Wednesday, March 7, 2012

datetime compare - eastern and pacific

Hi,
I need to compare two datetime values, one is eastern and anther is pacific. Is there an existing function that I can turn a pacific time to eastern one? or I have to write it by myself?
Thanks,
Liliyou might try using the dateadd function to manipulate the time by +2 hours.|||It works! Thank you.

In case someone wanna know, I used:
SET @.NEWSTART = DATEADD(HH, 3, OLDSTARTTIME)
Posted by inh at 3:23 AM 0 comments
Labels: , , , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)