SQL query in .NET with DATETIME issues

[SenileOwl]

I'm working in vb.net. I'm trying to create a query for a table adapter that will filter data. The query is

SELECT CommitteeId, CommitteePostion FROM MemberIndexApp WHERE ((UserName = @UserName) AND (LoginDate = @LoginDate))

The table lies in a SQL Server and the LoginDate's type is DATETIME.
Right now, the query is returning nothing. I'm fairly certain the problem lies in the DATETIME format surrounding the LoginDate. The parameter @LoginDate starts out as a string, and I've tried converting it to datetime with CONVERT(DATETIME, @LoginDate, 102) and CONVERT(DATETIME, @LoginDate). I've also tried other formats beside 102, but nothing seems to work.

How can I get LoginDate and @LoginDate into the same format? The format must contain the date and the time (including seconds).

Thanks in advance.

[Plater]

In your current situation, if your datetime variable is in the string format, surrounding it with ' will implictly cast it as a datetime

SELECT CommitteeId, CommitteePostion FROM MemberIndexApp WHERE ((UserName = @UserName) AND (LoginDate = '@LoginDate'))
 

Another alternative would be to use the parameter objects and set the type to DateTime then give it a .NET DateTime object.

[shweta123]

Hi,

To remove the error you have to convert both the dates into same format . You can do this way :
In your vb .net code format LoginDate value as following :

e.g.

  LoginDate = Format(LoginDate,"MM/dd/yyyy")

You have to make change into sql query as follows :

SELECT CommitteeId, CommitteePostion FROM MemberIndexApp WHERE ((UserName = @UserName) AND
(Convert(varchar,LoginDate,101) = @LoginDate))

Hope this helps.

[SenileOwl]

Thank you for your replies.

Unfortunately, I keep getting the same thing. I tried to convert to varchar, but the table was still blank. I tried the format, but it wasn't' "a recognized function." I also tried to put the @LoginDate in '' but it quit recognizing it as a parameter. I also tried changing the parameter type to DateTime, but I still received a blank table.



I tried < and >in place if the = just to see what it would happen.

SELECT CommitteeId, CommitteePostion, LoginDate, MemberId, Note, TermEnd, TermStart, TypeOfMember, UserName, WhoAppointed FROM MemberIndexApp WHERE ((UserName = @UserName) AND (LoginDate  >  @LoginDate))

The program believes the parameter is smaller than the column field. Which implies that the data in the table somehow has extra data. Doesn't it? Either seconds, where the parameter has none or else 0's added on. I'm nearly positive the parameter has seconds. At least it has them when I right before it gets sent to the SQL statement.

I think I might have to create just make sure the column is between the parameter and the parameter plus one second. However, I really don't want to do this, unless I have to. Is there anyway to get the program to recognize they have the same data?

[Plater]

All DateTimes have seconds, whether they show up or not (they could just always be set to zero) depends on the printing format.
I am not sure what you are doing differently then the rest of us?

//where 'sc' is my SqlConnection object
//and 'SDate' is an instance of DateTime
SqlCommand com = new SqlCommand("SELECT * FROM myTable WHERE myDateColumn >= @StartDate", sc);
com.Parameters.Add("@StartDate", SqlDbType.DateTime);
com.Parameters["@StartDate"].Value = SDate;
 

[SenileOwl]

Alas, I know what I'm doing different than everyone else, but I'm not sure why that is affecting everything. I'm also not willing to change my ways yet.

You see, I learned to program from a "Teach yourself VB.NET programming" book. Unfortunately the book taught me that to connect to the database, I had to let .NET do it for me. So unlike the rest of the true programmers out there, I don't know how to create my connection in code. Although I plan to rectify this soon, I'm not ready yet to change my program this late in the game. Thus, my SQL select statement is not in my code. It is in a query I'm adding to my table adapter using the add query wizard in the dataset designer page.

So having gotten that embarrassing confession off my chest. There are a few things that I can see different. 1. in your example you were using a greater than or equal to. I'm just using an equal to. If I use a greater than or equal to I can capture the data I want, but I'm also grabbing some extra, that I don't want. 2. I set the DBtype property of my parameter to DateTime. It doesn't say SQLdbtype like yours did. Maybe this has something to do with it.

I have played with my code and I added a second on to my parameter LoginDate. I then selected only the rows where the LoginDate was between the old Parameter and the new Parameter (+ second) This seems to work just fine. If I can't solve the problem, I know I can at least make my code work. Yeah!

[Plater]

You see, I learned to program from a "Teach yourself VB.NET programming" book. Unfortunately the book taught me that to connect to the database, I had to let .NET do it for me.

Ick, yet another reason I dislike VB, everything is geared around drag/drop, no focus on how it works behind the scenes.

[shweta123]

Hi,

If you dont want to consider time part of the date while comparing the 2 dates
you can use format 101 or 103 as these 2 formats are standard and also they do not have time in their format.

Also ,
If you are not getting records by query through vb .Net code then one way is that you goto query Analyzer and execute the same query there. It is possible that Sql statement is right and there is some other error in the code.

[SenileOwl]

Alas I don't have access to the SQL Server to use the Query Analyzer to check my query language (I assume that was the Query Analyzer you were referring to) The closest I can come to it, is calling the tables into Access and then querying it there. Unfortunately I can not use the same functions (i.e.CONVERT) as I've used in vb.net. Is this normal? Have I missed something really important?

As for using 101 and 103. I've tried them both, but maybe I've used them incorrectly. I tried using

(LoginDate =  CONVERT(DATETIME, @LoginDate, 103))

and

(CONVERT(DATETIME, LoginDate, 101) =  CONVERT(DATETIME, @LoginDate, 101)))

both give me an empty table. However I can get

(LoginDate > @LoginDate) AND (LoginDate <  @LoginDate2)

to work, where the second login date is one second more than the first.

I need to know the time for my program. But I'm curious to see if I can get it to work with just a date. So far nothing has worked.

[shweta123]

Hi,

Are you using SQL Server for database ?
Please read this Article
for datetime handling with sql.

[SenileOwl]

yes I am using SQL. I've now decided to bag it. I cannot find the cause of the difference. So I'm using the plus one second and minus one second. Seems to work great and I'll try to solve the other problem later.