MsSQL - Not storing and returning milliseconds

[chromis]

Hi,

I know this question has been asked before, but I seem to be getting different results from everyone else.

I want to return times from my db table in this format: hh:mm:ss.mmm using mssql.

I'm using a datetime column to store the information and I am entering the data in this manner: yyyy-mm-dd hh:mm:ss.mmm which is I think the way that mssql stores datetime's in the database. The trouble is when I go to retrieve this data it returns it like this:

01/01/1900 00:00:00
09/09/2008 01:01:02

With no milliseconds.

My insert query is:

sqlStatement = "INSERT INTO scores (name,email,usertime,milliseconds) VALUES('" & fName & "','" & fEmail & "',convert(datetime, '" & fTime & "', 21),'" & fMilliseconds & "')"    
 

and my display query is:

sqlStatement = "SELECT scores.id, scores.name, scores.email, scores.usertime FROM scores INNER JOIN ( SELECT T2.name, T2.email, convert(varchar(30),min(usertime),9) AS mintime FROM scores T2 GROUP BY T2.name, T2.email ) T3 ON scores.name = T3.name AND scores.email = T3.email AND scores.usertime = T3.mintime ORDER BY T3.mintime"
 

Can anyone point out where I'm going wrong? I'm guessing it's to do with the convert() function. I've trawled the internet but not found the info i need.

Thanks.

[ck9663]

Try using 109 as type parameter.

Read more here

-- CK

[Delerna]

and what is the ,9 in the convert function for?

Shouldn't it be just

convert(varchar(30),min(usertime)) AS mintime

[chromis]

thanks ck. Does the style number effect the way in which the timestamp is entered into the database, so you can enter 12/12/2003 12:10:09AM instead of 12 12 2003 12:10:09AM and with the style number 109 it will enter it in the db as
12 12 2003 12:10:09AM?

I'm struggling because I've tryed a few of the styles and none of them seem to allow me to enter milliseconds...or at least the milliseconds are not being returned when i query the database.

[chromis]

and what is the ,9 in the convert function for?

Shouldn't it be just

convert(varchar(30),min(usertime)) AS mintime

The 9 is for the style of the conversion.

Ref