Date Problem

In our database we have dates in the format:

05/02/2007 10:39:32
05/02/2007 12:33:22
06/02/2007 20:19:32
07/02/2007 13:33:32
08/02/2007 17:35:42

We are using a .net calendar from visual studio to allow users select a date. The date is returned in the format 05/02/2007. No time is included.

We wish to select all data from, for instance the 05/02/2007 from our MSSQL server. We have a query to select based on a date in this format but it does not work.

SELECT Time
FROM DB
WHERE (Time = '05/02/2007');

We get no responses in query analyser for this and incorrect ones when we use parenthesis.

you will need to account for the time component of your dates when writing your queries. Date selections are virtually always done as a range selection

for example:

SELECT TimeFROM DBWHERE (Time >='05/02/2007')AND (Time <'05/03/2007') ;

|||

Hi,

I ran this query

SELECT

StartTime, EndTime

FROM

aspnet_SortedTrip

WHERE

(device='353863842373')AND(StartTime>'2007-02-01'And StartTime<'2007-02-08')

This is the output

2007-02-01 20:32:03.000 2007-02-01 21:51:00.000
2007-02-03 12:33:56.000 2007-02-03 14:05:00.000
2007-02-05 10:46:01.000 2007-02-05 10:50:33.000
2007-02-05 11:45:35.000 2007-02-05 11:55:34.000
2007-02-06 11:39:41.000 2007-02-06 11:56:45.000
2007-02-06 20:00:29.000 2007-02-06 21:05:18.000
2007-02-07 19:25:34.000 2007-02-07 19:33:34.000

Should it retrieve data for the 1st of March even though i had StartTime > '2007-02-01'?

Is that correct?

|||

I do not see any values for the 1st of March in your output. ?

The output appears to be valid - you have records ranging from 02/01/2007 through the end of the daya on 02/07/2007 (mm/DD/yyyy)

Note:, you sould use a >= instead of the > .
If a record was added at exaclty midnight on the morning of 02/07/2007, your query would miss it.

WHERE(device ='353863842373')AND (StartTime >='2007-02-01'And StartTime <'2007-02-08')

|||

Re: Date Problem

ReplyQuoteFavoritesContact

Hi,

I ran this query

SELECT

StartTime, EndTime

FROM

aspnet_SortedTrip

WHERE

(device='353863842373')AND(StartTime>'2007-02-01'And StartTime<'2007-02-08')

This is the output

2007-02-01 20:32:03.000 2007-02-01 21:51:00.000
2007-02-03 12:33:56.000 2007-02-03 14:05:00.000
2007-02-05 10:46:01.000 2007-02-05 10:50:33.000
2007-02-05 11:45:35.000 2007-02-05 11:55:34.000
2007-02-06 11:39:41.000 2007-02-06 11:56:45.000
2007-02-06 20:00:29.000 2007-02-06 21:05:18.000
2007-02-07 19:25:34.000 2007-02-07 19:33:34.000

The format of the above output is yyyy-mm-dd followed by time.

This is the structure of the data in the table. Is this correct?

So that why i asked should it retireve data for the 1st of March even thoough i ran the query as Time > 1st of March and Time < 8th of March

|||

Does any body know how i can increment a Date Value?

Im retrieving a date value from a button click on a calender.

Id like to be able to increment the date by 1 day.

Is this possible to do?

Should the date be in a datetime format in order to increment the day value?

|||

So that why i asked should it retireve data for the 1st of March even thoough i ran the query as Time > 1st of March and Time < 8th of March

your query was from the 1st of February through the 8th of February...not March

|||

Sorry i only realised that now.

But should it be retriving data from the 1st of February even though the query states > 1st of Feb?

Is it possible to increment the day value in date

Example:

1st of Feb incremented to the 2nd of Feb

|||

There are two important date functions you can play with: DateAdd and DateDiff.

To add one day, try this:

SELECT

DATEADD(day,1,GetDate()),GetDate()

You can search these two functions and you will find enough information to handle your case.

|||

Hi,

Thanks for all the posts just figured out our problem.

Thanks.

|||

emersondub:

But should it be retriving data from the 1st of February even though the query states > 1st of Feb?

although using > will select almost all datetimes from Feb 1st, if a record were added on Feb 1st with a zero time value (midnight), then that record would not be selected even though it was on Feb 1st. To insude you dont drop these records, alway use >=