Date and Time in VB .NET and SQL Server
Whether you're a seasoned developer or just starting out, handling date and time data correctly is crucial in business applications. In this guide, we explore how to work with temporal data using both VB .NET and SQL Server so that your applications run smoothly and reliably.
VB.NET DateTime Basics
VB.NET offers a robust DateTime structure that simplifies working with dates and times. For example:
' Get the current time
Dim currentTime As DateTime = DateTime.Now
' Create a specific date
Dim specificDate As DateTime = New DateTime(2024, 12, 31)
' Format the current time as a string
Dim formatted As String = currentTime.ToString("yyyy-MM-dd HH:mm:ss")
Key properties and methods include: DateTime.Now, DateTime.UtcNow, DateTime.Today, plus arithmetic methods like AddDays() and custom formatting via ToString("format").
SQL Server Date and Time Types
SQL Server supports several data types to handle date and time values:
| Type | Description |
|---|---|
DATE |
Stores only the date. |
TIME |
Stores only the time. |
DATETIME |
Accurate to 1/300th of a second. |
SMALLDATETIME |
Accurate to the minute. |
DATETIME2 |
A more precise version of DATETIME. |
DATETIMEOFFSET |
Includes time zone awareness. |
For example, you could create a table as follows:
CREATE TABLE Events (
EventID INT PRIMARY KEY,
EventDate DATETIME2,
CreatedAt DATETIME DEFAULT GETDATE()
)
SQL Server functions such as GETDATE(), GETUTCDATE(), and SYSDATETIME() are essential for managing date and time data.
Passing Dates Between VB.NET and SQL Server
When sending dates from your VB.NET application to SQL Server, use the SqlParameter object. This ensures proper date conversion and guards against SQL injection:
Dim cmd As New SqlCommand("INSERT INTO Events (EventDate) VALUES (@EventDate)", conn)
cmd.Parameters.Add("@EventDate", SqlDbType.DateTime).Value = DateTimePicker1.Value
cmd.ExecuteNonQuery()
Common .NET Date & Time Format Strings
| Format | Example | Meaning |
|---|---|---|
d |
4/17/2025 | Short date |
D |
Thursday, April 17, 2025 | Long date |
f |
Thursday, April 17, 2025 2:00 PM | Full date and time |
yyyy-MM-dd |
2025-04-17 | ISO format |
Best Practices
- Use
DateTimeOffsetin SQL Server when time zone data is relevant. - Standardize formatting with
ToString("yyyy-MM-dd HH:mm:ss")for consistency. - Validate date inputs both on the client and server sides.
- Avoid hard-coded, culture-specific formats where possible.
Summary
Understanding and applying the correct date and time methods in VB.NET and SQL Server is key to building reliable applications. By using the appropriate data types, formatting, and security practices, you can manage temporal data effectively.
♥ Here are some online Visual Basic lessons and courses:
