VB/SQL numeric datetime values

Question

VB/SQL numeric datetime values

hrodberaht

SSC-Addicted

Points: 428
More actions
August 14, 2003 at 7:47 am

#84119

I need to insert a date+time value from VB into a datetime field. I do not want to use a formatted date-time string like '2003-08-14', but the corresponding numeric value like 37847, due to regional differnces between servers & clients.
This works fine, but my actual dates for the same numeric date value always differs by two days. For instance: The numeric value 37847 in vb would be '2003-08-14', but when I insert that numeric value into a datetime field by means of t-sql, the date is displayed as '2003-08-16'. I have tested this with my app and sql running on the same machine, and otherwise, but to no avail.
I am using cdbl() in vb to convert my date variable to a numeric value.
Any ideas?
Robert

Robert

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi Robert,

quote:
I need to insert a date+time value from VB into a datetime field. I do not want to use a formatted date-time string like '2003-08-14', but the corresponding numeric value like 37847, due to regional differnces between servers & clients.
This works fine, but my actual dates for the same numeric date value always differs by two days. For instance: The numeric value 37847 in vb would be '2003-08-14', but when I insert that numeric value into a datetime field by means of t-sql, the date is displayed as '2003-08-16'. I have tested this with my app and sql running on the same machine, and otherwise, but to no avail.

try this to see the difference

in QA:

DECLARE @stmt int

SET @stmt = 1

SELECT CAST(@stmt as datetime)

in VB:

MsgBox CDate(1)

it's simply two different starting points

quote:
I am using cdbl() in vb to convert my date variable to a numeric value.

not sure it this can cause rounding issues on the date?

Cheers,

Frank

Edited by - a5xo3z1 on 08/14/2003 08:03:27 AM

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

hrodberaht SSC-Addicted Points: 428 More actions · Answer 2

Thanks,

it seems vb's day 1 start on 1899-12-31, and sql's on 1900-01-02. i've played around with some settings and it seems that this diff. should stay constant.

Robert

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

quote:
it seems vb's day 1 start on 1899-12-31, and sql's on 1900-01-02. i've played around with some settings and it seems that this diff. should stay constant.

it (hopefully) does stay constant.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]