SELECT indicate datetime format

Question

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

[Link](http://stackoverflow.com/questions/715432/why-is-sql-server-losing-a-millisecond) this will be helpful. — MeshBoy, Mar 24 '15 at 11:24
121 will be yyyy-mm-dd... and he wants yyyy-dd-mm. I don't think you can do that without parsing the string and building it yourself. The real question is where is that datatime being used? If it is in some code or report the formatting should be done there — Juan, Mar 24 '15 at 11:24
Output format is something that belongs to presentation level, not to the SELECT statement. Having said that, are you sure that the column's datatype is datetime, and not perhaps datetime2(0) or something else? — dean, Mar 24 '15 at 11:25

score 0 · Answer 1 · answered Mar 24 '15 at 11:31

0

This will give you time in milliseconds

SELECT CONVERT(DATETIME, dt,109) AS dt,x,y
FROM mytable 
WHERE dt BETWEEN '2015-25-05 00:00:00.000' AND '2015-25-05 12:00:00.000';

answered Mar 24 '15 at 11:31

Alex

21,273
10
61
73

Thank you! I thought this would be my answer, but the problem was not clear to me... I added the solution to my post. – Friedrich Mar 24 '15 at 13:19

SELECT indicate datetime format

1 Answers1