Date format check

ukerandi
ukerandi used Ask the Experts™
on
Hi,
I need to know this date format is yyyy-mm-dd?
DECLARE @DueDate nvarchar(30)

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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
This should work:

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

Open in new window


»bp
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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
;

Open 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.
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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)
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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.

Author

Commented:
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

Commented:
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.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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)
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

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

thus I recommend using is_date as Scott has shown.
Bill PrewTest your restores, not your backups...
Top Expert 2016
@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:



»bp

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial