select max time in AM
in [Server Configuration]
|
From: aleem510 via SQLMonster.com on 7 Jul 2010 07:12 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
From: aleem510 via SQLMonster.com on 7 Jul 2010 07:19 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
From: Eric Isaacs on 7 Jul 2010 14:16 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
From: aleem510 via SQLMonster.com on 8 Jul 2010 00:50
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 |