Month to 2 digits

Month to 2 digits

Post by Steve Chatha » Sat, 07 Sep 2002 23:29:49



This one:

Quote:> Try RIGHT('00' + convert(varchar, month(getdate()) )  , 2 )

Appears to do what I want it to do.

Thanks for the help.

Steve

> John

> On Fri, 6 Sep 2002 09:38:53 -0400, "Steve Chatham"

> >I'm having to write some date stuff to determine current, past due and
> >future orders.

> >I'm using similar code in a couple places in a query:

> >convert(varchar(4),year(max(req_ship_date)),2) as 'yyy',
> > convert(varchar(2),month(max(req_ship_date)),2) as 'xxx' ,

> >This is the actual case statement I've got:

> >             case when  convert(varchar(4),year(max(req_ship_date)),2) +
> >convert(varchar(2),month(max(req_ship_date)),2)  <
> >convert(varchar(4),year(getdate()),2) +
> >convert(varchar(2),month(getdate()),2)  then
> >             'Past Due' else
> >             case when convert(varchar(4),year(max(req_ship_date)),2) +
> >convert(varchar(2),month(max(req_ship_date)),2) >
> >convert(varchar(4),year(getdate()),2) +
> >convert(varchar(2),month(getdate()),2) then
> >             'Future' else
> >             case when convert(varchar(4),year(max(req_ship_date)),2) +
> >convert(varchar(2),month(max(req_ship_date)),2) =
> >convert(varchar(4),year(getdate()),2) +
> >convert(varchar(2),month(getdate()),2) then
> >                'Current'

> >What happens, when I run it, is that the year converts to 4 digits, and
the
> >month does to 2 digits when it's 10+.  However, if the month < 10, I get
> >just the single digit.

> >So, when I try to do the concatenation of the YYYYMM to determine future,
> >past and current orders, if the month is 10+, I get 200210.  If the month
is
> >9, I get 20029.  That'll work fine for now, but if the date is for
September
> >2003, that'd return 20039, which would still be < 200210 (October 2002).

> >Is there a way to do an on the fly convert and get the month to be 2
digits?

> >Thanks,

> >Steve

 
 
 

Month to 2 digits

Post by Steve Chatha » Sat, 07 Sep 2002 22:38:53


I'm having to write some date stuff to determine current, past due and
future orders.

I'm using similar code in a couple places in a query:

convert(varchar(4),year(max(req_ship_date)),2) as 'yyy',
 convert(varchar(2),month(max(req_ship_date)),2) as 'xxx' ,

This is the actual case statement I've got:

             case when  convert(varchar(4),year(max(req_ship_date)),2) +
convert(varchar(2),month(max(req_ship_date)),2)  <
convert(varchar(4),year(getdate()),2) +
convert(varchar(2),month(getdate()),2)  then
             'Past Due' else
             case when convert(varchar(4),year(max(req_ship_date)),2) +
convert(varchar(2),month(max(req_ship_date)),2) >
convert(varchar(4),year(getdate()),2) +
convert(varchar(2),month(getdate()),2) then
             'Future' else
             case when convert(varchar(4),year(max(req_ship_date)),2) +
convert(varchar(2),month(max(req_ship_date)),2) =
convert(varchar(4),year(getdate()),2) +
convert(varchar(2),month(getdate()),2) then
                'Current'

What happens, when I run it, is that the year converts to 4 digits, and the
month does to 2 digits when it's 10+.  However, if the month < 10, I get
just the single digit.

So, when I try to do the concatenation of the YYYYMM to determine future,
past and current orders, if the month is 10+, I get 200210.  If the month is
9, I get 20029.  That'll work fine for now, but if the date is for September
2003, that'd return 20039, which would still be < 200210 (October 2002).

Is there a way to do an on the fly convert and get the month to be 2 digits?

Thanks,

Steve

 
 
 

Month to 2 digits

Post by John Bel » Sat, 07 Sep 2002 23:01:25


Try RIGHT('00' + convert(varchar, month(getdate()) )  , 2 )

John

On Fri, 6 Sep 2002 09:38:53 -0400, "Steve Chatham"


>I'm having to write some date stuff to determine current, past due and
>future orders.

>I'm using similar code in a couple places in a query:

>convert(varchar(4),year(max(req_ship_date)),2) as 'yyy',
> convert(varchar(2),month(max(req_ship_date)),2) as 'xxx' ,

>This is the actual case statement I've got:

>             case when  convert(varchar(4),year(max(req_ship_date)),2) +
>convert(varchar(2),month(max(req_ship_date)),2)  <
>convert(varchar(4),year(getdate()),2) +
>convert(varchar(2),month(getdate()),2)  then
>             'Past Due' else
>             case when convert(varchar(4),year(max(req_ship_date)),2) +
>convert(varchar(2),month(max(req_ship_date)),2) >
>convert(varchar(4),year(getdate()),2) +
>convert(varchar(2),month(getdate()),2) then
>             'Future' else
>             case when convert(varchar(4),year(max(req_ship_date)),2) +
>convert(varchar(2),month(max(req_ship_date)),2) =
>convert(varchar(4),year(getdate()),2) +
>convert(varchar(2),month(getdate()),2) then
>                'Current'

>What happens, when I run it, is that the year converts to 4 digits, and the
>month does to 2 digits when it's 10+.  However, if the month < 10, I get
>just the single digit.

>So, when I try to do the concatenation of the YYYYMM to determine future,
>past and current orders, if the month is 10+, I get 200210.  If the month is
>9, I get 20029.  That'll work fine for now, but if the date is for September
>2003, that'd return 20039, which would still be < 200210 (October 2002).

>Is there a way to do an on the fly convert and get the month to be 2 digits?

>Thanks,

>Steve

 
 
 

Month to 2 digits

Post by Wayne Snyde » Sat, 07 Sep 2002 23:04:39


try:    right('0' + datepart(mm,max(req_ship_date)),2)
--
Wayne Snyder, SQL Server MVP
Computer Education Services Corporation (CESC), C*te, NC
www.computeredservices.com

I support the Professional Association for SQL Server (PASS) and its user
community of SQL Server Professionals.
www.sqlpass.org

 
 
 

Month to 2 digits

Post by Anith Se » Sat, 07 Sep 2002 23:02:25


Your CASE expression can be written as:
...
CASE
     WHEN LEFT(CONVERT(VARCHAR, MAX(req_ship_date), 112), 4) <
        LEFT(CONVERT(VARCHAR, GetDate(), 112), 4) THEN 'Past Due'
     WHEN LEFT(CONVERT(VARCHAR, MAX(req_ship_date), 112), 4) >
        LEFT(CONVERT(VARCHAR, GetDate(), 112), 4) THEN 'Future'
     ELSE 'Current' END
...

--
- Anith

 
 
 

Month to 2 digits

Post by Keith Kratochvi » Sat, 07 Sep 2002 23:04:48


How about something like this?

SELECT SUBSTRING(CONVERT(varchar(8),GETDATE(),112), 1, 6)

--
Keith, SQL Server MVP


Quote:> I'm having to write some date stuff to determine current, past due and
> future orders.

> I'm using similar code in a couple places in a query:

> convert(varchar(4),year(max(req_ship_date)),2) as 'yyy',
>  convert(varchar(2),month(max(req_ship_date)),2) as 'xxx' ,

> This is the actual case statement I've got:

>              case when  convert(varchar(4),year(max(req_ship_date)),2) +
> convert(varchar(2),month(max(req_ship_date)),2)  <
> convert(varchar(4),year(getdate()),2) +
> convert(varchar(2),month(getdate()),2)  then
>              'Past Due' else
>              case when convert(varchar(4),year(max(req_ship_date)),2) +
> convert(varchar(2),month(max(req_ship_date)),2) >
> convert(varchar(4),year(getdate()),2) +
> convert(varchar(2),month(getdate()),2) then
>              'Future' else
>              case when convert(varchar(4),year(max(req_ship_date)),2) +
> convert(varchar(2),month(max(req_ship_date)),2) =
> convert(varchar(4),year(getdate()),2) +
> convert(varchar(2),month(getdate()),2) then
>                 'Current'

> What happens, when I run it, is that the year converts to 4 digits, and
the
> month does to 2 digits when it's 10+.  However, if the month < 10, I get
> just the single digit.

> So, when I try to do the concatenation of the YYYYMM to determine future,
> past and current orders, if the month is 10+, I get 200210.  If the month
is
> 9, I get 20029.  That'll work fine for now, but if the date is for
September
> 2003, that'd return 20039, which would still be < 200210 (October 2002).

> Is there a way to do an on the fly convert and get the month to be 2
digits?

> Thanks,

> Steve

 
 
 

Month to 2 digits

Post by lindawi » Sun, 08 Sep 2002 00:32:58


Steve,

Quote:> I'm using similar code in a couple places in a query:

> convert(varchar(4),year(max(req_ship_date)),2) as 'yyy',
>  convert(varchar(2),month(max(req_ship_date)),2) as 'xxx' ,

> What happens, when I run it, is that the year converts to 4 digits,
> and the month does to 2 digits when it's 10+.  However, if the
> month < 10, I get just the single digit.

> Is there a way to do an on the fly convert and get the month to be
> 2 digits?

If you study the format specifications for the convert function,
you will see that format 112 does exactly with you want without
the messy string concatination.

The expression

Quote:> convert(varchar(4),year(max(req_ship_date)),2) +
> convert(varchar(2),month(max(req_ship_date)),2)  

can be rewritten as

convert(varchar(6), max(req_ship_date), 112)

and the expression

Quote:> convert(varchar(4),year(getdate()),2) +
> convert(varchar(2),month(getdate()),2)  

becomes

convert(varchar(6), getdate(), 112)

So your big expression becomes:

case when convert(varchar(6), max(ShippedDate), 112) <
          convert(varchar(6), getdate(), 112)
     then 'Past Due'
     when convert(varchar(6), max(ShippedDate), 112) >
          convert(varchar(6), getdate(), 112)
     then 'Future'
     when convert(varchar(6), max(ShippedDate), 112) =
          convert(varchar(6), getdate(), 112)
     then 'Current'
end

However, this can be simplified even further.
Put the conversion into a view, derived table, or
computed column. Something like this (untested):

select * from
  (select convert(varchar(6), max(ShippedDate), 112) req_ship_month,
          convert(varchar(6), getdate(), 112) this_month
  from  YourTable) dt

Your expression then becomes

case when req_ship_month < this_month then 'Past Due'
     when req_ship_month > this_month then 'Future'
     when req_ship_month = this_month then 'Current'
end

Even if you are doing this date comparison in only one place
I would recommend this approach for readability.

Linda

 
 
 

Month to 2 digits

Post by Keith Kratochvi » Sun, 08 Sep 2002 06:49:30


That was my thought process as well....
Some people just like over complicated code.

--
Keith, SQL Server MVP


Quote:> Steve,

> > I'm using similar code in a couple places in a query:

> > convert(varchar(4),year(max(req_ship_date)),2) as 'yyy',
> >  convert(varchar(2),month(max(req_ship_date)),2) as 'xxx' ,

> > What happens, when I run it, is that the year converts to 4 digits,
> > and the month does to 2 digits when it's 10+.  However, if the
> > month < 10, I get just the single digit.

> > Is there a way to do an on the fly convert and get the month to be
> > 2 digits?

> If you study the format specifications for the convert function,
> you will see that format 112 does exactly with you want without
> the messy string concatination.

> The expression

> > convert(varchar(4),year(max(req_ship_date)),2) +
> > convert(varchar(2),month(max(req_ship_date)),2)

> can be rewritten as

> convert(varchar(6), max(req_ship_date), 112)

> and the expression

> > convert(varchar(4),year(getdate()),2) +
> > convert(varchar(2),month(getdate()),2)

> becomes

> convert(varchar(6), getdate(), 112)

> So your big expression becomes:

> case when convert(varchar(6), max(ShippedDate), 112) <
>           convert(varchar(6), getdate(), 112)
>      then 'Past Due'
>      when convert(varchar(6), max(ShippedDate), 112) >
>           convert(varchar(6), getdate(), 112)
>      then 'Future'
>      when convert(varchar(6), max(ShippedDate), 112) =
>           convert(varchar(6), getdate(), 112)
>      then 'Current'
> end

> However, this can be simplified even further.
> Put the conversion into a view, derived table, or
> computed column. Something like this (untested):

> select * from
>   (select convert(varchar(6), max(ShippedDate), 112) req_ship_month,
>           convert(varchar(6), getdate(), 112) this_month
>   from  YourTable) dt

> Your expression then becomes

> case when req_ship_month < this_month then 'Past Due'
>      when req_ship_month > this_month then 'Future'
>      when req_ship_month = this_month then 'Current'
> end

> Even if you are doing this date comparison in only one place
> I would recommend this approach for readability.

> Linda

 
 
 

Month to 2 digits

Post by Delbert Glas » Sun, 08 Sep 2002 09:02:40


Consider:

If a shipment is to be ship on the last day of the month,
it is pastdue the following day when it is only one day late.
Meanwhile a shipment that was to be shipped on the
first day of that same month has been late for 4 weeks
without being classified as pastdue!

If you are going to treat your customers that way,
you could just always entered the last date of month
of the requested ship date as the req_ship_date.
You could then simply check req_ship_date against
the current date to determine the status.

Of course the above suggests several alternative
ways you could have formulated the comparision.

Bye,
Delbert Glass