DateTime Convert for GL reporting

Hi,

This is mostly a SQL question and I'm having issues figuring it out. It has to do with converting the datetime format in Post dates for GL accounts.

We have a custom procedure/report that is slightly different from the GL_SUMMARY_REPORT.  Basically, the question I have is that our finance team wants the date format for this report to be 'mm/dd/yyyy' and though I think i'm doing it right, it doesn't seem to be working!

Apparently, before we upgraded to V12, the report output when saved in excel did save in the date format that they wanted, but I'm not sure why that would have anything to do with it.  The main issues is, now that we are in V12, this format is different and will not import into our excel macro, which is then uploaded into our accounting software and how our finance department does their job.

I have suggested altering the macro to be compatible to the datetime format in Tessitura, which at this point is 'YYYY-MM-DD HH:MM:SS:MMM", but they insist that I change it back to the way it was before the upgrade. *sigh*

I have found the procedure this report uses to run and have found the code I think I would need to alter (just the output, right?) , but I'm not sure how to CONVERT the post_dt which is also in datetime.  I've been trying CONVERT to various different format types, but I think my syntax might be wrong.  In my testing, all the results after I insert the CONVERT function, are coming back with incorrect dates ("1900-01-01 .........")

Any help would be great.  I must be missing something obvious!

Here's the sproc with notes on where I'm having the trouble.

Normal 0 false false false EN-US X-NONE X-NONE

select      a.gl_act_no ,         

      a.type ,           

      a.post_amt ,

      b.gl_description,

      a.post_no,

      a.post_dt,  ---This is the date I want!

      debit = convert(money,0),

      credit = convert(money,0),

      net = convert(money,0),

      mode = convert(int,0),

      fyear

into  #temp

FROM  T_GL_POSTING_HISTORY a 

      LEFT OUTER JOIN T_GL_ACCOUNT b  ON a.gl_act_no  =  b.gl_account_no

where   2=1

Normal 0 false false false EN-US X-NONE X-NONE

insert into #temp

SELECT  a.gl_act_no ,         

      a.type ,          

      a.post_amt ,

      gl_description = ISNULL(b.gl_description, '***UNKNOWN***'),

      a.post_no,

      a.post_dt,--Again, here's the datetime

      debit = case when a.type = 'D' then a.post_amt else 0 end,

      credit = case when a.type = 'C' then a.post_amt else 0 end,

      net = convert(money,0),

      mode = @mode,

      a.fyear

FROM  T_GL_POSTING_HISTORY a 

      LEFT OUTER JOIN T_GL_ACCOUNT b  ON a.gl_act_no  =  b.gl_account_no 

WHERE       a.post_dt >= @post_dt_start and

        a.post_dt <= @post_dt_end and

      exists (select * from t_batch where post_no = a.post_no) and      --

      a.post_no in (select post_no from t_batch where batch_type in

      (select id from tr_batch_type where description like 'T:%'))

SELECT      gl_act_no ,         

      gl_description = max(gl_description),

      post_no,

      post_dt,---= CONVERT(datetime,110),---This is the output, so is this where I would change the date format??

      debit = sum(debit),

      credit = sum(credit),

      net = sum(debit) - sum(credit),

      mode = max(mode),

      fyear

FROM    #temp

group by gl_act_no, fyear, post_no,post_dt

GO

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Hi,

This is mostly a SQL question and I'm having issues figuring it out. It has to do with converting the datetime format in Post dates for GL accounts.

We have a custom procedure/report that is slightly different from the GL_SUMMARY_REPORT.  Basically, the question I have is that our finance team wants the date format for this report to be 'mm/dd/yyyy' and though I think i'm doing it right, it doesn't seem to be working!

Apparently, before we upgraded to V12, the report output when saved in excel did save in the date format that they wanted, but I'm not sure why that would have anything to do with it.  The main issues is, now that we are in V12, this format is different and will not import into our excel macro, which is then uploaded into our accounting software and how our finance department does their job.

I have suggested altering the macro to be compatible to the datetime format in Tessitura, which at this point is 'YYYY-MM-DD HH:MM:SS:MMM", but they insist that I change it back to the way it was before the upgrade. *sigh*

I have found the procedure this report uses to run and have found the code I think I would need to alter (just the output, right?) , but I'm not sure how to CONVERT the post_dt which is also in datetime.  I've been trying CONVERT to various different format types, but I think my syntax might be wrong.  In my testing, all the results after I insert the CONVERT function, are coming back with incorrect dates ("1900-01-01 .........")

Any help would be great.  I must be missing something obvious!

Here's the sproc with notes on where I'm having the trouble.

Normal 0 false false false EN-US X-NONE X-NONE

select      a.gl_act_no ,         

      a.type ,           

      a.post_amt ,

      b.gl_description,

      a.post_no,

      a.post_dt,  ---This is the date I want!

      debit = convert(money,0),

      credit = convert(money,0),

      net = convert(money,0),

      mode = convert(int,0),

      fyear

into  #temp

FROM  T_GL_POSTING_HISTORY a 

      LEFT OUTER JOIN T_GL_ACCOUNT b  ON a.gl_act_no  =  b.gl_account_no

where   2=1

Normal 0 false false false EN-US X-NONE X-NONE

insert into #temp

SELECT  a.gl_act_no ,         

      a.type ,          

      a.post_amt ,

      gl_description = ISNULL(b.gl_description, '***UNKNOWN***'),

      a.post_no,

      a.post_dt,--Again, here's the datetime

      debit = case when a.type = 'D' then a.post_amt else 0 end,

      credit = case when a.type = 'C' then a.post_amt else 0 end,

      net = convert(money,0),

      mode = @mode,

      a.fyear

FROM  T_GL_POSTING_HISTORY a 

      LEFT OUTER JOIN T_GL_ACCOUNT b  ON a.gl_act_no  =  b.gl_account_no 

WHERE       a.post_dt >= @post_dt_start and

        a.post_dt <= @post_dt_end and

      exists (select * from t_batch where post_no = a.post_no) and      --

      a.post_no in (select post_no from t_batch where batch_type in

      (select id from tr_batch_type where description like 'T:%'))

SELECT      gl_act_no ,         

      gl_description = max(gl_description),

      post_no,

      post_dt,---= CONVERT(datetime,110),---This is the output, so is this where I would change the date format??

      debit = sum(debit),

      credit = sum(credit),

      net = sum(debit) - sum(credit),

      mode = max(mode),

      fyear

FROM    #temp

group by gl_act_no, fyear, post_no,post_dt

GO

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!