Board index » delphi » Convert date.time to date for grouping

Convert date.time to date for grouping

I'm trying to group a datetime field by the date component only.

I used "select convert( DECIMAL, datefield)..... group by
convert(DECIMAL, datefield)"

The problem is that if the time as > 12:00 is gets grouped in with the
next day.
(ie.  01.june.2002 12:01 is group with 02.June.2002)

Has anyone run into this problem?  Is there another function I should be
using?
--
Ron Simonsmeier
Enhanced Business Systems Inc.
ro...@enhanced-business.com

 

Re:Convert date.time to date for grouping


Quote
"Ron Simonsmeier" <ro...@enhanced-business.com> wrote in message

news:3D61B85E.ACC7BE4C@enhanced-business.com...

Quote
> I'm trying to group a datetime field by the date component only.

Answered in "Re: Converting Decimal to Varchar" thread.

Re:Convert date.time to date for grouping


I finally figured out the following

select convert( char(10), DateField, 20) as MyDate
.....
group by convert( char(10), DateField, 20)

This grabs the first 10 character of the ODBC canonical format  (YYYY-MM-DD) and
sorts it properly also.

Thanks for your help

Quote
Juan Calfucura wrote:
> That works on Oracle only, Sql Server is a lot hard to workaround

> "Christopher Latta" <nob...@nowhere.not> wrote in message
> news:3d61c247@newsgroups.borland.com...
> > Try:
> > select Trunc(datefield).....
> > group by Trunc(datefield)

> > Christopher Latta

> > "Ron Simonsmeier" <ro...@enhanced-business.com> wrote in message
> > news:3D61B85E.ACC7BE4C@enhanced-business.com...
> > > I'm trying to group a datetime field by the date component only.

> > > I used "select convert( DECIMAL, datefield)..... group by
> > > convert(DECIMAL, datefield)"

> > > The problem is that if the time as > 12:00 is gets grouped in with the
> > > next day.
> > > (ie.  01.june.2002 12:01 is group with 02.June.2002)

> > > Has anyone run into this problem?  Is there another function I should be
> > > using?
> > > --
> > > Ron Simonsmeier
> > > Enhanced Business Systems Inc.
> > > ro...@enhanced-business.com

--
Ron Simonsmeier
Enhanced Business Systems Inc.
ro...@enhanced-business.com

Re:Convert date.time to date for grouping


You can use "select convert(smalldatetime, DateField) as MyDate". It will
only extract the Date.

"Ron Simonsmeier" <ro...@enhanced-business.com>
???????:3D64656C.6B34F...@enhanced-business.com...

Quote
> I finally figured out the following

> select convert( char(10), DateField, 20) as MyDate
> .....
> group by convert( char(10), DateField, 20)

> This grabs the first 10 character of the ODBC canonical format
(YYYY-MM-DD) and
> sorts it properly also.

> Thanks for your help

> Juan Calfucura wrote:

> > That works on Oracle only, Sql Server is a lot hard to workaround

> > "Christopher Latta" <nob...@nowhere.not> wrote in message
> > news:3d61c247@newsgroups.borland.com...
> > > Try:
> > > select Trunc(datefield).....
> > > group by Trunc(datefield)

> > > Christopher Latta

> > > "Ron Simonsmeier" <ro...@enhanced-business.com> wrote in message
> > > news:3D61B85E.ACC7BE4C@enhanced-business.com...
> > > > I'm trying to group a datetime field by the date component only.

> > > > I used "select convert( DECIMAL, datefield)..... group by
> > > > convert(DECIMAL, datefield)"

> > > > The problem is that if the time as > 12:00 is gets grouped in with
the
> > > > next day.
> > > > (ie.  01.june.2002 12:01 is group with 02.June.2002)

> > > > Has anyone run into this problem?  Is there another function I
should be
> > > > using?
> > > > --
> > > > Ron Simonsmeier
> > > > Enhanced Business Systems Inc.
> > > > ro...@enhanced-business.com

> --
> Ron Simonsmeier
> Enhanced Business Systems Inc.
> ro...@enhanced-business.com

Other Threads