Question About Time

[evios]

Lets say if the user choose an interval from 2008/06/09 12:00:00 till 2008/06/10 12:00:00, and there will be data within that interval that i need to further categorize, and i use the coding as below:

            If vDateTime.Hour >= 0 And vDateTime.Hour < 24 Then
                Dim hour As Integer = vDateTime.Hour
                If hour = 0 Then
                    hour = 24
                End If

those with 12:00:00 will fall within hour=12:00:00. However there will be 2 dates merge together which i dont want it in that way....how can i solve it? Thanks.
P/S: I juz manage to convert the data in the database from varchar to:

 vDateTime = Convert.ToDateTime(storedate(1))

 

[alander]

Hi, are u trying to get the time diff between 2 dates? Sorry but i am finding problems trying to understand you.

If you are you can try using the timespan type

i am assuming you have 2 datetimepickers for users to select the interval

        Dim startDate As Date = dtpStartDate.Value
        Dim endDate as Date = dtpEndDate.Value
        Dim interval As TimeSpan = endDate - startDate

       'to get the days, 
        Debug.Print(interval.Days)
       'to get the hours, 
        Debug.Print(interval.Hours)
       'to get the minutes, 
        Debug.Print(interval.Minutes)
       'to get the seconds, 
        Debug.Print(interval.Seconds)
       'to get the milliseconds, 
        Debug.Print(interval.Milliseconds)

 

[evios]

Em....
Lets say user can select a time to view the data he needs, for example, if he want to view data from 2008/05/30 12:00:00 to 2008/05/31 12:00:00, but i use the coding below:

If vDateTime.Hour >= 0 And vDateTime.Hour < 24 Then
                        Dim hour As Integer = vDateTime.Hour
                        If hour = 0 Then
                            hour = 24
                        End If

Hence those data within that particular hours will be grouped.
But now i am facing this problem:
2008/05/30 08:00:00 and 2008/05/31 08:00:00 will grouped together and consider as same but actually are of two different date, which i dont want in that way.
The idea i want is something like this:
For 2008/05/30:
08:00:00 .........
09:00:00 ........
23:59:59

For 2008/05/31:
08:00:00 .........
09:00:00 ........
23:59:59

Thanks

 

[cjard]

Youre not making this any easier because youre assuming we know what youre storing your data in. Remember, your problem makes sense to you because you know the system youre talking about.. We're lost.

Here's a database way of getting what you want:

SELECT * FROM table WHERE timeColumn BETWEEN @fromDate AND @toDate


Does it help? Probably not, but consider that i've been as helpful there as you have been to me.. You want free advice, you need to learn to explain yourself very well

 

[evios]

OK, i do my best effort to explain this:
Lets say now inside my database has one field of name='TEX' and it looks like this:

+-----------------------+
| TEX                   |
+-----------------------+
| 75   2008/05/15 02:35 |
| 77   2008/05/15 02:47 |
| 77   2008/05/15 02:47 |
| 77   2008/05/15 02:47 |
| 77   2008/05/16 02:47 |
| 77   2008/05/16 02:47 |
| 77   2008/05/16 02:47 |
| 75   2008/05/15 02:27 |
| 75   2008/05/16 02:27 |
| 77   2008/05/15 02:47 |

the two digits on the leftmost denote the machine ID, while the date and time on the right showing the time that particular item has been pass through this machine. For example, 75 2008/05/15 02:35 means the item pass through machine ID 75 at time 2008/05/15 02:35.
Now i need to categorize it. In ASP.NET, lets say user want to view data from 2008/05/15 12:00:00 to 2008/05/16 12:00:00. Then i need to show them on that particular 2008/05/15 12:00:00, on machine 75,76,77,78, there are how much items being allocated to them according to hour. Like:
2008/05/15 12:00:00 For Machine 75: 89
2008/05/15 12:00:00 For Machine 76: 23
2008/05/15 12:00:00 For Machine 77: 45
2008/05/15 12:00:00 For Machine 78: 88
.....
It goes by hour here.
I'd settled everything, but now i face one critical problem. Look at what user has chosen to view:
2008/05/15 12:00:00 to 2008/05/16 12:00:00
I used the code shown below:

                    If vDateTime.Hour >= 0 And vDateTime.Hour < 24 Then
                        Dim hour As Integer = vDateTime.Hour
                        If hour = 0 Then
                            hour = 24
                        End If

That means, once it see hour=12:00:00, it will be allocated to hour=12 and so on. How if yesterday 12:00:00 and todays 12:00:00? They had been throw to the same hour but actually are of different date. This is what i am facing now. I need to separate the date as well if 2 dates have been chosen.

Thanks.

 

[InertiaM]

Whoever designed that database should be shot. Anyway...

If I was writing this, I'd split the TEX field into different parts within a query. Take the first two characters, and make a 'MACHINE' field, take 13 characters starting at position 5 to give a 'PRODUCTIONDATEHOUR' field. You can then group by these fields which will give you what you want. A generic example would be

SELECT
  SUBSTRING (TABLENAME.TEX, 0, 2) AS MACHINE,
  SUBSTRING (TABLENAME.TEX, 5, 13) AS PRODUCTIONDATEHOUR
FROM
  TABLENAME.TEX
WHERE
  CONVERT (datetime, SUBSTRING (TABLENAME.TEX, 5, 19)) >= @SEARCHSTARTTIME
AND
  CONVERT (datetime, SUBSTRING (TABLENAME.TEX, 5, 19)) <= @SEARCHENDTIME
GROUP BY
  SUBSTRING (TABLENAME.TEX, 0, 2),
  SUBSTRING (TABLENAME.TEX, 5, 13)

 

[cjard]

Can you explain why your example data shows times of about 2am (02:xx) but you have given a sample output where all the times are 12pm (noon) ?

 

[InertiaM]

I read all of it, ignored most of it and just worked with

Then i need to show ... how much items being allocated to them according to hour

Dont know if that's what was wanted, but it's the closest I could achieve given the vagueness

 

[cjard]

couple of missings in your SQL, but before I launch into an answer, i want to know what question i'm answering