How to search SQL Server database using C#

Question

I have a SQL Server table Registration with name and datetime, I want to search the data with datetime which is less then datetime.now.

SqlDataAdapter cs = 
    new SqlDataAdapter("Select * from Registration where Date > DateTime.now")

I want that datetime is less then today.

You need to learn ADO.Net, What have you tried? where have you looked? Here is the msdn page :- msdn.microsoft.com/en-us/library/aa577376(v=bts.20).aspx — Derek
– Derek, Commented Feb 22, 2013 at 15:56
You need a SqlCommand with parameters : msdn.microsoft.com/en-us/library/… — Steve B
– Steve B, Commented Feb 22, 2013 at 15:57
She does not need to use SqlCommand with parameters, she can do it directly in the constructor for the SqlDataAdapter. — Halvard
– Halvard, Commented Feb 22, 2013 at 16:00

lc. · Accepted Answer · 2013-02-22 15:56:57Z

4

The T-SQL built-in function getdate() returns the current server date/time.

Therefore your query should be, SELECT * FROM Registration WHERE [Date] < getdate().

Using this in your code would be:

SqlDataAdapter cs = new SqlDataAdapter("Select * from Registration where [Date] < getdate()")

answered Feb 22, 2013 at 15:56

lc.

117k21 gold badges163 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Iswanto San Over a year ago

the question say less then datetime.now

lc. Over a year ago

@IswantoSan Yeah, bad copy-paste. Thanks

Saima Maheen Over a year ago

But Dear Table Columen DataType is DateTime not only date??

lc. Over a year ago

@SaimaMaheen getdate() contains the date and time. Look at the linked documentation.

Saima Maheen Over a year ago

Dear But if i want manual enter date in textbox or selection date from monthcalender1 what is code?

Curtis · Accepted Answer · 2013-02-22 15:56:55Z

3

You can use SQL GETDATE() to do this:

SqlDataAdapter cs = new SqlDataAdapter("Select * from Registration where Date < GETDATE()")

answered Feb 22, 2013 at 15:56

Curtis

104k69 gold badges280 silver badges359 bronze badges

2 Comments

Saima Maheen Over a year ago

But Dear Table Columen DataType is DateTime not only date??

Saima Maheen Over a year ago

Dear But if i want manual enter date in textbox or selection date from monthcalender1 what is code?

Iswanto San · Accepted Answer · 2013-02-22 15:58:27Z

2

Use GETDATE() to get current date in your query.

SqlDataAdapter cs = new SqlDataAdapter("Select * from Registration where Date < GETDATE()");

answered Feb 22, 2013 at 15:58

Iswanto San

18.6k13 gold badges60 silver badges79 bronze badges

1 Comment

Saima Maheen Over a year ago

But Dear Table Columen DataType is DateTime not only date??

zax0978 · Accepted Answer · 2013-02-22 16:16:18Z

1

This may work... if you want to use .NET DateTime, that will be the date of the local system.

     SqlDataAdapter cs = new SqlDataAdapter(
           "SELECT * FROM [Registration] 
            WHERE [Date] < '" + DateTime.Now.ToShortDateString() +"';");

     //output: SELECT * FROM [Registration] WHERE [Date] < '2/22/2013';

It is going to depend upon the data type of the [date] column, if it is string or datetime or something else. If it is YYYYmmdd, then you can manipluate the DateTime.Now output to match.

answered Feb 22, 2013 at 16:16

zax0978

588 bronze badges

2 Comments

Saima Maheen Over a year ago

Dear But if i want manual enter date in textbox or selection date from monthcalender1 what is code?

zax0978 Over a year ago

if using a text box, collect and DateTime.TryParse() the entered text. if you are using a calendar control, it should have a means to export the date in whatever format you need it in. Place the data-values in the string in place of 'DateTime.Now.ToShortDateString()'

Pratik Mehta · Accepted Answer · 2013-02-22 16:21:48Z

1

Its very simple..

if you want to use sql current datetime use SELECT * FROM Registration WHERE [Date] < getdate()

and if you want your application current datetime use

"SELECT * FROM Registration WHERE [Date] < '+Datetime.Now.Tostring() +'"

Also try to use parameterized query if you want second option.

answered Feb 22, 2013 at 16:21

Pratik Mehta

1,3724 gold badges17 silver badges37 bronze badges

Collectives™ on Stack Overflow

How to search SQL Server database using C#

5 Answers 5

5 Comments

2 Comments

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

2 Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related