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.
4  3145  ck9663 2,878
Recognized Expert Specialist
Try using 109 as type parameter.
Read more here
-- CK
Delerna 1,134
Recognized Expert Top Contributor
and what is the ,9 in the convert function for?
Shouldn't it be just
convert(varchar (30),min(userti me)) AS mintime
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.
and what is the ,9 in the convert function for?
Shouldn't it be just
convert(varchar (30),min(userti me)) AS mintime
The 9 is for the style of the conversion. Ref Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Kieran Benton |
last post by:
Hi,
I have quite a lot of metadata in a WinForms app that I'm currently storing
within a hashtable, which is fine as long as I know the unique ID of the
track (Im storing info on media files). Up until now I've been sending the
track info to a seperate server app using serialization/sockets which stores
the metadata in a mysql db so that I can query on Artist/Album etc as well.
However, I'm looking to remove the server and move into a more...
|
by: jblankenburg |
last post by:
Please help!
I am hunting high and low for an equivalent function for MSSQL's
DATENAME function. Here's the spec on the function from MSSQL's Books
Online:
DATENAME
Returns a character string representing the specified datepart of the
specified date.
|
by: LizRickaby |
last post by:
My client has several Access databased that they wanted converted into MSSQL to be able to access them online (excuse the pun). I added the first Access database, LenderPrograms, as a table in the MSSQL database with no problem, it runs fine. But when I tried to add the other Access database, LenderContacts, as another table, it doesn't work.
When I first import LenderContacts using Enterprise Manager it turns all of the "text" fields into...
|
by: pks83 |
last post by:
In MSSQL I am using datetime as the column type and trying to execute the below mentioned query
select time from tickets where ticket_id = '1';
when i execute the query on sql manager window it shows it correctly with seconds and milliseconds in the output like this
2008-01-30 13:58:04.000
But when i run the sql through PHP it replaces the seconds and milliseconds with AM/PM
sql1="select time from tickets where ticket_id = '1' ";
|
by: pks83 |
last post by:
In MSSQL I am using datetime as the column type and trying to execute the below mentioned query
select time from tickets where ticket_id = '1';
when i execute the query on sql manager window it shows it correctly with seconds and milliseconds in the output like this
2008-01-30 13:58:04.000
But when i run the sql through PHP 5.2.5 it replaces the seconds and milliseconds with AM/PM
sql1="select time from tickets where ticket_id = '1'...
| | |
by: Shawn Beasley |
last post by:
Hi List,
I am searching franticly for a solution (or the procedure) to setting
the coding of a new DB to UTF-8. I can find no setting in the Server
Manager, during creation of the DB, to influence this. Can someone
please show me the way? Thanks
--Shawn
|
by: chromis |
last post by:
Hi,
I've never coded in ASP before and I'm trying to port a couple of simple PHP files to ASP.NET. The first file addScores.php takes form data and a hash and inserts the data into the db, it returns error or success depending on the success of the query. This is then used by a third party piece of software.
My first problem is how to retrieve the result of an insert query to see if the query is successful or not:
PHP Version:
//...
|
by: chromis |
last post by:
Hi,
I wouldn't post such a lazy question usually but I've got to have this sorted for the end of the day, sorry! Could someone tell me how to write this statement in MSSQL please?
SELECT scores.id, scores.name, scores.email, scores.time, scores.milliseconds
FROM scores
INNER JOIN
(
SELECT T2.id, T2.name, T2.email, min(time_to_sec(T2.time)*1000+T2.milliseconds) AS mintime
|
by: chromis |
last post by:
Hi,
I've recently been porting a little php / mysql script over to asp / mssql and I've got issues with the timestamp data type. With mysql i could easily insert 01:01:01 into the timestamp column, however mssql throws an error and whats the string passed in to be converted into a timestamp data type.
Is this the right datatype to use? I read somewhere that the timestamp column is readonly and is updated each time the record is updated, if...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| | |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
