locked
how compare the date RRS feed

  • Question

  • Hi guys.,

         I need to compare 2 different type of date  first date format :

    2011 1
    2011 2
    2011 3

    second date format :

    July 2011
    June 2011
    April 2011
    March 2011
    February 2011

    compare the month if it's equal we need to display the data,Please share some example queries

    Thanks for advance

     

     


    Ranganathan.Palanisamy
    Thursday, March 10, 2011 10:59 AM

Answers

  • btw, when you really want to compare the month only, regardless of the year:

    DECLARE @TextDate1 NVARCHAR(255) = '2012 1' ;
    DECLARE @TextDate2 NVARCHAR(255) = 'January 2011' ;
    
    DECLARE @Date1 DATE = CAST(
     SUBSTRING(@TextDate1, 0, CHARINDEX(' ' ,@TextDate1)) + '-' +
     SUBSTRING(@TextDate1, CHARINDEX(' ' ,@TextDate1) + 1, LEN(@TextDate1)) + '-1' AS DATE) ;
    
    DECLARE @Date2 DATE = CONVERT(DATE, 
     '1 ' +
     SUBSTRING(@TextDate2, CHARINDEX(' ' ,@TextDate2) + 1, LEN(@TextDate2)) + ' ' +  
     SUBSTRING(@TextDate2, 0, CHARINDEX(' ' ,@TextDate2)), 106) ;  
    
    SELECT @Date1, @Date2, CASE WHEN MONTH(@Date1) = MONTH(@Date2) THEN '==' ELSE '!=' END ;
    

     


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    • Proposed as answer by Naomi N Friday, March 11, 2011 1:19 AM
    • Marked as answer by KJian_ Thursday, March 17, 2011 6:19 AM
    Thursday, March 10, 2011 1:17 PM
  • Hello,

    You can also try the following conversions and adopt your query according to that

    declare @d date = 'July 2011'
    select @d
    
    declare @str varchar(10) = '2011 1'
    declare @d2 date = replace(@str,' ','0')
    select @d2
    
    

     


    SQL Server and T-SQL Tutorials
    My Personal Site
    Our true mentor in life is science
    • Marked as answer by KJian_ Thursday, March 17, 2011 6:19 AM
    Thursday, March 10, 2011 3:06 PM

All replies

  • hi,

    first of all, why don't you have DATE values instead of ((N)VAR)CHAR values?

    E.g.

    DECLARE @TextDate1 NVARCHAR(255) = '2011 1' ;
    DECLARE @TextDate2 NVARCHAR(255) = 'January 2011' ;
    
    DECLARE @Date1 DATE = CAST(
     SUBSTRING(@TextDate1, 0, CHARINDEX(' ' ,@TextDate1)) + '-' +
     SUBSTRING(@TextDate1, CHARINDEX(' ' ,@TextDate1) + 1, LEN(@TextDate1)) + '-1' AS DATE) ;
    
    DECLARE @Date2 DATE = CONVERT(DATE, 
     '1 ' +
     SUBSTRING(@TextDate2, CHARINDEX(' ' ,@TextDate2) + 1, LEN(@TextDate2)) + ' ' +    
     SUBSTRING(@TextDate2, 0, CHARINDEX(' ' ,@TextDate2)), 106) ;   
    
    SELECT @Date1, @Date2, CASE WHEN @Date1 = @Date2 THEN '==' ELSE '!=' END ;
    
    

     


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Thursday, March 10, 2011 11:26 AM
  • btw, when you really want to compare the month only, regardless of the year:

    DECLARE @TextDate1 NVARCHAR(255) = '2012 1' ;
    DECLARE @TextDate2 NVARCHAR(255) = 'January 2011' ;
    
    DECLARE @Date1 DATE = CAST(
     SUBSTRING(@TextDate1, 0, CHARINDEX(' ' ,@TextDate1)) + '-' +
     SUBSTRING(@TextDate1, CHARINDEX(' ' ,@TextDate1) + 1, LEN(@TextDate1)) + '-1' AS DATE) ;
    
    DECLARE @Date2 DATE = CONVERT(DATE, 
     '1 ' +
     SUBSTRING(@TextDate2, CHARINDEX(' ' ,@TextDate2) + 1, LEN(@TextDate2)) + ' ' +  
     SUBSTRING(@TextDate2, 0, CHARINDEX(' ' ,@TextDate2)), 106) ;  
    
    SELECT @Date1, @Date2, CASE WHEN MONTH(@Date1) = MONTH(@Date2) THEN '==' ELSE '!=' END ;
    

     


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    • Proposed as answer by Naomi N Friday, March 11, 2011 1:19 AM
    • Marked as answer by KJian_ Thursday, March 17, 2011 6:19 AM
    Thursday, March 10, 2011 1:17 PM
  • Hello,

    You can also try the following conversions and adopt your query according to that

    declare @d date = 'July 2011'
    select @d
    
    declare @str varchar(10) = '2011 1'
    declare @d2 date = replace(@str,' ','0')
    select @d2
    
    

     


    SQL Server and T-SQL Tutorials
    My Personal Site
    Our true mentor in life is science
    • Marked as answer by KJian_ Thursday, March 17, 2011 6:19 AM
    Thursday, March 10, 2011 3:06 PM