Date format check

This should work:

SELECT FORMAT(CONVERT(DATETIME, @DueDate, 101), 'yyyy/MM/dd', 'en-US')

Select allOpen in new window

---

»bp

It entirely depends on what data is inside the nvarchar data. I suggest you use TRY_CAST() or TRY_CONVERT()
e.g.

with tbl as (
     select N'2019/10/21' as DueDate union all
     select N'23-10-2019' as DueDate union all
     select N'bad-date-data' as DueDate 
    )
select
    coalesce(try_convert(date, DueDate,105), try_convert(date, DueDate,111)) as [using_try_convert]
  , try_cast(DueDate as date)                                                as [using_try_cast]
from tbl
;

Select allOpen in new window

The advantage of  these functions is that they do not fail if the string cannot be converted to date (instead of failing they return NULL)

Note that TRY_CONVERT() also allows you to specify the date format of the input data,, in the example above I have mixed data with both yyyy/mm/dd and dd-mm-yyyy

This article lists the date style numbers you may use.

Once you have the data converted to date THEN (and only then) can you reliably output the information as yyyy-mm-dd and here you may use convert() as shown in the article, ot format() like Bill Prew has.

   

is yyyy-mm-dd?

SELECT RIGHT(DueDate,4)+'/'+SUBSTRING(DueDate,4,2)+'/'+LEFT(DueDate,2)

It is correct except that instead of - it has /. It is assumed that DueDate is a string date and has a format "dd?mm?yyyy" where "?" It can be any date separator.

If DueDate contains any valid date string, let SQL make sure the result is always yyyy-mm-dd:

SELECT CONVERT(varchar(10), CAST(DueDate AS date), 120)
FROM ( VALUES(GETDATE()),('20190103'),('15May1986'),('04/05/1996') ) AS test_dates(DueDate)

Storing dates as strings is never a great solution. Consider the following

31-02-2017

Is not a valid date but it could be stored in that column. So using string functions to flip it around into yyyy/mm/dd sequence would still be an invalid date

2017/02/31

Use try_cast() or try_convert() to get valid dates first, then output in the desired format.

this date is coming form CSV file.then data insert in to the nvarchar(10)  filed.
I asked to user to dd/mm/yyyy
But if user change date format or if there is no proper date change how to do that.Any idea much appriciated

If you're on MSSQL 2008, there are not many options besides doing lots of checks (like below) or doing the date check outside of SQL...

You can look at the example below (should add checks for existing "/" on positions 3 and 6 ...) and try figuring out a way.

DECLARE @DueDate nvarchar(30) -- Your input datetime in dd/mm/yyyy

DECLARE @CheckDate datetime
SET @CheckDate = CONVERT(datetime, RIGHT(@DueDate,4)+LEFT(@DueDate,2)+SUBSTRING(@DueDate,4,2))

IF CONVERT(varchar(10), @CheckDate, 103) = CONVERT(varchar(10), @DueDate) THEN
      PRINT('Happy times')
ELSE
      PRINT('Input date was not in correct format')

Let us know if you need any further help.

BR,
J.

Attempting to set a date/datetime column to an invalid value will cause a run-time error; you'd never get to the PRINT statement.

If you want to do this as part of a SELECT statement, use a CASE statement.  The critical thing here is that we aren't trying to force only one format of valid date to be allowed.  ANY format that yields a valid date will work; note the different date formats I used in my sample values.:

SELECT
    DueDate,
    CASE WHEN ISDATE(DueDate) = 1
         THEN CONVERT(varchar(10), CAST(DueDate AS date), 120)
         ELSE '' END AS DueDate_YYYY_MM_DD
FROM ( VALUES
    (CAST(GETDATE() AS varchar(30))), /*good datetime*/
    ('20190103'),('15May1986'),('04/05/1996'), /*good dates*/
    ('20110230'),('Jan 20'),('today') /*bad dates*/
) AS test_dates(DueDate)

Sorry I  forgot try_convert isn't available in SQL2008 (started for 2012)

thus I recommend using is_date as Scott has shown.

@ukerandi,

Are you all set with this now, or do you need more help?  If all set, could you please close it out now.  If you need help with the question close process take a look at:

• How do I select a solution for my question?

---

»bp