Ho to store dates in MsSql from vbnet

[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.

 

[Cor Ligthert[MVP]]

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

 

[kimiraikkonen]

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

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

Thanks.

 

[VBnet News]

Hi Cor


Thanks for your answer, I get step forward now..

Marc

 

[SurturZ]

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.