Help with DATE data type conversion

[John Cosmas]

I've encountered a table that stores dates in Gregorian format so that the
dates and time looks like straight integers that cannot be easily discrened
as a date or time by just looking at it. I'm using SQL to query the table
and retrieve the data as an integer. I'm tyring to figure out how to best
CAST or CONVERT the field into a DATE type so that its readable in the right
format. I'm coding in VB.NET and the use of CDATE and FORMAT have both been
futile. I realize this is a simple question, but I'm kindly lost so I'm
trying not to loose it by spinning my wheels too hard.

John

 

[Katy King]

From: "John Cosmas" <[email protected]>
| I've encountered a table that stores dates in Gregorian format so that the
| dates and time looks like straight integers that cannot be easily
discrened
| as a date or time by just looking at it. I'm using SQL to query the table
| and retrieve the data as an integer. I'm tyring to figure out how to best
| CAST or CONVERT the field into a DATE type so that its readable in the
right
| format. I'm coding in VB.NET and the use of CDATE and FORMAT have both
been
| futile. I realize this is a simple question, but I'm kindly lost so I'm
| trying not to loose it by spinning my wheels too hard.

I'm not sure what you mean by "Gregorian format." Can you give an example
of a date stored this way?
Take a look at DateTime.ParseExact(), if you know what format your date
string is in.

Katy

 

[Otis Mukinfus]

I've encountered a table that stores dates in Gregorian format so that the
dates and time looks like straight integers that cannot be easily discrened
as a date or time by just looking at it. I'm using SQL to query the table
and retrieve the data as an integer. I'm tyring to figure out how to best
CAST or CONVERT the field into a DATE type so that its readable in the right
format. I'm coding in VB.NET and the use of CDATE and FORMAT have both been
futile. I realize this is a simple question, but I'm kindly lost so I'm
trying not to loose it by spinning my wheels too hard.

John

John,

Are you sure about the date format? The last time I saw dates stored as
integers was when I was working with PASCAL back in the 80's. The format was
called Julian, and the integer was the number of days since a certain date,
although I don't recall what the base date was.

Any way, if you have that format you can find the formula for encoding and
decoding it at your pubic library. That's where I found out how to decode it.
nowadays you might be able to find it on the Internet.


Otis Mukinfus
http://www.otismukinfus.com

 

[Cor Ligthert]

John,

The datetime format is a long that exist from ticks. For the SQLserver
datetime there is a different starting point than for the system.datetime.
The first start with the Georgian calendar in 1753 and the other in the year
1. (1753 was the point where the UK (and therefore there colonies) switched
from Julian to Georgian)

With CDate that is converted right so that is not the problem.

When you want the date as value, than you can use something like this
roughly written

\\\
dim dtFromSQL as datetime = CDate(dr("DateTime"))
dim dtLong as long = dtfromSQL.Ticks
///

I hope this helps,

Cor

 

[John Cosmas]

37525, 37526, 37558 are some examples.