Convert date.time to date for grouping

Ron Simonsmeier

Delphi Developer

Sat, 05 Feb 2005 11:32:46 GMT

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

Andy Macki

Delphi Developer

Sat, 05 Feb 2005 16:09:45 GMT

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.

Ron Simonsmeie

Delphi Developer

Mon, 07 Feb 2005 12:15:40 GMT

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

timoth

Delphi Developer

Mon, 14 Feb 2005 09:03:32 GMT

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

1. SQL 2000 - converting date/time to date in aggregate query

2. Getting Date/Time from LAN Machine and Setting Date/Time on Client

3. Query for last date/time and date/time in ASC format

4. Date Time Field, accessing Date xor Time only?

5. Converting julian date to normal date

6. Converting english date to french date format

7. Date only or Date and Time ?

8. Setting the initial date in Date time picker

9. date time to date conversion

10. converting packed 16Bit DOS time/date