SQL Server Date Data Type

[Papa_Don]

Group,

I'm thinking I may want to use the Date data type in SQL Server. However I'm still very unclear how to read, write and compare using this kind of data type. I realize this has a time component which is of no importance right now (it will be later though). Can someone help me understand how to use this? Here are some of my question:

1) is there a way to create a "null" or blank date?
2) Visual Basic has the Today.Date commands. I'll need to compare a date created by the user to what will be in the date field of the data table (formated as a date data type). How is this done?
3) If I have the user enter the date manually, in what format does it need to be in? yyyymmdd?

Feel free to elaborate. I clearly don't understand enough about this kind of data type to really ask good questions.

Thanks,

Don

[Jx_Man]

1.Read this
2. You can compare date function using DateTime.Compare(date1, date2)
Ex :

    Dim date1 As Date = #08/01/2009 12:00AM#
    Dim date2 As Date = #08/01/2009 12:00PM#
    Dim result As Integer = DateTime.Compare(date1, date2)
    Dim relationship As String 

    If result < 0 Then
       relationship = "is earlier than" 
    ElseIf result = 0 Then
       relationship = "is the same time as"          
    Else
       relationship = "is later than" 
    End If

    Console.WriteLine("{0} {1} {2}", date1, relationship, date2)
    ' The example displays the following output: 
    '    8/1/2009 12:00:00 AM is earlier than 8/1/2009 12:00:00 PM

More info : MSDN and this article

3.It's depend on your region. By default, the date format for SQL server is in U.S. date format MM/DD/YY.
Read this

[Papa_Don]

JX_Man,

I ran your code. It worked, but more importantly, it explained a lot. I'm going to use this.

The next question is: If I want to leave the date field blank, would that be a "NULL", or is there another entry that should be used?

Also, since this is entered into a textbox, is there a date conversion that is required to write this to the data table?

Thanks for the help!!

Don

[Jx_Man]

If I want to leave the date field blank, would that be a "NULL", or is there another entry that should be used?

You can use SqlDateTime.Null.
Read this : http://msdn.microsoft.com/en-us/library/system.data.sqltypes.sqldatetime.null%28v=vs.80%29.aspx

Dim DateVal As SqlDateTime
DateVal = SqlDateTime.Null
cmd.Parameters("@Date").Value = DateVal

Also, since this is entered into a textbox, is there a date conversion that is required to write this to the data table?

cmd.Parameters("@Date").Value = DateTime.Parse(txtDate.Text)

[Papa_Don]

Kudo's, Jx_Man! Thanks for this. It's perfect and exactly what I needed.

You're the man!