0

I have to display records in gridview based on condition - Appointment date = tomorrow date.

i have tried many options but didn't get exact method. I am using datetime for appointment , it also has time. So I could not remove time and compare date alone.

Now using this query in sqldatasource:

SELECT * FROM [Patient_Appointment_Detail] WHERE ([AptTime] = @AptTime)

and in control parameter using:

<asp:ControlParameter ControlID="lblgetdate" DefaultValue="NULL" Name="AptTime" 
                PropertyName="Text" Type="DateTime" />

I am putting tomorrows date to label while loading of page. But could not remove time in AptTime and compare only date.

lblgetdate.Text = DateTime.Now.AddDays(1).ToString("dd/mm/yyyy");

I also get this error:

String was not recognized as a valid DateTime. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.FormatException: String was not recognized as a valid DateTime. Source Error: An unhandled exception was generated during the execution of the current web request

Cyril Gandon
  • 16,830
  • 14
  • 78
  • 122
Sam Simon
  • 43
  • 1
  • 2
  • 9

2 Answers2

0

In SQL Server, you can CAST the DateTime to Date :

SELECT * 
FROM [Patient_Appointment_Detail] 
WHERE CAST([AptTime] AS DATE) = CAST(@AptTime AS DATE)

Reference : MS SQL compare dates?

You have another problem, you are passing a string in the parameter as a DateTime. It is illegal and you got an exception.

Create a control to keep your date, like a Calendar:

<asp:ControlParameter ControlID="myCalendar" DefaultValue="NULL" Name="AptTime" 
            PropertyName="SelectedDate" Type="DateTime" />

myCalendar.SelectedDate = DateTime.Now.AddDays(1);
Community
  • 1
  • 1
Cyril Gandon
  • 16,830
  • 14
  • 78
  • 122
  • but i want query.. inside query how can i get only date from AptTime? – Sam Simon Apr 23 '13 at 09:16
  • tried but got this error: String was not recognized as a valid DateTime. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.FormatException: String was not recognized as a valid DateTime. Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. – Sam Simon Apr 23 '13 at 09:21
  • any problem with my label?? label is showing tomorrows date only. – Sam Simon Apr 23 '13 at 09:22
0

This will do what you want:

SELECT * FROM [Patient_Appointment_Detail] WHERE ([AptTime] = DATEADD(day, 1, CONVERT(DATETIME, @AptTime)))

if you pass in the date as today's date.

For date comparison with tomorrow's date, use:

SELECT * FROM [Patient_Appointment_Detail] WHERE CAST([AptTime] AS DATE) = CAST(CONVERT(DATETIME, @AptTime) AS DATE))
jordanhill123
  • 4,142
  • 2
  • 31
  • 40
  • got same error: String was not recognized as a valid DateTime. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.FormatException: String was not recognized as a valid DateTime. Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. – Sam Simon Apr 23 '13 at 09:26
  • No.i want to pass tomorrows date. So i am taking a label and storing tomorrows date. but couldnot pass label as parameter..gives error. – Sam Simon Apr 23 '13 at 09:31