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?

DateAdd

Trying to use the DateAdd feature so I can do some MTD/YTD
comparisons. When I use the Expression builder, I am getting errors.
Using =DateAdd(yy, -1, 'parameter'), the yy is underlined in red.
I've tried year and Year. I've even seen it put into quotes ("yy").
I'm basically just trying to take the current date and subtract 1 year
from it. My ultimate goal is to create a report where I get LYTD/YTD
and MTD compared to the same month a year ago. This would key of a
report parameter since the user may want to look at particular month,
not just current. The report works fine as long as you manually put
in every date. I just need the DateAdd to make some automatic
calculations for me. Thanks in advance.On Dec 5, 3:51 pm, Barry <ba...@.tecelectric.com> wrote:
> Trying to use the DateAdd feature so I can do some MTD/YTD
> comparisons. When I use the Expression builder, I am getting errors.
> Using =DateAdd(yy, -1, 'parameter'), the yy is underlined in red.
> I've tried year and Year. I've even seen it put into quotes ("yy").
> I'm basically just trying to take the current date and subtract 1 year
> from it. My ultimate goal is to create a report where I get LYTD/YTD
> and MTD compared to the same month a year ago. This would key of a
> report parameter since the user may want to look at particular month,
> not just current. The report works fine as long as you manually put
> in every date. I just need the DateAdd to make some automatic
> calculations for me. Thanks in advance.
DateAdd is very finicky, and the documentation is really poor. It's
case sensitive, and only certain combinations seem to work. You will
want to use yyyy for Year, M or m for Month, d for Day, H for hour, n
for Minute, s for Second.
You want to not use the double quotes when you are using the function
in SQL, but do use the quotes when it is in a .Net expression.
1 Year Ago from Date
= DateAdd( "yyyy", -1, Parameters!CurrentYearsDate.Value )
-- Scott|||On Dec 5, 3:30 pm, Orne <polysilly...@.yahoo.com> wrote:
> On Dec 5, 3:51 pm, Barry <ba...@.tecelectric.com> wrote:
> > Trying to use theDateAddfeature so I can do some MTD/YTD
> > comparisons. When I use the Expression builder, I am getting errors.
> > Using =DateAdd(yy, -1, 'parameter'), the yy is underlined in red.
> > I've tried year and Year. I've even seen it put into quotes ("yy").
> > I'm basically just trying to take the current date and subtract 1 year
> > from it. My ultimate goal is to create a report where I get LYTD/YTD
> > and MTD compared to the same month a year ago. This would key of a
> > report parameter since the user may want to look at particular month,
> > not just current. The report works fine as long as you manually put
> > in every date. I just need theDateAddto make some automatic
> > calculations for me. Thanks in advance.
> DateAddis very finicky, and the documentation is really poor. It's
> case sensitive, and only certain combinations seem to work. You will
> want to use yyyy for Year, M or m for Month, d for Day, H for hour, n
> for Minute, s for Second.
> You want to not use the double quotes when you are using the function
> in SQL, but do use the quotes when it is in a .Net expression.
> 1 Year Ago from Date
> =DateAdd( "yyyy", -1, Parameters!CurrentYearsDate.Value )
> -- Scott
PERFECT!!! I have spent many numerous hours searching for the correct
syntax. Worked great! Thanks so much.