Help on query

Question

Hi All,

   I have a column with the datatype datetime and it is storing the values as 8/2/2012 9:02:25 AM(example).

  When  i tried to read the data from frontend(ASP.NET) i am unable to find the record.

  I am able to find the record if the value is like this 08/02/2012 9:02:25(ex).

  I tried by including the condition as CONVERT(CHAR(11),Entrydate,101) + CONVERT(CHAR,Entrydate,114)= in select statement, which  is resulting the properoutput. When i am trying to access the data from frontend i am unable to get the data.

 Please let me know how i can solve it.

Waiting for valuable replies

Answer 1

You can show your complete script for that, here.

---

Many Thanks & Best Regards, Hua Min

Answer 2

Hi Chen,

   Please see the procedure below.

CREATE PROCEDURE getPatientComplaint
(
@Patientname varchar(100),
@eod datetime
)
AS

BEGIN

SET NOCOUNT ON;

SELECT ComaplintID FROM Patientchiefcomplaint WHERE Patientname=@Patientname and CONVERT(CHAR(11),Entrydate,101)
     + CONVERT(CHAR(8),Entrydate,114)=@eod

END

Answer 3

Try

CREATE PROCEDURE getPatientComplaint

(
@Patientname varchar(100),
@eod datetime
)
AS

BEGIN

SET NOCOUNT ON;

SELECT ComaplintID FROM Patientchiefcomplaint WHERE Patientname=@Patientname and CONVERT(CHAR(11),Entrydate,101)
     + CONVERT(CHAR(8),Entrydate,114)=CONVERT(CHAR(11),@eod,101)
     + CONVERT(CHAR(8),@eod,114)

END

Or simply try

CREATE PROCEDURE getPatientComplaint
(
@Patientname varchar(100),
@eod datetime
)
AS

BEGIN

SET NOCOUNT ON;

SELECT ComaplintID FROM Patientchiefcomplaint WHERE Patientname=@Patientname and Entrydate=@eod

END

---

Many Thanks & Best Regards, Hua Min

Answer 4

Hi Chen,

     Initially i tried with

SELECT ComaplintID FROM Patientchiefcomplaint WHERE Patientname=@Patientname and Entrydate=@eod this one only, but i couldn't get  the result later i added convert clause but no luck. I tried with your statement but no output. Can you suggest another one plz.

Answer 5

Can you check what is being passed for @eod variable (by running SELECT @eod statement) - a different format in date or date with milli seconds might not work for you as you are trying to equate it with your date column (which has no milli seconds in it).

Thanks!

Answer 6

What value is this - "CONVERT(CHAR(11),@eod,101)
     + CONVERT(CHAR(8),@eod,114)"?

You can also do a check there to directly run this in Management studio, to see the output of this?

SELECT ComaplintID FROM Patientchiefcomplaint WHERE Patientname=@Patientname and CONVERT(CHAR(11),Entrydate,101)
     + CONVERT(CHAR(8),Entrydate,114)='08/02/2012 9:02:25';

---

Many Thanks & Best Regards, Hua Min

Answer 7

Hi Deepak,

   Thank you for your valuable information.

Answer 8

Hi Chen,

   It worked and thank you for suggestion.