CODEWITHSUNDEEP
c#sqldatetime
user685590
user685590

Reputation: 2564

SQL Query with Long Date Time?

I am trying to pass a SQL query through c# , currently it is based on just the day month and year with time always set to 0 EG 1997-10-28 00.00.00.000 which is great as the time never changes it easy for me to just Select Where date equals the calendar date.

However with the Start Field , the time is different on each record , eg 1899-12-30 14.14:00.00.000 , 1899-12-30 15.14:30.00.000 . (Seconds downwards are always the same) .

So I need to have a query that will return all the results of the selected date on the "Start" field . How would I do this?.

E.G IF i click the calendar which passes 1997-10-28 00.00.00.000 , I would like the results of every time in that day!. How do I go about that?.

Thanks for any input.

EDIT: Is there a way to format the date that i have in SQL ?. This comes from an old access database!. and as you can see above it is 1899-12-30 ?. not 1998 , I don't know why this has happened!.

Upvotes: 3

Views: 961

Answers (4)

Bob Vale
Bob Vale

Reputation: 18474

This SQL DATEADD(d,datediff(d,0,startdate),startdate) will convert a date with time to just the date;

eg

Select field1,field2 from mytable where DATEADD(d,datediff(d,0,startdate),startdate)='1997-10-28'

Upvotes: 0

musefan
musefan

Reputation: 48425

Can you not just query the date part. I think this should work...

SELECT * FROM Table1 WHERE StartDate = '1997-10-28'

EDIT: Above may not work but following should cover needs

SELECT * FROM Table1 WHERE StartDate >= '1997-10-28 00:00:00.000' AND StartDate < '1997-10-29 00:00:00.000'

(notice the second date is the following date)

Upvotes: 0

LukeH
LukeH

Reputation: 269478

WHERE DATEDIFF(dd, your_start_field, @your_param) = 0

Upvotes: 3

Leons
Leons

Reputation: 2674

You need to select all record between today and tomorrow without including tomorrow's date.

WHERE EventDate >= StartDate AND EventDate < DATEADD(d, 1, StartDate)

Upvotes: 3

Related Questions