Convert string to SQL Server DateTime which should be in "yyyy-MM-dd hh:mm:ss" format

Question

C#'s DateTime is in dd.MM.yyyy hh:mm:ss format so it is not compatible with the one SQL Server uses. I want it to be in yyyy-MM-dd hh:mm:ss format. Does anyone know how I can do this?

DateTime odt = new DateTime(year, month, day, hour, minute, second);
string odt2= odt.ToString("yyyy-MM-dd hh:mm:ss");

Here is the stored procedure I'm using;

ALTER PROCEDURE [dbo].[SELECT_ORDERSTATUS_BY_ORDERDATETIME]
    @ORDERDATETIME DateTime
AS
    SELECT dbo.OrderTable.Status
    FROM dbo.OrderTable
    WHERE OrderDateTime = @ORDERDATETIME

And here is my C# code. ExecuteScalar returns null value and I think it's because DateTime's simply do not match.

DateTime odt = new DateTime(year, month, day, hour, minute, second);

SqlConnection con = generateConnectionString();
con.Open();

SqlCommand command = generateCommand("SELECT_ORDERSTATUS_BY_ORDERDATETIME", con);

SqlParameter paramOrderDateTime = new SqlParameter("@ORDERDATETIME", odt);
command.Parameters.Add(paramOrderDateTime);

string status = (string)command.ExecuteScalar();
currentstatusTextbox.Text = status;
con.Close();

See http://stackoverflow.com/questions/202243/custom-date-time-formatting-in-sql-server — Mitat Koyuncu, Aug 11 '15 at 07:28
ADO.NET is perfectly aware of how both .NET's `DateTime` and SQL Server's `datetime` data types work and how to translate between them. Neither of them **have** a format. What you're seeing is what happens when those types are converted to *strings*. Avoid those conversions and use the built in support in ADO.Net to translate between them. — Damien_The_Unbeliever, Aug 11 '15 at 07:29
**Dates**, either in SQL Server or C# **have no format**, they are binary values. If you use date-typed fields in your tables and pass date-typed parameters to your parameterized queries, you'll have no problem. If you do , it's because your own code forced a conversion to a string. Please post the relevant code — Panagiotis Kanavos, Aug 11 '15 at 07:30
Please post the query that produces the problem. Otherwise one can only guess that you are using string concatenation instead of parameterized queries and converting dates to arbitrary strings instead of passing them as parameters, or even as recognizable date literals — Panagiotis Kanavos, Aug 11 '15 at 07:33
As @PanagiotisKanavos mentioned, I try to explain your root mistake of thinking in my answer. If you won't explain your exact problem, it is not possible to help you completely. — Soner Gönül, Aug 11 '15 at 07:43
If your call returns no data it's because there *is* no data with the exact datetime value (right down to the second). Try executing the procedure with the exact same datetime to see whether any data is returned — Panagiotis Kanavos, Aug 11 '15 at 07:51

score 3 · Accepted Answer · edited May 23 '17 at 11:44

3

SQL Server keeps DateTime values as a binary. It doesn't have any format concept. What you see is only their textual representation in a SSMS or in a query window.

If you use datetime or datetime2 as a column type, you can directly use your odt value in a parameterized queries.

After your edit:

As far as I can see, you didn't set your CommandType to StoredProcedure. If your Status column is mapped with string and all other things are okey, this should work;

using(SqlConnection con = generateConnectionString())
using(var command = con.CreateCommand())
{
    command.CommandText = "SELECT_ORDERSTATUS_BY_ORDERDATETIME";
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add("@ORDERDATETIME", SqlDbType.DateTime).Value = odt;

    con.Open();
    string status = (string)command.ExecuteScalar();
}

edited May 23 '17 at 11:44

Community

1
1

answered Aug 11 '15 at 07:31

Soner Gönül

97,193
102
206
364

TSQL _has_ date format as it is a language and represented as _text_. You are right about it is preferred to use some intelligent layer which hides the TSQL details so at higher level the developer do not have to care about this syntax, just use a native .NET DateTime. However your argument in your first line is completely inappropriate, and this thoughts are far from the "explained" – g.pickardou Aug 11 '15 at 07:43
2

@g.pickardou no it doesn't. You mistake the connection-level language display format for an actual data type format. You can check the documentation for the various datetime types if you want, for the exact bit layout. No intelligent layer is involved with parameterized queries - it's the same as using stored procedures or functions with date-typed parameters. The parameter values are passed as binaries – Panagiotis Kanavos Aug 11 '15 at 07:47
2

@g.pickardou I don't understand what do you mean with _inappropriate_. `date`, `datetime` and `datetime2` is mapped with `DateTime` in CLR side. The _format_ you talking about can be used in a query window for example since you can't insert them as a binary. For example when you try to say `Insert into ... values ('2004-05-23T14:25:10')`. These are the proper formats when you try to use a `datetime` or `datetime2` as a _string literal_. – Soner Gönül Aug 11 '15 at 07:51
1

@SonerGönül many people confuse the date format option or a collations's date format for an actual date type format. – Panagiotis Kanavos Aug 11 '15 at 07:56
@PanagiotisKanavos: TSQL (and generally SQL) are _not_ "connection-level language display format", and they _have_ date literal syntax format specification. Think about an INSERT statement: There are syntax rules how the date literal must be represented there. So it _has_ a format. However this fact became out of topic, after the question was edited, and it turned out it is a SqlCommand parameter question. – g.pickardou Aug 11 '15 at 07:57
1

@g.pickardou - once some data has been transformed into a `datetime` value, there is no information stored about *how* that value was constructed. That is what people mean when they describe the type as not having a format - there's no way to take a value of that type and ask it to transform back into the same string that was used to produce it. – Damien_The_Unbeliever Aug 11 '15 at 08:02
@SonerGönül: I mean: It is obvious we do not have to care about how an RDBMS _represents_ the date _internally_ this simple case (similarly we also do not case how say C# represents it _internally_. However we sometimes interface with our RDBMS with SQL (like T-SQL or PL/SQL) what are textual languages, and they _have_ date literal syntax rules. (unlike C# where there is no date literal). – g.pickardou Aug 11 '15 at 08:04
@g.pickardou whate you refer to are the *connection-level* [SET DATEFORMAT](https://msdn.microsoft.com/en-us/library/ms189491.aspx) and the database/field-level collation-specific *string literal* formats with actual data type formats. The docs describe how to handle date and time literals in [Use Date and Time Data](https://msdn.microsoft.com/en-us/library/ms180878(v=sql.105).aspx), where it's mentioned that only the unseparated and the ISO formats are treated uniformly across all locales – Panagiotis Kanavos Aug 11 '15 at 08:04
So there _is_ a DATEFORMAT, both standard (regardless of connection state), both configurable. Again the question before edited was not defined how the developer was communicating with the Sql Server. This means it could be meaning a question which ask for how to format a date to a specifice (expected) format. That's an other question, that using SqlParameter is the _preferred_ way to handle the issue. – g.pickardou Aug 11 '15 at 08:07
@g.pickardou They _have to_ has these string literals. Otherwise, how we can represent a _proper_ `datetime` value in a query window in SSMS? Otherwise, how you can represent a value in a query window which saved as a binary for human readable format? – Soner Gönül Aug 11 '15 at 08:08
2

@g.pickardou - the existence of literals doesn't change the nature of a data type - or if it does, how do you explain the fact the VB.Net *does* have a datetime literal, but both C# and VB.Net use the exact same `DateTime` type within .NET? – Damien_The_Unbeliever Aug 11 '15 at 08:10
@SonerGönül: Yes, and not only in SSMS, but in _all_ scripts, what are written in SQL and all (bad practice) .NET programs, what are concatenating SQL statement as strings. That's why answering "It doesn't have any format concept." is not true. – g.pickardou Aug 11 '15 at 08:13
@Damien_The_Unbeliever: I surely expressing myself very badly if it is so not understandable in many readers. You are right, but as I see I did not write anything what suggest that. The only thing I wrote: When communicating with SQL server, (scripts, concatenated SQL strings (bad practice)) there _is_ a format expectation, so the statement "It doesn't have any format concept" is false. – g.pickardou Aug 11 '15 at 08:17
1

@g.pickardou - what people mean by "doesn't have a format" is, for example: `declare @dt1 datetime,@dt2 datetime; select @dt1 = '20150811',@dt2='2015-08-11T00:00:00'; select CAST(@dt1 as binary(8)),CAST(@dt2 as binary(8))`. Both results are `0x0000A4F100000000` - there's no way to distinguish what "format" was used to construct the values - and that's what people mean when they say that the data type "doesn't have a format". And as you yourself are aware, there are ways to pass datetime values to the server that avoid every working with a string representation. – Damien_The_Unbeliever Aug 11 '15 at 08:27
@Damien_The_Unbeliever: Again, true. However the original question was _not_ about: How SQL Server represents date. The question was about (in its original form) about how can one _pass_ a datetime value to SQL Server. So that's why I wrote, answering "It doesn't have any format concept" is not appropriate answer. No one (even the original question) stated that a binary internal representation has textual format, and while this fact is completely true (I mean it has not), this has nothing to do with a problem, when a textual SQL string or script must represent a date, and how one can do it. – g.pickardou Aug 11 '15 at 08:35

Convert string to SQL Server DateTime which should be in "yyyy-MM-dd hh:mm:ss" format

1 Answers1