Validating A DateTime Value and Returning in a Specific Format

What database is it for?
SQL Server? Oracle? ...

Most relational databases have very product specific facilities for temporal data. For example SQL Server does NOT save dates in yyyy-mm-dd format at all, in fact a datetime data type is stored as 2 integers (1 for the date and one for the time of day). An Oracle "date" is stored as 7 integers.

So, please let us know what database it is for, and also the exact data type (date? datetime" datetime2? timestamp? (if not SQL Server)

Excellent. Thanks.

The data is going into MS Sql Database.

Knowing this is related to your previous question:
I agree that if the SQL procedure you have accepts a date datatype, the you need to pass in a date data type not a string for the procedure to implicitly convert.

When I passed web/form values from an app to a stored procedure, I had the procedure take string values as parameters and converted them to database date types inside the procedure.

SQL Server has many options for converting strings to dates in pretty much any format you want.

Better yet, give the app a date-picker instead of a textbox.  This further simplifies things.

I would change the app and put edits on the field to force correct values on validation.  Then you don't have to worry about things inside the procedure.  Pick the format mask best for the users, pass the string into the procedure and let the procedure deal with the string to date conversion.

You can allow the app to pass in the null value and let the procedure deal with the null to 1901-01-01 substitution.

I would look at changing the procedure to allow a null value in the query instead of forcing some minimum date value to query by.

>>"The data is going into MS Sql Database."

OK, but it's a pity I didn't ask for the version.
Can you please note that for any database related question we really need to know both database type and version[?

If you are accepting data from a user, for entry into a date or datetime column, then the front-end should use a date picker - there are too many things to go wrong if using strings. i.e. these are excellent words!

>>"Better yet, give the app a date-picker instead of a textbox.  This further simplifies things."


However, if you are stuck with a string and you want SQL Server to "test" this there are several functions in TSQL for this e.g.

declare @bad  as varchar(20) = '2019-02-29'
declare @good as varchar(20) = '2019-02-28'

select
  isdate(@bad)
, try_cast(@bad as date)
, try_convert(date,@bad,120)

, isdate(@good)
, try_cast(@good as date)
, try_convert(date,@good,120)

Select allOpen in new window

isdate() returns 1 if recognized as a date (or datetime)
try_cast/try_convert return NULL if the data cannot be converted (available in SQL Server 2012 or later)

try_convert uses a "style" number as the third parameter you can find more of these here

see:
https://docs.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/t-sql/functions/isdate-transact-sql?view=sql-server-2017

>>takes datetime variable and tests if it is a valid datetime

Just to be pedantic.  A datetime variable is always a valid datetime.  You are probably talking about a string.  Just because you see 12.12.2018 for instance does not mean that is how the datetime stores it.  You will find that integers (see earlier comments) aren't involved in the .net world either.

I only have your question, so I cannot guess the context, but - as you state C# as the language, not T-SQL - Andy already answered the first part, that a DateTime variable cannot hold anything but a valid date. The only option is to make it nullable to also allow for null for an unknown date:

DateTime? someDate;

Select allOpen in new window

As for the second part, formatting - which only makes sense for concatenating in an SQL string - you can use ToString:

DateTime someDate = DateTime.Today;
string formattedDate = someDate.ToString("yyyy'-'MM'-'dd");

Select allOpen in new window