SQL Server - Min date time and max date time - Asked By farrukh on 10-May-12 07:36 AM

Hello All ,

How to retrieve data on date time basis  i need to get the max time and mintime sql query?


2012-05-09 04:00:00.000
2012-05-09 10:00:00.000



THanks
farukh
kalpana aparnathi replied to farrukh on 10-May-12 07:44 AM
hi,

Using max() and min() function

SELECT DATEDIFF(minute, MIN(time), MAX(time)) FROM [table]

Regards,
Chintan Vaghela replied to farrukh on 10-May-12 08:38 AM

Hello,

Pass your date and take time as following way

select

    CONVERT(Varchar(20),MIN(DateField),114) as MinimumTime,

    CONVERT(Varchar(20),MAX(DateField),114) as MaximumTime

   

from TableName where CONVERT(Varchar(10),DateField,102)='2012.05.09'

 

 

 

Hope this helpful!

Thanks

 

 

 

farrukh replied to kalpana aparnathi on 10-May-12 08:38 AM

Hi Kalpana,


 I am retrieving record from your select statement showing combined minutes . i need that this whole below format comes in the query separately i want to pick first  min time record then in next query with maximum,

2012-05-09 04:00:00.000
2012-05-09 10:00:00.000
farrukh replied to Chintan Vaghela on 10-May-12 09:01 AM
R B,

GREAT IT WORKS CAN YOU PLEASE CHANGE THE DATE FORMAT

CONVERT(Varchar(10),DateField,102)='2012.05.09'

TO   '5/9/2012' 'M/D/YYYY'


THANKS
FARRUKH
Chintan Vaghela replied to farrukh on 10-May-12 09:09 AM

Hello,

Compare as following way (First Cast date into DateTime)

CONVERT(Varchar(10),DateField,101)=  CONVERT(Varchar(10),CAST('5/9/2012' as DateTime),101)

 

 

Hope this helpful!

Thanks

 

 

 

farrukh replied to Chintan Vaghela on 10-May-12 01:45 PM

RB ,

THIS IS MY BELOW QUERY THE DATE IS WORKS FINE BUT WHEN I SAY SUM OF MIN TO PARM1 AND PARM2 IT GETS THE VALUES FROM OTHER DATE TIME VALUE. CAN YOU PLEASE GUIDE ME I AM NEW.


  SELECT CONVERT(Varchar(20),MIN(START_DATETIME),114) as MinimumTime,
   (PARM1),(PARM2)
   FROM TABLE1    
   WHERE NAME='CPF'
    AND CONVERT(Varchar(10),START_DATETIME,101)>=  CONVERT(Varchar(10),CAST('5/9/2012' as DateTime),101)
    AND CONVERT(Varchar(10),START_DATETIME,101)<=  CONVERT(Varchar(10),CAST('5/9/2012' as DateTime),101)
  GROUP BY CONVERT(Varchar(20),MIN(START_DATETIME),114)


THANK YOU
Chintan Vaghela replied to farrukh on 11-May-12 01:07 AM

Hello,

 

Make inner query as following way to get SUM of two field based on Minimum Date

 

  SELECT CONVERT(Varchar(20),MIN(START_DATETIME),114) as MinimumTime,

 

  (SELECT PARM1 + PARM2 from TABLE1 as intbl where CONVERT(Varchar(20),intbl.START_DATETIME,114) = CONVERT(Varchar(20),MIN(outtbl.START_DATETIME),114))

 

   FROM TABLE1    as outtbl

   WHERE

    NAME='CPF' AND

    CONVERT(Varchar(10),START_DATETIME,102) between  CONVERT(Varchar(10),CAST('5/8/2012' as DateTime),102)

    and CONVERT(Varchar(10),CAST('5/8/2012' as DateTime),102)

 

 

Hope this helpful!

Thanks

 

 

 

farrukh replied to Chintan Vaghela on 11-May-12 08:44 AM
RB Wonderful thank you for your kind support wish you best of luck

Many thanks


farrukh
Chintan Vaghela replied to farrukh on 11-May-12 08:52 AM
welcome :)