August 9, 2002 at 7:36 pm
anyone know the way to convert datetime data in sql to vb (using an ADO) recordset while preserving millisenconds..
for example Format(objRs("DateTime_Col"),"MM/DD/YYYY hh:mm:ss:??")
what should I use instead of ??
Brian Lockwood
President
LockwoodTech Software
Brian Lockwood
President
ApexSQL - SQL Developer Essentials
August 10, 2002 at 6:09 am
There may be a way, I just don't know what it is. Let me know if you find out. One hack would be to convert the date to a varchar serverside and return that, letting SQL do the formatting for you.
Andy
August 10, 2002 at 11:10 am
quote:
There may be a way, I just don't know what it is. Let me know if you find out. One hack would be to convert the date to a varchar serverside and return that, letting SQL do the formatting for you.Andy
http://www.sqlservercentral.com/columnists/awarren/
I've spent a few hours on this and there appears there is no way to do this. no such formatting constants exist. to retrieve it server side is easy becuase sql provides a number of formats to allow it (see below) but in VB it is tough.
we managed to find a solution but it took a lot of custom code to infer the milliseconds and then append it to the format.
Here are the 3 SQL Formats
There are three:
1. 9 or 109 for Default + milliseconds
2. 13 or 113 for Europian format
3. 21 or 121 for ODBC format (that also matches ISO format)
Brian Lockwood
President
LockwoodTech Software
Brian Lockwood
President
ApexSQL - SQL Developer Essentials
August 11, 2002 at 10:16 am
I wrote up our VB solution here:
http://www.lockwoodtech.com/index.asp?PageId=articles/milliseconds&TitleID=support
Brian Lockwood
President
LockwoodTech Software
Brian Lockwood
President
ApexSQL - SQL Developer Essentials
August 12, 2002 at 8:57 am
OK, I know there are more ways to program than programmers but since you are passing a SQL statement anyway, why not just:
select datefield, datepart(ms,datefield) ?
then in Vb you can just concat the MS to the
formatted date?
August 12, 2002 at 9:22 am
quote:
OK, I know there are more ways to program than programmers but since you are passing a SQL statement anyway, why not just:select datefield, datepart(ms,datefield) ?
then in Vb you can just concat the MS to the
formatted date?
I already mentioned 3 formatting functions in SQL above to do this - it's not like doing it on the server never crossed my mind.
But changing the SQL string doesn't fit out app. We don't know the field names or even the table before we query the database - we must take the resultset, sort out the fields by datatype on the client and then process. It is during this process that milliseconds gets dropped.
There might be other uses for this - on existing apps. you would have to find each and every query where this problem occurred, change it and hoped you missed none.
Basically, if you are kicking out apps that have to work on unknown databases you can't hardcode the sql. There are ways to interrogate the table def in sql, then write the call etc etc etc but sometimes it is just better to do it on the client.
In this case doing it on the client also allows you to write a universal function.
Brian Lockwood
President
LockwoodTech Software
Brian Lockwood
President
ApexSQL - SQL Developer Essentials
August 12, 2002 at 12:15 pm
You are correct that the only solution to this is in the SQL. You will never be able to get milliseconds using VB 6(however VB.NET, from what I have read, does have support for milliseconds). I have run into this problem myself on an app that accesses DB2, the only way to get the complete timestamp returned was to format it in the SQL statement to character data.
As far as embedding the SQL, why would you want to? I find it so much easier to just use stored procedures. That way all you have to do is change your connection string and the stored procedure name(in the event you are going to be accessing the same data from a different data base or RDBMS).
August 12, 2002 at 12:26 pm
quote:
You are correct that the only solution to this is in the SQL. You will never be able to get milliseconds using VB 6(however VB.NET, from what I have read, does have support for milliseconds). I have run into this problem myself on an app that accesses DB2, the only way to get the complete timestamp returned was to format it in the SQL statement to character data.As far as embedding the SQL, why would you want to? I find it so much easier to just use stored procedures. That way all you have to do is change your connection string and the stored procedure name(in the event you are going to be accessing the same data from a different data base or RDBMS).
you can do it in VB. see:
http://www.lockwoodtech.com/index.asp?PageId=articles/milliseconds&TitleID=support
It requires some code but it works.
Brian Lockwood
President
LockwoodTech Software
Brian Lockwood
President
ApexSQL - SQL Developer Essentials
August 12, 2002 at 1:21 pm
That looks pretty good. The one issue I have with it, and unfortunately I don't have a lot of time to devote at the moment, would be does it go out to 6 decimal places on the time. I have thrown it into a test app and as it is currently set it only goes out 3. When confronted with 6 it throws something like this in the works:
8/1/2002 12:47:20 AM:558
8/1/2002 1:44:05 AM:895
8/1/2002 2:07:25 AM:096
8/1/2002 2:12:16 AM:548
8/1/2002 4:21:18 AM:069
8/1/2002 6:00:50 AM:931
Basically what I would like to see would be something with an output like "2002-08-01-06.00.50.931871" which is the full output from the last given entry on the list.
August 12, 2002 at 2:03 pm
quote:
That looks pretty good. The one issue I have with it, and unfortunately I don't have a lot of time to devote at the moment, would be does it go out to 6 decimal places on the time. I have thrown it into a test app and as it is currently set it only goes out 3. When confronted with 6 it throws something like this in the works:8/1/2002 12:47:20 AM:558
8/1/2002 1:44:05 AM:895
8/1/2002 2:07:25 AM:096
8/1/2002 2:12:16 AM:548
8/1/2002 4:21:18 AM:069
8/1/2002 6:00:50 AM:931
Basically what I would like to see would be something with an output like "2002-08-01-06.00.50.931871" which is the full output from the last given entry on the list.
I can't help you with six digit milliseconds.
but to clean up the AM/PM thing
' check of the strFormat is missing
If Not IsMissing(strFormat) Then
FormatDateTimeWithMS = Format(dDate, strFormat)
Else
FormatDateTimeWithMS = Format(dDate, "yyyymmdd Hh:Nn:Ss") ' You can add your own format
End If
FormatDateTimeWithMS = FormatDateTimeWithMS & ":" & Format(lMilliseconds, "000000")
End Function
Brian Lockwood
President
LockwoodTech Software
Brian Lockwood
President
ApexSQL - SQL Developer Essentials
August 12, 2002 at 2:28 pm
Why would you want to express millisecond, a three digit quantity, in six digits? The system clock only has a granularity of approximately three milliseconds. The extra digits only represent a round-off error.
Steve Hendricks
MCSD, MCDBA
Data Matrix
shendricks@afsconsulting.com
(949) 588-9800 x15
August 12, 2002 at 2:42 pm
DB2 on the OS/390 - z/OS uses a timestamp that has 6 places behind the second. Some of the data I deal with on this system uses a unique timestamp value and I would like to see the "real" value behind it. I have found the best way to do it it to let the SQL handle a conversion to character data and pass that back through ADO.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply