-1

I just need to compare a date entered by a user to a DateTime stored in my database. I have tried converting the DateTime to Date like this: 

select CONVERT(date, startdate) from myTable

However, when the information is shown in my GridView, there is still a time attached to the date. (The time shown is 12:00:00.) What can I do to either convert the DateTime to just a Date when the information is pulled from the database, or compare a Date to a DateTime? Why is there still a time shown, even though I converted the DateTime to Date?

**Update

It may be worth noting that I can run the query that populates my GridView in SQL Server Management Studio and the results shown are what I expect-- no time is in the date. I think the problem must be in VB.NET...

**More information

I am having the user fill out a form that will be used to search the database for relevant results. However, as it stands, no results are being returned because the user enters a normal date in the Date field on the form, while the field in the database is a DateTime. This is why I need to know how to convert it.

**Requested Information

Here is the query I'm using:

select convert(date, startdate) as startdate, CONVERT(date, completedate) as completedate
from myTable

Here is the result set in SQL Server Management Studio (no times):

enter image description here

Here is the result set in the grid (shows times):

enter image description here

I obviously omitted information from the database, but the information shown is all I'm worried about. I had to omit confidential information.

ic3man7019
  • 721
  • 6
  • 24
  • Would it be sufficient to adjust the rendered appearance of the date? [How to change the format of a column in GridView from c#](http://stackoverflow.com/q/10681378/1115360) – Andrew Morton Dec 06 '16 at 18:51
  • @AndrewMorton I have updated the question. – ic3man7019 Dec 06 '16 at 18:55
  • So, to be clear, the `Date` comes back from the database without a time, yet the grid is populated *with* the time, correct? You want to know how to remove the time? – ragerory Dec 06 '16 at 19:03
  • @ragerory That is correct. I need the search to return results based on just the date, not the time. – ic3man7019 Dec 06 '16 at 19:04
  • The .net framework doesn't have just an intrinsic date type. The `Date` type in VB is syntactically the same as the `system.datetime` type (similar to how `Integer` is the same as `system.int32`). – Martin Soles Dec 06 '16 at 19:05
  • Please show us a small sample of data from the database and the query you are using. – Andrew Morton Dec 06 '16 at 19:06
  • @AndrewMorton I have updated the question. – ic3man7019 Dec 06 '16 at 19:20
  • 1
    This is a presentation layer format issue and nothing to do with your database. Using gridview you can specify a format string for the date. You could use a format string like 'yyyy-MM-dd' which would then not display the time portion. Something like `DataFormatString="{0:yyyy-MM-dd}"`. If you want more a more specific pointer please post your grid code where you bind the date column. – Igor Dec 06 '16 at 19:35
  • 1
    @ic3man7019 I was hoping that you would show the query used to select a particular date based on the user input - could you show that to us please? And the VB code that you use to create the query. That seems to be where the problem is. – Andrew Morton Dec 06 '16 at 20:03

1 Answers1

0

It returns it as a DateTime in .NET which automatically adds the time. Just do the conversion in VB.NET when you do your display. For instance if you're using MVC and you're passing up your viewmodel, in your Razor syntax you'd do vm.DateRetrieved.ToString("d")

https://msdn.microsoft.com/en-us/library/system.datetime.date(v=vs.110).aspx

Dim dateTimeStamp = DateTime.Now  
Console.WriteLine(dateTimeStamp.ToString("d")) 

'12/6/2016 14:22:28PM
'12/6/2016
ragerory
  • 1,360
  • 1
  • 8
  • 27