Parse time stamp 1

[razchip]

I have a time stamp that reads 15:28:02, I need to pass this to another system as 152802. I'm can't hit upon the right query expression as I keep getting 3282. The military hour converts back to the standard hour and the zero in the minutes and seconds seem to disappear. Not sure what to try next.

Any suggestions will be appreciated, thanks.

Thanks for the help.
Greg

 

[SkipVought]

Why don't you want to pass Time, rather than a useless STRING?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[BigRed1212]

Given data like:

ID	mydate
1	3:28:02 PM
2	4:13:56 AM
3	9:02:14 PM

A query like:

SELECT 
right("00" & hour(mydate), 2), 
right("00" & minute(mydate),2), 
right("00" & second(mydate), 2), 
right("00" & hour(mydate), 2) & right("00" & minute(mydate),2) &  right("00" & second(mydate), 2) as mynewtime
from dates

Yields results of:

Expr1000	Expr1001	Expr1002	mynewtime
15		28		02		152802
04		13		56		041356
21		02		14		210214

Perhaps that is useful.

 

[BigRed1212]

But I agree with Skip- passing a time as opposed to a string can have value. But maybe you are converting it back to a time later in whatever you are doing.

 

[razchip]

The odd thing is, I'm collecting this data as a time punch and passing it to our time and attendance system as a string. I believe they use this string as part of their identifier for the record and repopulate their time fields accordingly. We are doing this outside their normal data capturing system. I tried the suggestion you pointed out and it worked fine, thanks again.

Thanks for the help.
Greg

 

[MajP]

why not simply?

Select Format([myDate],"hhmmss") as myNewDate from tblDates

 

[BigRed1212]

Fine MajP. Be all smart and everything. The answer is because I didn't know to do that until now.

 

[razchip]

I'll give it a try too. At least I can say I learned something today.

Thanks for the help.
Greg

 

[MajP]

http://msdn.microsoft.com/en-us/library/office/gg251755.aspx