Date Conversions - Are they possible? 1

[MikeBronner]

I would like to convert minutes into hours and minutes inside a SQL select statement as follows:

SELECT *, convert Time to hours AS Hours FROM Table

where time is a datafield containing the number of minutes.

Is this possible?

Thanks! Take Care,
Mike

 

[MikeBronner]

Really, the only thing I'm missing is the type-casting in SQL, so that i could use the following:

SELECT *, (Int(Time/60) & "h " & Time%60 & "m&quot AS Hours FROM Table

The code in parentheses is c++/vb pseudocode, but thats basically what I'm looking to accomplish.

Any ideas?
Take Care,
Mike

 

[shmiller]

What are you doing with these time values once you get them. Is there a reason you can't just convert them in code after you've retrieved the records? And are you talking ADO, or something else?

 

[MikeBronner]

well, i can't chenge them in code, since I will be linking the resulting recordset directly to a datareport.
and yes, i'm using ADO to connect.
Thanks Take Care,
Mike

 

[chiph]

Michael -

Don't forget that the SQL Server Datetime datatype always includes a date and a time, so even if you extract the minutes out, if you put it back into a datetime variable, it'll come out as: 12/31/1869 00:xx:00.

It sounds like you need to do something like:

@iMinutes = CAST(SUBSTRING(CAST(StartTime AS nchar(20)), 15, 2) AS int)

Chip H.

 

[MichaelRed]

MyMIns = 200
? Format(MyMins / 1440, "HH:mm&quot
03:20

? Format(MyMins / 1440, "Short Time&quot
03:20

P.S. the 1440 is the number of minutes in a day = 24 * 60
Since the time part of a datetime data type represents the fractional part of the day, the minutes need to be translated intot he fraction. Thereafter it may be treated as any date time data type. Of course there is no DATE (Integer) part, so the display of the date should be suppresssed.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[MikeBronner]

MichaelRed,

So if I understand you correctly, the following should work:

"SELECT *, FORMAT(Time/1440,'HH:mm') FROM Table"

Thanks!

Take Care,
Mike

 

[chiph]

Michael Bronner -

No, the FORMAT command/function is part of VB (not part of SQL), and if you want to use it, you'll have to take it outside the double-quotes:
[tt]
"SELECT *, '" & FORMAT(Time/1440,'HH:mm') & "' FROM Table"
[/tt]
When you run this, you'll get a result set where each row has every column in your table listed (the asterisk part of the SELECT clause), plus a column with the time.
[tt]
Name Address City State
------- ----------- ---------- ----- -----
Joe Main St L.A. CA 16:13
Jill Oak Ave. NYC NY 16:13
Jack Capital Rd. Chicago IL 16:13
[/tt]

Chip H.

 

[MikeBronner]

MichaelRed, thats the problem though. I need it as part of the SQL. Take Care,
Mike

 

[MichaelRed]

There is no reason the conversion can't be done in the datareport.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[MikeBronner]

How would that be done? I don't have much experience with data reports, especially if its done in the code. Any help is appreciated.

Thanks,
Mike Take Care,
Mike

 

[MichaelRed]

I don't use then at all, as I have just gotten in the habbit of doing it on my own (bcause the datareport thinggy) is SOOOOOOOOOOOO Retro). However, you can set the field value to any expression, not JUST the recordsource field, so the expression just goes into the controls source w/ te "=" prefix, a-la:

=Format([MyMins] / 1440, "Short Time&quot

where [MyMins] is the summed time fields.

You shold also, however, be aware that the display for short time or HH:MM will NOT transcend the 24 Hours barier / limit on the number of hours in the DAY period. To show valyes greater than this (as in, perhaps the cumulative time worked in a week, month ...), it is either done as just a decimal value or with specific conversions.

I think the whole issue is now a bit confused. In the initial post, the reference was JUST to converting a time value to hours. Later, the use of some value (the converted time to hours) is discused as being (used) in a (VB) DataReport.

There are various options even between these two venues, and the 'better' selection may depend on yet other criteria. To JUST do the conversion, I would do the math in the SQL arena (Time / 1440) this should ALWAYS give the hours in the date/time field. How this is used thereafter would be a seperate issue or discussion. Simple normalization rules would almost dictate this soloution unless the schema of the db is being changes. It requires ammost NO effort at any level to achieve and leaves the existing schema in place, trhus also requiring no other changes to the app. Extracted in this manner through a simple select query, the value is converted in-process, so it may readily me modified to suit other changes at a later time.




MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[MikeBronner]

MichaelRed:
Nono, you didn't confuse at all. In fact, you cleared up quite a few cloudy spots regarding datareports! Thanks a bunch! Take Care,
Mike

 

[MikeBronner]

In another matter, MichaelRed, what do you use instead of DataReports? I'd be eager to learn a better, more efficient way of doing things

Thanks,
Mike Take Care,
Mike

 

[MichaelRed]

Are you familiar with the quote "Be careful what you ask for .... "

I - me - personally - usually generate my own reports from straight VB code. Many think this is somewhat 'stupid', but I do not like the cost/benefit of the 'third party' packages (e.g. "Crystal Reports&quot. From my perspective, they all require a great deal of duplication of effort and fail to provide the overall flexibility I want/need/desire in reporting.

I find MS Access a truly superior tool for report generation and often even generate reports therein and construct an auxilliary mdb/mde file for standard reports for an app.

BUT for truly VB based reports I just use the printer object and its properties to 'roll my own'. The most common format is to set up arrays of locations with font selection and attributes ([Header | GroupHdr(s) | (Grp)Detail(s) | GroupFtr(s) | Footer] for the various outputs. With various techniques to assemble the recordset contents, the locations and font are set and the content set to the printer object. Page 'Progress' (current X/Y) are tracked, so that pagenation is achieved. It sounds like a lot of work, and at times it can be. The normal reality is that -like most repetive tasks- once the concept is familiar it becomes more or less just another routine to incorporate.

(the completion of the quote goes here).

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[MikeBronner]

I agree with you in all points: I too find 3rd party tools cumbersome, require additional learning, and don't have the flexibility that is oftentimes needed. I've had my share of frustrations with the DataReport element as well, but so far I have been able to iron all the kinks out (still working on the time though).

Again, thanks for your patience and time
Take Care,
Mike