How to select items with respect to date only in sqlite?

Question

Hello Everyone, 

I have to select all rows from database by just passing only date. For example to get all rows that I am passing date only this format: 11/9/2016

In sqlite db I store this in DATETIME format column: Eg: 11/9/2016 6:44:46 PM

 I have tried to get by using date() but I get nothing for date:

 

sql = "SELECT * FROM Tble_UserSetUp inner join Tble_Login on Tble_UserSetUp.Id = Tble_Login.UserId WHERE Tble_Login.UserId='" + UserID + "' and Tble_Login.LogInTime = date('" + AccessDate + "') or Tble_Login.LogOutTime = date('" + AccessDate + "')";

So all I need is to compare only dates but can't find how to do this in sqlite. But it return null data.

 

 

Answer 1

Improve this code by using switch cases OR if-else conditions.... then u will get your desire output ...

My code is working and running without error,,,, now you need to improve this only according your need.

 If you are not able to improve then tell me ...... i will help you 

Answer 2

Hai Mohammad ,

 

Your program result shows only the first 2 digit value. I don't want this . I can just separate four digit value as digit . Exampe 1120=11:20 and also separate the three digit value as 120=01:20.

 

Are you clear now. Please leave above conversation. This is the exact question to solve .

 

Thank you for your kind co-operation. 

Answer 3

Hai Gowtham ,

I am already used this code , but am not get the correct answer. 

Answer 4

Now use this code tested and working properly ... this is implemented by me

 

string FirstTotalHrs = string.Empty;

string total = "1200";

string hour1= total.Substring(0,2);

string hour1minutes = total.Substring(2,2);

TimeSpan tshour1 = new TimeSpan(Convert.ToInt32(hour1), Convert.ToInt32(hour1minutes), 00);

FirstTotalHrs = Math.Floor(tshour1.TotalHours) + ":" + (tshour1.Minutes < 10 ? "0" + tshour1.Minutes : tshour1.Minutes.ToString());

Response.Write("FirstTotalHrs: " + FirstTotalHrs + "<br/>");

 

Answer 5

int i = dataGridView1.CurrentCell.RowIndex;

string s=dataGridView1.Rows[i].Cells[0].Value.ToString() ;

dataGridView1.Rows[i].Cells[1].Value =s.Substring(0,2) +":"+"00";

 

Answer 6

private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e) { string input = dataGridView1.CurrentRow.Cells[0].Value.ToString(); if (e.ColumnIndex == 1) { dataGridView1.CurrentRow.Cells[1].Value = input.Substring(0, 2) + ":" + input.Substring(2, 2); } }

Answer 7

Hi, Please check debug mode 

 

string input =dataGridView1.CurrentRow.Cells[0].Value.ToString();

 

here dataGridView1.CurrentRow.Cells[0].Value.ToString().

 

Index 0 values not existing in datagridview cells array please check

 

 

Answer 8

Hai,

 

private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)

{

string input = dataGridView1.CurrentRow.Cells[0].Value.ToString();

if (e.ColumnIndex == 1)

{

dataGridView1.CurrentRow.Cells[1].Value = input.Substring(0, 2) + ":" + input.Substring(2, 2);

}

}

 

This is my code  . In that first line only show that error.

Answer 9

Please share your query..What u have tried?  Please check query contains record are exists or not? If yes show me query out put here.

Answer 10

Hai Raja,

 

I am using your query , But it was showing this error : object reference not set to an instance of an error. But it will separate the strings .

 

Thanks. 

Answer 11

Use one pf them functions

TimeSpan.TryParseExact()

      OR

TimeSpan.ParseExact()

 

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

Answer 12

first question solution check below

 

string a, b;

a = "1100".Substring(0, 2);

b = "1200".Substring(0, 2);

if (Convert.ToInt32(a) > 12)

a= a + ":" + "00" + " PM";

else

a = a + ":" + "00" + " AM";

if (Convert.ToInt32(b) > 12)

b = b+ ":" + "00" + " PM";

else

b = b + ":" + "00" + " AM";

messagebox.show(a+"-"+b) ;

Answer 13

Hello Pavithra,

 

 

string input = "1100";  

string sub = input.Substring(0,2)+":"+input.Substring(2,2); 

Console.WriteLine("Substring: "+ sub)

more details checkout below url

http://www.dotnetperls.com/substring

Answer 14

string num = "1110";

            string result = num.Substring(0, 2) + ":" + "00";

Answer 15

Hai Ravi,

 

This is also not working, Ok leave all these things , Now I just want to separate the string ex(1110)  like this will be separate 11:00 like this. This is the concept now.

 

Is there any idea to solve this please share your code.

 

Thank you for your kind co-operation.

 

 

 

Answer 16

soryy 

 

try this

 

if(convert.toInt32(convert.tostring(time).substring(0,1))>12)

convert.tostring(time).substring(0,1) +":" +convert.tostring(time).substring(2,3) +"PM";

else

convert.tostring(time).substring(0,1) +":" +convert.tostring(time).substring(2,3) +"AM";

 

Answer 17

Hai Ravi, 

 

It was show the error is Invalid expression term 'if'.

 

How to overcome this and time variable is not accepted. 

Answer 18

Hai Mohammad,

 

Can you understand my qusetion . I need to enter the hour and minute in the datagridview cell  and it will automatically separate the time. and also show the AM and PM format. How can I do this please tell me any suggestion.

 

Need to finish this topic quickly. 

Answer 19

if(convert.toInt32(convert.tostring(time).substring(0,1))>12)

if(convert.tostring(time).substring(0,1) +":" +if(convert.tostring(time).substring(2,3) +"PM";

else

if(convert.tostring(time).substring(0,1) +":" +if(convert.tostring(time).substring(2,3) +"AM";

 

try again this

what error your getting 

Answer 20

Hi

 

I hope it will help :

   

      TimeSpan span = TimeSpan.FromHours(16); 

      DateTime time = DateTime.Today + span; 

      String result = time.ToString("hh:mm tt");

      OUTPUT: 04:00 PM

 See here time formats

https://msdn.microsoft.com/en-us/library/az4se3k1.aspx

Demo Link:

   http://ideone.com/veJ6tT 

Answer 21

Hai,

 

It is not working again it was showing an error. Is there any other possibilities to overcome this.

 

Answer 22

if(convert.tostring(time).substring(0,1)>12)

if(convert.tostring(time).substring(0,1) +":" +if(convert.tostring(time).substring(2,3) +"PM";

else

if(convert.tostring(time).substring(0,1) +":" +if(convert.tostring(time).substring(2,3) +"AM";

Answer 23

Hai Ravi,

 

Error 14 'System.DateTime' does not contain a definition for 'substring' and no extension method 'substring' accepting a first argument of type 'System.DateTime' could be found (are you missing a using directive or an assembly reference?)

 

Error 15 Only assignment, call, increment, decrement, and new object expressions can be used as a statement 

 

it was show n this error.

 

How to reduce this. 

Answer 24

if(time.substring(0,1)>12) 

time.substring(0,1) +":" +time.substring(2,3) +"PM";

else

time.substring(0,1) +":" +time.substring(2,3) +"AM";