Time And Only Time

20 Jul 2006 11:04 PM

Jim

In SQL Server 2005, is there a way to retrieve only the time portion of
a date/time field and ignore the date part?

I have a DATETIME field and I do not care what the specific date is, I
only want query results if time > 00:00:00 and <= 00:30:00 for any
Sunday.   Like this:

SELECT 1, COUNT(SYS_ENTRY_TS)
FROM dbo.PCM_Apps_Received_Raw
WHERE SYS_ENTRY_TS >= @START_TIME_1 AND
      SYS_ENTRY_TS < @END_TIME_1 AND
      DAY_OF_WEEK = 1            /* SUNDAY */

I need it to ignore the date because if I have one months worth of
data, I want the query to return information for each Sunday, of which
there will be four - not just one specific Sunday.

In the example above, SYS_ENTRY_TS and @START_TIME_1 are DATETIME

Any help appreciated.

20 Jul 2006 11:16 PM

Stopher

Just a novice but the convert command may help you.

convert(varchar, convert(varchar,time(table.field))

Don't know if this will work but it works for year month and day.

21 Jul 2006 12:30 AM

Arnie Rowland

Something like this ...

SELECT
     1
   , count( SYS_ENTRY_TS )
FROM dbo.PCM_Apps_Received_Raw
WHERE (   SYS_ENTRY_TS >= convert( varchar(10), @START_TIME_1, 108 )
      AND SYS_ENTRY_TS < convert( varchar(10), @END_TIME_1, 108 )
      AND DAY_OF_WEEK = 1
      )

Style 108 is 24 hour time.

--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quoteHide quote

"Jim" <Jim.Muek***@wellsfargo.com> wrote in message news:1153436668.929986.83280@s13g2000cwa.googlegroups.com...
>
>
> In SQL Server 2005, is there a way to retrieve only the time portion of
> a date/time field and ignore the date part?
>
> I have a DATETIME field and I do not care what the specific date is, I
> only want query results if time > 00:00:00 and <= 00:30:00 for any
> Sunday.   Like this:
>
> SELECT 1, COUNT(SYS_ENTRY_TS)
> FROM dbo.PCM_Apps_Received_Raw
> WHERE SYS_ENTRY_TS >= @START_TIME_1 AND
>      SYS_ENTRY_TS < @END_TIME_1 AND
>      DAY_OF_WEEK = 1            /* SUNDAY */
>
> I need it to ignore the date because if I have one months worth of
> data, I want the query to return information for each Sunday, of which
> there will be four - not just one specific Sunday.
>
> In the example above, SYS_ENTRY_TS and @START_TIME_1 are DATETIME
>
> Any help appreciated.
>

21 Jul 2006 12:37 PM

Stu

Try

SELECT 1 , count( SYS_ENTRY_TS )
FROM dbo.PCM_Apps_Received_Raw
WHERE (SYS_ENTRY_TS - DATEADD(d, 0, DATEDIFF(d, 0,
SYS_ENTRY_TS))>=@START_TIME_1
      AND SYS_ENTRY_TS - DATEADD(d, 0, DATEDIFF(d, 0, SYS_ENTRY_TS)) <
@END_TIME_1
      AND DAY_OF_WEEK = 1
      )

Assuming that you are entering Start_time and End_Time as either '8:00'
OR '19000101 8:00'.

Stu

Jim wrote:
Show quoteHide quote

> In SQL Server 2005, is there a way to retrieve only the time portion of
> a date/time field and ignore the date part?
>
> I have a DATETIME field and I do not care what the specific date is, I
> only want query results if time > 00:00:00 and <= 00:30:00 for any
> Sunday.   Like this:
>
> SELECT 1, COUNT(SYS_ENTRY_TS)
> FROM dbo.PCM_Apps_Received_Raw
> WHERE SYS_ENTRY_TS >= @START_TIME_1 AND
>       SYS_ENTRY_TS < @END_TIME_1 AND
>       DAY_OF_WEEK = 1            /* SUNDAY */
>
> I need it to ignore the date because if I have one months worth of
> data, I want the query to return information for each Sunday, of which
> there will be four - not just one specific Sunday.
>
> In the example above, SYS_ENTRY_TS and @START_TIME_1 are DATETIME
>
> Any help appreciated.

21 Jul 2006 1:15 PM

jsfromynr

Hi There,

You may like to try this one.

Select * From
(
Select 1 r,'2006-07-23 00:00:00.750' d
Union All
Select 2,'2006-07-23 00:01:00.750' d
Union All
Select 3,'2006-07-23 00:02:00.750' d
Union All
Select 4,'2006-07-23 00:03:00.750' d
Union All
Select 5,'2006-07-23 01:03:00.750' d
Union All
Select 6,'2006-07-23 00:05:00.750' d
)X Where datepart(dw,d)=1 and datepart(hh,d)=0
and datepart(mi,d) between 0 and 30

It would be slow

With Warm regards
Jatinder Singh
http://sqloracle.tripod.com

Thread options