View Message Thread (8 replies)
| select max time in AM |
 |
From: aleem510
Date Posted: 7/6/2010 8:55:00 AM
I have this simple table:
ID------DATE-------
1----2004-08-02 12:30:AM----
2----2004-08-03 4:30:AM----
3----2004-10-17 9:30:AM----
4----2004-08-02 1:30:PM----
5----2003-02-12 4:30:PM----
6----2004-02-03 12:30:PM----
7----2004-01-20 12:45:AM---
8----2004-07-04 8:30:PM----
I want max time of AM shift...
| Re: select max time in AM |
|
From: Eric Isaacs
Date Posted: 7/6/2010 1:43:00 PM
I take it that you only want the TIME returned, without the date.
Here's a useful function for parsing out the date portion of a
DATETIME field...
CREATE FUNCTION dbo.jfn_Utility_GetDateOnly
(
@InputDateTime DATETIME
)
RETURNS DATETIME
WITH SCHEMABINDING
AS
BEGIN --Function
RETURN @InputDateTime - CAST(CAST(@InputDateTime AS BINARY(4)) AS
DATETIME)
--other ways to do the same thing:
--RETURN DATEADD(DD, 0, DATEDIFF(DD, 0, @InputDateTime))
--RETURN CAST(FLOOR(CAST(@InputDateTime AS DECIMAL(12, 5))) AS
DATETIME)
END --Function
GO
--SELECT GETDATE() AS InputValue,
dbo.jfn_Utility_GetDateOnly(GETDATE()) AS ReturnValue
GO
Here's another function for parsing out the time portion of a DATETIME
field...
CREATE FUNCTION dbo.jfn_Utility_GetTimeOnly
(
@InputDateTime AS DATETIME
)
RETURNS DATETIME
WITH SCHEMABINDING
AS
BEGIN --Function
RETURN CAST(CAST(@InputDateTime AS BINARY(4)) AS DATETIME)
END --Function
-----------------------------
With the time function in place, you can do the following...
SELECT MAX(dbo.jfn_Utility_GetTimeOnly([DateTimeColumnName]) FROM
[TableName]
-Eric Isaacs
J Street Technology, Inc.
| Re: select max time in AM |
|
From: Eric Isaacs
Date Posted: 7/6/2010 1:48:00 PM
Oops...forgot about the AM part....
SELECT MAX(dbo.jfn_Utility_GetTimeOnly([AuditCreateTS])) FROM
[tblAudit] WHERE dbo.jfn_Utility_GetTimeOnly([AuditCreateTS]) <
'12:00:00'
-Eric Isaacs
| Re: select max time in AM |
|
From: Eric Isaacs
Date Posted: 7/6/2010 1:56:00 PM
> SELECT MAX(dbo.jfn_Utility_GetTimeOnly([AuditCreateTS])) FROM
> [tblAudit] WHERE dbo.jfn_Utility_GetTimeOnly([AuditCreateTS]) <
> '12:00:00'
Time values have in integer for the date and a decimal value for the
time. The select above returns the DATETIME value that contains zero
for the date and the max time. You may need to format that value the
way you want it presented using the CONVERT function.
SELECT CONVERT(VARCHAR(25),
MAX(dbo.jfn_Utility_GetTimeOnly([AuditCreateTS])), 8) FROM
[tblAudit] WHERE dbo.jfn_Utility_GetTimeOnly([AuditCreateTS]) <
'12:00:00'
-Eric Isaacs
| Re: select max time in AM |
|
From: aleem510 via SQLMonster.com
Date Posted: 7/6/2010 11:16:00 PM
Eric Isaacs wrote:
>> SELECT MAX(dbo.jfn_Utility_GetTimeOnly([AuditCreateTS])) FROM
>> [tblAudit] WHERE dbo.jfn_Utility_GetTimeOnly([AuditCreateTS]) <
>> '12:00:00'
>
>Time values have in integer for the date and a decimal value for the
>time. The select above returns the DATETIME value that contains zero
>for the date and the max time. You may need to format that value the
>way you want it presented using the CONVERT function.
>
>SELECT CONVERT(VARCHAR(25),
>MAX(dbo.jfn_Utility_GetTimeOnly([AuditCreateTS])), 8) FROM
>[tblAudit] WHERE dbo.jfn_Utility_GetTimeOnly([AuditCreateTS]) <
>'12:00:00'
>
>-Eric Isaacs
Thanks for the help buddy...
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201007/1
| Re: select max time in AM |
|
From: aleem510 via SQLMonster.com
Date Posted: 7/7/2010 5:16:00 AM
aleem510 wrote:
>>> SELECT MAX(dbo.jfn_Utility_GetTimeOnly([AuditCreateTS])) FROM
>>> [tblAudit] WHERE dbo.jfn_Utility_GetTimeOnly([AuditCreateTS]) <
>[quoted text clipped - 11 lines]
>>
>>-Eric Isaacs
>
>Thanks for the help buddy...
Its Not Displaying the exact date.....
its showing me '1900-00-00 04:00.000'
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201007/1
| Re: select max time in AM |
|
From: aleem510 via SQLMonster.com
Date Posted: 7/7/2010 5:22:00 AM
aleem510 wrote:
>>>> SELECT MAX(dbo.jfn_Utility_GetTimeOnly([AuditCreateTS])) FROM
>>>> [tblAudit] WHERE dbo.jfn_Utility_GetTimeOnly([AuditCreateTS]) <
>[quoted text clipped - 3 lines]
>>
>>Thanks for the help buddy...
>
>Its Not Displaying the exact date.....
>its showing me '1900-00-00 04:00.000'
my query is
Declare @Min_dt datetime
Declare @Max_dt datetime
SELECT @Min_dt = (SELECT CONVERT(VARCHAR(25),MAX(dbo.jfn_Utility_GetTimeOnly(
[dtPunchDate])), 8) FROM
[purpletalk].[dbo].[tabElectronicPunchesPT] WHERE dbo.jfn_Utility_GetTimeOnly
([dtPunchDate]) >
'12:00:00') , @Max_dt = (SELECT CONVERT(VARCHAR(25),MAX(dbo.
jfn_Utility_GetTimeOnly([dtPunchDate])), 8) FROM
[purpletalk].[dbo].[tabElectronicPunchesPT] WHERE dbo.jfn_Utility_GetTimeOnly
([dtPunchDate]) <
'12:00:00') from [tabElectronicPunchesPT]
WHERE
[varCardNo] = '72'
--and [intBranchId] = @varBranchId
print @Min_dt
print @Max_dt
intBranchId varCardNo dtPunchDate
1 72 7/5/2010 6:00:00 PM
1 72 7/6/2010 5:00:00 AM
1 72 7/6/2010 3:00:00 AM
1 72 7/6/2010 5:00:00 PM
i m getting result...
Jan 1 1900 6:00PM
Jan 1 1900 5:00AM
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201007/1
| Re: select max time in AM |
|
From: Eric Isaacs
Date Posted: 7/7/2010 12:22:00 PM
You said you wanted to know the max time in the AM. I'm still not
certain what you need. If you format that DATETIME to just time, as I
mentioned, it will display as just the time. If you need to know
which dates and times have the max time in the AM, that's a little
more difficult, but not much more.
Perhaps this is what you need?
DECLARE @Min_dt VARCHAR(25) --Note that these are now strings, not
DATETIME values.
DECLARE @Max_dt VARCHAR(25)
SELECT
@Min_dt = CONVERT(VARCHAR(25),
MIN(dbo.jfn_Utility_GetTimeOnly([dtPunchDate])), 8),
@Max_dt = CONVERT(VARCHAR(25),
MAX(dbo.jfn_Utility_GetTimeOnly([dtPunchDate])), 8)
FROM
[purpletalk].[dbo].[tabElectronicPunchesPT]
WHERE
[varCardNo] = '72'
AND dbo.jfn_Utility_GetTimeOnly([dtPunchDate]) < '12:00:00'
--AND [intBranchId] = @varBranchId
PRINT @Min_dt
PRINT @Max_dt
-Eric Isaacs
| Re: select max time in AM |
|
From: aleem510 via SQLMonster.com
Date Posted: 7/7/2010 10:53:00 PM
Eric Isaacs wrote:
>You said you wanted to know the max time in the AM. I'm still not
>certain what you need. If you format that DATETIME to just time, as I
>mentioned, it will display as just the time. If you need to know
>which dates and times have the max time in the AM, that's a little
>more difficult, but not much more.
>
>Perhaps this is what you need?
>
> DECLARE @Min_dt VARCHAR(25) --Note that these are now strings, not
>DATETIME values.
> DECLARE @Max_dt VARCHAR(25)
>
> SELECT
> @Min_dt = CONVERT(VARCHAR(25),
>MIN(dbo.jfn_Utility_GetTimeOnly([dtPunchDate])), 8),
> @Max_dt = CONVERT(VARCHAR(25),
>MAX(dbo.jfn_Utility_GetTimeOnly([dtPunchDate])), 8)
> FROM
> [purpletalk].[dbo].[tabElectronicPunchesPT]
> WHERE
> [varCardNo] = '72'
> AND dbo.jfn_Utility_GetTimeOnly([dtPunchDate]) < '12:00:00'
> --AND [intBranchId] = @varBranchId
>
> PRINT @Min_dt
> PRINT @Max_dt
>
>-Eric Isaacs
Again Thanks for your help buddy but it got the soluion....
@Min_dt = CONVERT(VARCHAR(25),
MIN(dbo.jfn_Utility_GetTimeOnly([dtPunchDate])), 20),--- changed size from 8
to 20
@Max_dt = CONVERT(VARCHAR(25),
MAX(dbo.jfn_Utility_GetTimeOnly([dtPunchDate])), 20)
--
Message posted via http://www.sqlmonster.com
Would you like to track this thread?
By adding this News Thread to your Favorites Area you can refer to it later with just a click of the mouse.
Also you can optionally be notified instantly whenever there are any replies
posted to this Thread. To add this Thread to your Favorites Area just click on the red arrow next to the subject of the thread above
 .
|
