locked
Check date format is dd/mm/yyyy RRS feed

  • Question

  • Hi folks, is it possible to check if  the date has been formatted as dd/mm/yyyy i.e. something like this...

    if (@EnterDate <> dd/mm/yyyyy )

    SET @message = 'date not in the correct format' 

    Monday, November 2, 2015 1:45 PM

Answers

All replies

  • That kind of checking is normally done at the client side, not the SQL Server side.

    However, you can use TRY_CONVERT to test the value is valid.

    https://msdn.microsoft.com/en-us/library/hh230993.aspx?f=255&MSPPError=-2147217396

    • Marked as answer by brucey54 Monday, November 2, 2015 3:26 PM
    Monday, November 2, 2015 1:48 PM
  • Do you mean something like this?

    TRY_CONVERT(@EnterDate <> dd/mm/yyyyy )???

    Monday, November 2, 2015 2:02 PM
  • To add on to what Tom said, be aware that some date string formats are ambiguous. '02/11/2015' could be interpreted as either February 11 or November 2.  SQL Server will interpret datetime strings according to the current session DATEFORMAT setting.

    Consider using parameterized queries with strongly typed date/datetime values instead of strings to avoid ambiguity. 


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, November 2, 2015 2:03 PM
  • DECLARE @EnterDate varchar(25);
    SET @EnterDate = '02/11/2015'
    
    SELECT CASE WHEN TRY_CONVERT(date,@EnterDate,101) IS NULL THEN 'Error' Else 'OK' END
    
    

    • Proposed as answer by Naomi N Monday, November 2, 2015 5:18 PM
    Monday, November 2, 2015 3:49 PM