Saturday, February 25, 2012

dates and Time Comparisons

got a quick question guys.

if i use this to parse the current date to the right side of the time.
right(getdate(),7) - i'll get something like 7:30AM.

i also have Times stored in a column of a table, but as a string not a date time.
it seems to compare okay, but when the time is say 1:30PM and im comparing it if its greater than or equal to (>=)to 7:30AM - it doesnt return.

i think its ignoring the AM/PM Meridian Values and just comparing the numbers.

is there a conversion i could use to do this?
ive tried a military time conversion i found but it converts to hrs,min,milliseconds.
convert(char(8),(convert(datetime,current_timestam p,113)),114)

if anyone knows a good way to do this - i would appreciate it.

thanks again
rikI think i may have a solution to this, but, i probably should make this a UDF.

using military time and reading from the left rather than right :::
select left(convert(char(8),(convert(datetime,'7:33AM',11 3)),114),5)
gives me 07:33 -
which if i compare it to
select left(convert(char(8),(convert(datetime,'1:33PM',11 3)),114),5)

i'll get 13:33 and can compare too 07:33 just fine.

does that sound about right?

No comments:

Post a Comment