How do I store just a date in MS SQL from VB?

Question

My project requires I use VB (5 or 6)* to store a date in an SQL Server database. The SQL datetime type includes the time, which I don't want. I'm also aware that VB's representation of a date doesn't mirror that of SQL Server.

So, how can I store a date held in VB's date type in the SQL database, as the datetime at midnight on that date, for example?

Edit: I need to use the date to select rows further down the line, so I can't get away with just truncating it on read.

*I know, I you were me, you wouldn't start from here. But given the constraints, any VB6/MS SQL fiends out there?

Answer 1

Use the DateTime column and just truncate the time at the presentation level.

Answer 2

VB6 has a DateValue() function which returns the date portion of a Date/Time value, with the time portion "zeroed out". (Note: When the time portion of a date/time variable is "zeroed out", the time would be interpreted as 12:00 AM.)

Answer 3

Try inserting and updating the date like this:

CAST(FLOOR(CAST(@DateTime AS float)) AS datetime)

We have this in a UDF and it basically strips the time part from a datetime.

Answer 4

SQL Server 2008 has new date and time data types. There is the "Date" data type if you don't want to store the time component.

Answer 5

In VB you can use the Date() function to return the current date with no time element.

If you an use an ADO Parameter object with a Command object then the OLE DB provider should handle the conversion of a VB Date type to the SQL Server DATETIME value.

In SQL Server (pre SQL 2008 DATE type) you should create a CHECK constraint on the column to ensure it is not possible to add a date with a time element (note I've used an unambiguous language 'safe' format for my DATETIME literals) e.g.

ALTER TABLE MyTable ADD
   vb_date DATETIME NOT NULL
   CONSTRAINT vb_date__no_time_element
      CHECK ((vb_date = DATEADD(DAY, DATEDIFF(DAY, '1990-01-01T00:00:00.000', vb_date), '1990-01-01T00:00:00.000')));

Answer 6

I would just use DateSerial to create the date you need. You pass it a year, month and day and it gives you a date with midnight as the time. You can then use it to pass as a parameter to an ADO command or similar. When you read it, it will have midnight so that isn't a problem. I like it better than DateValue as there is no string conversion. If you really want you can create your own function like DateValue that uses DateSerial.

Function JustTheDatePlease(ByVal dtSource As Date) As Date

   JustTheDatePlease = DateSerial(Year(dtSource), Month(dtSource), Day(dtSource))

End Function

If for some reason you aren't using parameterized queries, and you really should have a good excuse for this, you can use the ODBC canonical form of a date in your queries. You just format the date as {d 'yyyy-mm-dd'} for example {d '2009-04-06'}.