Link to home
Start Free TrialLog in
Avatar of RIAS
RIASFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Sort by on two colums

Hello,
I have to columns in a table
Date : Data type is Datetime
Time: Data type is Datetime.

need to sort it by Date and time in asc manner

Cheers
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Just provide both columns in the ORDER BY. By default ORDER BY is ASC but you can explicitly say it:
SELECT *
FROM MyTable
ORDER BY Date ASC, Time ASC

Open in new window

Avatar of RIAS

ASKER

Hello Vitor,
I tried that  and it didn't work.
Please find my Sp below:
ALTER PROCEDURE  [dbo].[MCAL_Select_Diary]
  

      @RequestedBy Nvarchar(255)=null,  @AuthBy Nvarchar(50)=null,  @Date1 VARCHAR(50) =null, @Date2 VARCHAR(50) =null,@Time VARCHAR(20) = NULL ,@PickUpTime VARCHAR(20) =				  NULL ,@PatientName Nvarchar(MAX)=null,
	  @Ref Nvarchar(50)=null ,@Auth Nvarchar(50)=null,@PickUpAddress Nvarchar(50)=null,@Address Nvarchar(50)=null ,@Comments Nvarchar(MAX)=null,
	  @Interpreter Nvarchar(50)=null,@CarRequest Nvarchar(50)=null,@SpecialInstructions Nvarchar(MAX)=null ,@FaxAuth Nvarchar(50)=null ,
	  @MedicalReportRecieved Nvarchar(50)=null,@EnteredBy Nvarchar(MAX)=null ,@Attachments Nvarchar(50)	=null ,
	  @Title Nvarchar(10)= null ,@FaxNumber Nvarchar(50)=null ,@Attn Nvarchar(50)=null,@DoctorClinic Nvarchar(50)=null,@Year_Ref1 VARCHAR(8) =null,
	  @Year_Ref2 VARCHAR(8) =null,@DIARY_UID  Nvarchar(MAX)=null,@Contact Nvarchar(50)= NULL

	  
         
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  
	
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'SELECT EnteredBy,RequestedBy,AuthBy,Date,Time,PickUpTime,Title,PatientName,Contact,Ref ,Auth,PickUpAddress,DoctorClinic,Attn,Address,Comments,Interpreter,CarRequest,
				FaxAuth,FaxNumber,SpecialInstructions,MedicalReportRecieved,DIARY_UID 
				FROM DIARY WHERE ( DeleteRecord is null or DeleteRecord = 0)  '   
	
	   --IF  @Date <> ''
    --        SET @SQL = @SQL + '   AND DATE = ''' + CAST(@Date AS VARCHAR(20))  + '''' 	 

	    IF @Date1  <> '' AND @Date2  <> ''
		      SET @SQL =@SQL+ '   AND Date >= ' + ''''+ CAST(@Date1  AS VARCHAR(20))  + '''' + ' and Date <= ' + ''''+ CAST(@Date2  AS VARCHAR(20)) +''''		  
	    ELSE IF  @Date1  <> ''
			  SET @SQL = @SQL+ '   AND  Date >= '+ '''' + CAST(@Date1  AS VARCHAR(20))  +''''	
		Else IF @Date2  <> ''
			   SET @SQL = @SQL+ '  AND  Date <= '+'''' + CAST(@Date2  AS VARCHAR(20))+''''
	   IF  @PickUpTime <> ''
             SET @SQL = @SQL + '  AND CAST(PickUpTime AS Time) = ''' + @PickUpTime + ''''
       IF  @PatientName <> '' 	       
			 SET @SQL = @SQL + '  AND PatientName LIKE  ''%' + CAST(@PatientName AS  Nvarchar(MAX)) +  '%'''
       IF  @PickUpAddress <> ''
             SET @SQL = @SQL + '  AND PickUpAddress LIKE ''%'+ CAST(@PickUpAddress AS  Nvarchar(50)) + '%'''
	   IF  @AuthBy  <> ''
             SET @SQL = @SQL + '  AND AuthBy = ''' +  CAST(@AuthBy AS NVARCHAR(50))+ ''''
	   IF  @Contact <> ''
             SET @SQL = @SQL + '  AND Contact = ''' + CAST(@Contact  AS  Nvarchar(50))  + '''' 
	   IF  @Time <> ''
             SET @SQL = @SQL + '  AND CAST(Time AS Time) = ''' + @Time + ''''
	   IF  @Ref  <> ''
             SET @SQL = @SQL + '  AND Ref = ''' + CAST(@Ref  AS  Nvarchar(50))  + '''' 	 		
	   IF  @Auth   <> ''       
	         SET @SQL = @SQL + '  AND Auth  LIKE  ''%'+ CAST(@Auth  AS  Nvarchar(50)) + '%'''			 		
	   IF  @RequestedBy  <> ''       
	         SET @SQL = @SQL + '  AND RequestedBy = ''' +  CAST(@RequestedBy AS NVARCHAR(50))+ ''''
   	   IF  @DoctorClinic  <> ''
             SET @SQL = @SQL + '  AND DoctorClinic LIKE ''%'+ CAST(@DoctorClinic AS NVARCHAR(50)) + '%'''
	   IF  @Address   <> ''
             SET @SQL = @SQL + '  AND Address LIKE ''%'+ CAST(@Address AS NVARCHAR(50)) + '%'''
       IF  @Comments   <> ''
             SET @SQL = @SQL + '  AND Comments LIKE ''%'+ CAST(@Comments AS NVARCHAR(max)) + '%'''
	   IF  @Interpreter  <> ''
             SET @SQL = @SQL + '  AND  Interpreter LIKE ''%'+ CAST(@Interpreter AS NVARCHAR(50)) + '%'''
       IF  @CarRequest   <> ''
             SET @SQL = @SQL + '  AND  CarRequest LIKE ''%'+ CAST(@CarRequest AS NVARCHAR(50)) + '%'''
       IF  @SpecialInstructions  <> ''
             SET @SQL = @SQL + '  AND  SpecialInstructions LIKE ''%'+ CAST(@SpecialInstructions AS NVARCHAR(max)) + '%'''
       IF  @FaxAuth   <> ''
             SET @SQL = @SQL + '  AND  FaxAuth = ''' + CAST(@FaxAuth  AS  Nvarchar(50))  + '''' 	 
	   IF  @MedicalReportRecieved  <> ''
             SET @SQL = @SQL + '  AND  MedicalReportRecieved LIKE ''%'+ CAST(@MedicalReportRecieved AS NVARCHAR(50)) + '%'''
	   IF  @EnteredBy <> ''
             SET @SQL = @SQL + '  AND  EnteredBy  LIKE ''%'+ CAST(@EnteredBy AS NVARCHAR(max)) + '%'''	 
	   IF  @Title <> ''
             SET @SQL = @SQL + '  AND  Title  LIKE ''%'+ CAST(@Title AS NVARCHAR(10)) + '%'''
	   IF  @FaxNumber <> ''
             SET @SQL = @SQL + '  AND  FaxNumber LIKE ''%'+ CAST(@FaxNumber AS NVARCHAR(50)) + '%'''    
	   IF  @Attn <> ''
             SET @SQL = @SQL + '  AND  Attn LIKE  ''%'+ CAST(@Attn AS NVARCHAR(50)) + '%'''   
    
		IF @Year_Ref1  <> '' AND @Year_Ref2  <> ''
		      SET @SQL =@SQL+ '   AND Year_Ref >= ' + CAST(@Year_Ref1  AS VARCHAR(8))  + 'and Year_Ref <= ' + CAST(@Year_Ref2  AS VARCHAR(8))  			  
	    ELSE IF  @Year_Ref1  <> ''
			  SET @SQL = @SQL+ '   AND  Year_Ref >=  ' + CAST(@Year_Ref1  AS VARCHAR(8))  
		Else IF @Year_Ref2  <> ''
			   SET @SQL = @SQL+ '  AND  Year_Ref <= ' + CAST(@Year_Ref2  AS VARCHAR(8)) 
			  
     




----	A period of time --> WHERE date >= @date1 and date <= @date2 (you will to receive two parameters for the date period (min and max dates).

----Before than ---> WHERE date < @date
----Later than --> WHERE date > @date

	   SET @SQL = @SQL + 'ORDER BY Date'		
	

	   
	  --Print @SQL
	 
	
END

Open in new window

SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RIAS

ASKER

Thanks Vitor,

Please refer https://www.experts-exchange.com/questions/29020396/Sql-Query.html
You helped me with in the past.
But,Still not fixed.
Oh, just now I've realized that they were datetime columns.
By the way, if Date is datetime why do you need a Time column? You could store the time in the Date column and avoid having an extra column.
Avatar of RIAS

ASKER

Vitor,
Thats the design of the table and I am not authorised to change it.
Cheers!
At least report it :)
Avatar of RIAS

ASKER

Sure Sir!
>Please refer https://www.experts-exchange.com/questions/29020396/Sql-Query.html
RIAS - Do us a favor and include all relevant details in the question, as opposed to a 'go fish through this link and find what I'm asking'.  Thanks in advance.

>Thats the design of the table and I am not authorised to change it.
In that case it might not be a bad idea to create a view that consumes both date(datetime) and time(datetime) columns and returns a single datetime, and then use the view in your coding.   Having two datetime columns this way is just begging for errors, and I'm having a vague memory of being in that same situation with an airline client working on a database that was 30+ years old and could not be modified.
Avatar of RIAS

ASKER

Jim,
I have given all information in proceeding post.

Cheers
Avatar of RIAS

ASKER

Cheers!