locked
Comparing two dates and formating the date RRS feed

  • Question

  • To compare two dates is it good enough to say:

    case when Table1.DatetimeColumn < Table2.DatetimeColumn then Table1.DatetimeColumn .....

    or do we need to first convert before using <?

    I want to compare them even if there is difference of one second between them

    I also want to convert the current date format of my date which is in "2010-01-14 11:04:26.327" to 1/14/2010

    Thanks

    Thursday, January 14, 2010 4:08 PM

Answers

  • You don't need to convert to compare. And here is how you can convert the datetime


    DECLARE @t TABLE(Dat1 DATETIME, Dat2 DATETIME)
    INSERT INTO @t
    SELECT GETDATE(),DATEADD(ss,1,GETDATE())
    
    SELECT 1 
    FROM @t
    WHERE Dat1 < Dat2
    
    SELECT CONVERT(VARCHAR(10),Dat1,101)
    FROM @t

    Abdallah El-Chal, PMP, ITIL, MCTS
    • Marked as answer by techytechy Thursday, January 14, 2010 5:17 PM
    Thursday, January 14, 2010 4:12 PM
  • Here is one way

    SELECT CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '/' + 
    		CAST(DAY(GETDATE()) AS VARCHAR(2)) + '/' + 
    		RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)),2)




    Abdallah El-Chal, PMP, ITIL, MCTS
    • Marked as answer by techytechy Thursday, January 14, 2010 5:16 PM
    Thursday, January 14, 2010 4:53 PM

All replies

  • You don't need to convert to compare. And here is how you can convert the datetime


    DECLARE @t TABLE(Dat1 DATETIME, Dat2 DATETIME)
    INSERT INTO @t
    SELECT GETDATE(),DATEADD(ss,1,GETDATE())
    
    SELECT 1 
    FROM @t
    WHERE Dat1 < Dat2
    
    SELECT CONVERT(VARCHAR(10),Dat1,101)
    FROM @t

    Abdallah El-Chal, PMP, ITIL, MCTS
    • Marked as answer by techytechy Thursday, January 14, 2010 5:17 PM
    Thursday, January 14, 2010 4:12 PM
  • Yes, you can directly compare dates, there is no need to convert. To format date for display or reporting purposes you use the CONVERT function with appropriate style. In your case seems CONVERT(VARCHAR(10), datetime_column, 101) will work. Note that the DATETIME data type has not format, it is binary value. Formatting is done only for presentation.
    Plamen Ratchev
    Thursday, January 14, 2010 4:13 PM
  • The compare is strictly numeric, so < is fine for comparing dates.

    Look up CONVERT in the SQL Server Books Online and you will see many formats.  I believe that you want:

       SELECT CONVERT(CHAR(10),DatetimeColumn,101)

    RLF
    Thursday, January 14, 2010 4:14 PM
  • Sorry, I need the date format in 1/14/10 ie, m/d/yy
    I already checked the possible codes.

    SELECT

     

    CONVERT(VARCHAR(10),getdate(),1)

    This gives output in 01/01/10 format, I need in 1/1/10

    Thanks


    Thursday, January 14, 2010 4:49 PM
  • Here is one way

    SELECT CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '/' + 
    		CAST(DAY(GETDATE()) AS VARCHAR(2)) + '/' + 
    		RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)),2)




    Abdallah El-Chal, PMP, ITIL, MCTS
    • Marked as answer by techytechy Thursday, January 14, 2010 5:16 PM
    Thursday, January 14, 2010 4:53 PM
  • thanks Abdshall. Works great
    Thursday, January 14, 2010 5:16 PM