Prev: MICROSOFT SQL-DMO ERROR 5172
Next: SSRS2008 connecting to Oracle
From: aleem510 on 6 Jul 2010 10:53
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...

From: Eric Isaacs on 6 Jul 2010 15:41
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.

From: Eric Isaacs on 6 Jul 2010 15:45
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

From: Eric Isaacs on 6 Jul 2010 15:51

> 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

From: aleem510 via SQLMonster.com on 7 Jul 2010 01:12
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

 |  Next  |  Last
Pages: 1 2
Prev: MICROSOFT SQL-DMO ERROR 5172
Next: SSRS2008 connecting to Oracle