How do I get my datetime field from server to client without the PM/AM 1

[Themuppeteer]

Hi,
I have an SQL DB with an column which stores date and time with the function getdate(), this is great, because it stores the time like this : 5/04/01 14:44:36 wich is good because I need that. The problem is that if I send it to my client,(or even in SQL Query analyser) ,I get this annoying abbreviation 'apr 5 2001 14:44PM'.

Since I store the time so as to know how many seconds did pass between 2 loggings, this abbreviation is useless to me.

So my questions are:
1) Does all the information actually get to the client
and is it just wrongfully represented on the screen or
do I have to change something in my stored procedure ?

2) Is there a way to get the time even more specific then
in seconds (like tenths of a second),because sometimes I
have more then 1 logging in a second.

Thanks,

Mark.

 

[JNC73]

Hello StudentFromBelgium,

To get the time in a format like HH:MM:SS without a AM/PM string you must use the CONVERT function of SQL around the datetime field in your select.

A example is

CONVERT(VARCHAR(30), fieldname, 108) /* HH:MM:SS */

Or

CONVERT(VARCHAR(30), fieldname, 114) /* HH:Mi:SS:MMMM */

Or

CONVERT(VARCHAR(30), fieldname, 113) /* dd mon yyyy HH:Mi:SS:MMMM */


Hope this is what you were searching for.

JNC73

 

[Themuppeteer]

Thank you very much,that was exactly what I needed!

Now I see my data the way I want in SQL analyser.
But another problem arised: The client doesn't want to show the data anymore in the the testlistbox (which he did before with the AM/PM data).
Do you have any idea what that can be ?
Please let me know.

Thanks,
Mark.

 

[JNC73]

I don't know what kind of programming tool you use for the client. I know that in FoxPro when I convert a date to a Varchar I had to tell FoxPro that the field I returned was a string in stead of a datetime field. This could be the same problem for you.

 

[Themuppeteer]

The client uses VB6.0,

But I think the problem is in the stored procedure because,
when we do the following in the Form Load in Visual Basic6.0:

msgbox("before&quot
Set rs = cmd.Execute
msgbox("after&quot

The 2de messagebox never appears, but the form with the empty listbox does.

Just before the empty form appears,we get the following errormessage:
'Syntax error converting datetime from characterstring.'

maybe this can be important:
(*) In the db, the date and time is from the type datetime.
(*) The stored procedure puts it in a varchar(30) field.
(*) Everything works just fine in SQL Query analyser.

Mark.

 

[JNC73]

I unfortunatly don't know much of VB6.0.

But the error message you mentioned indicates that the command Set rs = cmd.Execute does something with the resultset of the Stored Procedure. Somewhere in the code you expect an datetime field but get an string instead. This is because you changed the SP but forgot to change the code in VB.

It can also mean that the control in which you put the datetime expects an date value in stead of an string and you just have to change that.

If this doesn't help I cannot help you further and perhaps ask the question in the VB forum.

 

[Themuppeteer]

Ok,I'll go search a bit more on the client side and go to the VB forum.

Thanks a lot for the help JNC73!
Mark.