0

My code is:

create table t_AccountTransactions
(
    CompanyID varchar(50) ,
    RecordID int ,
    Branch varchar(100) ,
    Year int,
    Month varchar(2),
    Date_Gregorian date,
    Date_Persian varchar(100),
    VoucherType varchar(100),
    VoucherNumber varchar(100),
    Row varchar(100),
    AccountNumber_SL varchar(100),
    AccountNumber_DL varchar(100)
)

insert into t_AccountTransactions (RecordID, CompanyID, Branch, Year, Month, Date_Gregorian, Date_Persian, VoucherType, VoucherNumber, Row, AccountNumber_SL, AccountNumber_DL)
   select 
      item.VchItmId, @CompanyID, hdr.BranchCode, hdr.Year,
      right('0' + DATEPART(month,hdr.Month),2),  
      hdr.VchDate, 'Date_Persian', Vtype.Title, hdr.Num,
      item.Seq, item.SLRef, item.DLRef
   from 
      SgDb1_dat.ACC.AccVchHdr as hdr, SgDb1_dat.ACC.AccVchItm as item, SgDb1_dat.ACC.AccVchType as Vtype
   where 
      hdr.HdrVchID = item.HdrRef 
      and hdr.Ctgry = vtype.Code

In my table month will appear as 1,2,3,4,... I want to modify it to appear such as 01,02,03 and ...

Please help me that how can i modify my select statement for this purpose .

thank you.

Arash
  • 1,692
  • 5
  • 21
  • 36
  • [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style should no longer be used, instead use the **proper** ANSI JOIN syntax from the ANSI-**92** SQL Standard (introduced more than **20 years** ago) – marc_s Aug 07 '14 at 09:08

1 Answers1

0

You can something like this :

 select case when DATEPART(month,getdate())<10
        then '0'+DATEPART(month,getdate())
        else DATEPART(month,getdate())
 end

You complete code will be :

insert into t_AccountTransactions (RecordID,CompanyID,Branch,Year,Month,Date_Gregorian,Date_Persian,VoucherType,VoucherNumber,Row,AccountNumber_SL,AccountNumber_DL)
select item.VchItmId,@CompanyID,hdr.BranchCode,hdr.Year,
case when DATEPART(month,hdr.Month)<10 then '0'+DATEPART(month,hdr.Month) else DATEPART(month,hdr.Month) end ,hdr.VchDate,'Date_Persian',Vtype.Title,hdr.Num,item.Seq,item.SLRef,item.DLRef
        from SgDb1_dat.ACC.AccVchHdr as hdr , SgDb1_dat.ACC.AccVchItm as item , SgDb1_dat.ACC.AccVchType as Vtype
            where hdr.HdrVchID=item.HdrRef and hdr.Ctgry = vtype.Code

UPDATE :

change the case section to this :

case when DATEPART(month,hdr.Month)<10 then '0'+CAST(DATEPART(month,hdr.Month) as varchar) else    CAST(DATEPART(month,hdr.Month) as varchar) end 

Remember that, in case statement all of results should have same type.

Saman Gholami
  • 3,416
  • 7
  • 30
  • 71