Retrieving matching dates from MS SQL DB 2

[plcman]

Hi

I am using VB6 and MS SQL Express DB. I have an app that uses a date picker for the operator to select a date, then I want to use an SQL statement to retrieve a record from the database that matches that date. The date in the DB is stored in Date/Time format, but I am only interested in the date portion.

At the moment I am using this.
sql_str = "SELECT Produced, Running_Time, Stopped_Time, Start_date From Shifts WHERE Start_Date = (" & dtpk_start_date.Value & ")"

but as the time is stored in the same field it doesn't work.

Can anybody suggest a possible solution.

many Thanks

Regards

Alan Edwards

85% of statistics that are quoted are entirely made up!

 

[gmmastros]

Try this...

sql_str = "SELECT Produced, Running_Time, Stopped_Time, Start_date From Shifts WHERE Start_Date = DateAdd(Day, DateDiff(day, 0, '" & dtpk_start_date.Value & "'), 0)"

Notice this part...

DateAdd(Day, DateDiff(Day, 0, 'YourDate'), 0)

It will remove the time portion of a DateTime value. For example....

[tt][blue]
Select DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[plcman]

Thanks for the reply!

I have in the mean time tried this

sql_str = "SELECT sum(Produced) as sum_prod, sum(Running_Time) as sum_run, sum(Stopped_Time) as sum_stop From Shifts WHERE Convert(VarChar, Start_Date, 103)BETWEEN '" & dtpk_start_date.Value & "'" & "AND '" & dtpk_end_date.Value & "'"

as I need to be able to pick a date range. It workd on a single date but when using the BETWEEN, it only seems to take account of the first element of the date.IE If I pick 14/09/06 to the 14/09/06 it picks all elements with 14 as the day regardless of the month and year.

Any ideas?

Regards

Alan Edwards

85% of statistics that are quoted are entirely made up!

 

[gmmastros]

Yeah. I have ideas.

The problem is that you are converting your dates to strings and comparing them that way. Instead, you need to treat them as dates in order to get this to work properly.

With dates, you always need to consider the time component. It's important to do so. Also, while you are at it, you should be aware of the 'international' issues for dates. Some people use day/month/year and others use month/day/year.

Because if this, it is always safest to use the ISO unseperated date format when using dates in a where clause. Dates in this format will NEVER be misinterpretted by SQL Server.

So, for example, suppose you wanted all of the records for 14/09/2006 to 16/09/2006. The best way to do this is...

[COLOR=blue]SELECT[/color] sum(Produced) [COLOR=blue]as[/color] sum_prod, 
       sum(Running_Time) [COLOR=blue]as[/color] sum_run, 
       sum(Stopped_Time) [COLOR=blue]as[/color] sum_stop 
[COLOR=blue]From[/color]   Shifts 
[COLOR=blue]WHERE[/color]  Start_Date >= [COLOR=red]'20060914'[/color]
       And Start_Date < [COLOR=red]'20060917'[/color]

Notice that I used < '20060917' (17/09/2006). So, if a record has a value of 8 am on 16/09/2006, it will be less than midnight on the 17th and will be included in the recordset. Any record from the 17th will NOT be included.

So, your final code should look like this...

sql_str = "SELECT sum(Produced) as sum_prod, sum(Running_Time) as sum_run, sum(Stopped_Time) as sum_stop From Shifts WHERE Start_Date > '" & Format(dtpk_start_date.Value, "yyyymmdd") & "' AND Start_Date < '" & Format(dtpk_end_date.Value, "yyyymmdd") & "' + 1"

*Note: I may not have gotten all the quotes and parenthesis in the right place.

Does this make sense?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[plcman]

Fantastic!!!!!!

With a small adjustment to the bracketing that works perfectly.

Many Many Thanks

Regards

Alan Edwards

85% of statistics that are quoted are entirely made up!

 

[BobRodes]

Another star for George from me. I appreciate (as do many others, I'm sure) the quickness of your responses, the amount of work you put into your answers, their clarity and your knowledge of the subject. You've been a lot of help to me on the occasions when I've needed it, too.

Best Regards,

Bob