[Solved] SELECT indicate datetime format

EverSQL Database Performance Knowledge Base

SELECT indicate datetime format

Database type:

I am running a query like this:

SELECT dt, x, y 
FROM mytable 
WHERE dt BETWEEN '2015-25-05 00:00:00.000' AND '2015-25-05 12:00:00.000';

dt is a datetime and I insert in the format 'yyyy-dd-MM hh:mm:ss.fff'. My output dt-format is only 'yyyy-dd-MM hh:mm:ss'.

I also need the milliseconds in my output. Please can somebody tell me how I get them? I am sorry that I could not find this syntax by myself...

EDIT:

I thought the problem was easier than it is. I tried to use

SELECT CONVERT(DATETIME, dt,109) 

and it works well in MSSQL-Management-Studio.

But I want to use the dt-value in C# in Visual Studio 2013. I run a query and write the output in a file.

I use System.Data.SqlClient.SqlConnection, System.Data.SqlClient.SqlCommand and System.Data.SqlClient.DataReader to get the values like this:

SqlConnection conn = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(query, conn);
SqlDataReader reader = command.ExecuteReader();

while (reader.Read())
{
    //write one line; column is the number of columns
    for (int n = 0; n < column; n++)
    {
        writer.Write(reader[n].ToString());
    }
    writer.WriteLine("");
}

I changed the query in this program and used the CONVERT-function, but I still do not get milliseconds... I guess it is more a C# / Visual Studio problem.

SOLUTION:

I added this to write the datetime:

 if(n==0)
     writer.Write(reader.GetDateTime(0).ToString("yyyy-MM-ddThh:mm:ss,fff"));

Thank you

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX mytable_idx_dt ON mytable (dt);
The optimized query:
SELECT
        mytable.dt,
        mytable.x,
        mytable.y 
    FROM
        mytable 
    WHERE
        mytable.dt BETWEEN '2015-25-05 00:00:00.000' AND '2015-25-05 12:00:00.000'

Related Articles



* original question posted on StackOverflow here.