Ho to store dates in MsSql from vbnet

  • Thread starter Thread starter VBnet News
  • Start date Start date
V

VBnet News

Hi,

When storing a date like 1/12/2008 to msSQL(setting field is "datetime")
The date in msSQLis stored like 1/12/2008 0:00:00

When upload the data in vbnet form my textbox also show 1/12/2008 0:00:00
instead of 1/12/2008


When using (setting field as "nvarchar(20)" is it working OK but a query
order by date does not work correct

I would be apreciated to have help on this
Manny thanks in advance
Marc.
 
Marc,

In Net a dateTime (and a Date) is always stored as a structure with Date and
Time.

To get the Date part you can use DateTime.Date
http://msdn2.microsoft.com/en-us/library/system.datetime.date(VS.80).aspx

(VB2008 style)
\\\
dim a = DirectCast(myDataRow.Item("TheDateField"),DateTime).Date
///

Although you want probably
(before VB2008 style)
\\\
dim a as String =
DirectCast(myDataRow.Item("TheDateField"),DateTime).ToString("dd-MM-yyyy")
(or a representation of the string in your culture)
///

Typed in this message so be aware of typos.

Cor
 
Marc,

In Net a dateTime (and a Date) is always stored as a structure with Date and
Time.

To get the Date part you can use DateTime.Datehttp://msdn2.microsoft.com/en-us/library/system.datetime.date(VS.80)....

(VB2008 style)
\\\
dim a = DirectCast(myDataRow.Item("TheDateField"),DateTime).Date
///

Although you want probably
(before VB2008 style)
\\\
dim a as String =
DirectCast(myDataRow.Item("TheDateField"),DateTime).ToString("dd-MM-yyyy")
(or a representation of the string in your culture)
///

Typed in this message so be aware of typos.

Cor
Click to expand...

Hi Cor,
Do we have to use DirectCast when displaying date? Or you provide a
format conversion?

Thanks.
 
Be aware that VB.NET stores dates differently to SQL Server, so you can lose
precision. I believe .NET stores datetimes to the precision of 100
nanoseconds, while SQL Server "only" has a precision of 1/300th of a second.

The ISO 8601 format used by T-SQL (e.g. WHERE
DateField='2007-06-13T23:45:20.123' ) can also be an issue, as T-SQL only
supports precision down to the millisecond.

Most of the time it won't matter, but it can mean that a date you just wrote
to the database won't match what you have stored in memory under certain
circumstances e.g. if you use the .NET Now() function for datetime stamps.
 
Back
Top