TheGoToGuy’s Blog

SQL Compatible Date With .NET 5 September 2008

I’ve been dealing with SQL Server for quite a while now. I’ve worked with SQL Server 2000, 2005 and more recently 2008. The one annoyance that has persisted throughout is date formats, or the management thereof.

Now granted this isn’t really a SQL problem because SQL does offer numerous ways to input and output dates. It was always more of a programming issue, mostly for input. Say you wanted to enter a date like the 6th of June, 2008. That would look something like this: 06/06/2008. So which is the day and which is the month? This would come down to your setup. This can be a problem when developing for a widely distributed application where the setup and enviroment can vary greatly and they arent under your control.

That’s why I came up with a .NET function that formats a given date into a standard, universal SQL date format – yyyymmdd. So no matter what the setup or enviromental settings are it will always be used correctly.

VB.NET:

 

Public Shared Function FormatSqlCompatibleDate(ByVal WholeDate As DateTime) As String
Dim DateBuffer As String
Dim Day As String = WholeDate.Day.ToString
Dim Month As String = WholeDate.Month.ToString
Dim Year As String = WholeDate.Year.ToString

If Day.Length = 1 Then
Day = “0” + Day
End If

If Month.Length = 1 Then
Month = “0” + Month
End If

DateBuffer = Year + Month + Day 

Return DateBuffer
End Function

C#:

 

public static string FormatSqlCompatibleDate(DateTime WholeDate)
{
string DateBuffer;
string Day = WholeDate.Day.ToString;
string Month = WholeDate.Month.ToString;
string Year = WholeDate.Year.ToString; 

if (Day.Length == 1)
{
Day = “0” + Day;
} 

if (Month.Length == 1)
{
Month = “0” + Month;
}

DateBuffer = Year + Month + Day; 

return DateBuffer;
}

Hope you found this useful.

Take care,
TheGoToGuy