Thursday, April 30, 2015

How do I convert a SQL Server Date MM/DD/CCYY to an Oracle Date formatted as NUMBER(8,0)

So I have to build dynamic T-SQL because of a date parameter that will be provided. The Date Parameter will be provided in SSRS in normal MM/DD/CCYY format. So how do I then convert that date to my Oracle format NUMERIC(8,0) CCYYMMDD?

I tried this...

SET           @SQLQuery       =       @SQLQuery       +   'AND    MEMBER_SPAN.YMDEFF                                              <=                   '''''   +   CAST(@AsOfDate  AS      VARCHAR)        +   '''''   '       +   @NewLineChar;
SET             @SQLQuery       =       @SQLQuery       +   'AND    MEMBER_SPAN.YMDEFF                                              >=                   '''''   +   CAST(@AsOfDate  AS      VARCHAR)        +   '''''   '       +   @NewLineChar;

but that put it in the format of...

AND

MEMBER_SPAN.YMDEFF<=''2015-04-01''

AND

MEMBER_SPAN.YMDEFF>=''2015-04-01''

which is close...I think I just need to lose the "-"

Thanks for your review and am hopeful for a reply.

No comments:

Post a Comment