Date conversion Conundrum

  • I am working on a report, and I need to take the business date and subtract one day from that business date. I also need the output to read as mmddyy, with no slashes or spaces. So, if I were to take 6/29/2002, It would need to read as 062803. Here is the code that I have written so far:

    DECLARE @STORE_ID VARCHAR (10),

    @BUSINESS_DATE DATETIME

    SET @STORE_ID = ''

    SET @BUSINESS_DATE='06/29/2002'

    SET @BUSINESS_DATE= CAST(DATEPART(MM, @BUSINESS_DATE)AS VARCHAR(2))+ '/' +

    CAST(DATEPART(DD, @BUSINESS_DATE)AS VARCHAR(2))+'/'+

    CAST(DATEPART(YY, @BUSINESS_DATE)AS VARCHAR(4))

    IF(@STORE_ID<>'')

    BEGIN

    SELECTSTORE_ID,

    BANK_CODE,

    DBR_DATE,

    TOTAL

    FROMCSDEPOSIT

    WHERE (@STORE_ID = STORE_ID AND DBR_DATE+1=@BUSINESS_DATE)

    END

    ELSE

    SELECTSTORE_ID,

    BANK_CODE,

    DBR_DATE,

    TOTAL

    FROMCSDEPOSIT

    WHERE (DBR_DATE+1=@BUSINESS_DATE)

    Any feedback that anyone can offer would be greatly appreciated. Thanks

  • REPLACE(CONVERT(char(8),@Business_Date-1,1),'/','')

    --Jonathan



    --Jonathan

  • Where is this being placed within the code?

  • Is there a way to remove the spaces between the month, day, and year?

  • Make sure there's no space between the final two single quotes:

    '' not ' '

    That should work.

    -SQLBill

  • That didn't work for me. I wasn't able to remove the spaces. Another option is to seperate the month, day, and year with DATEPART, and then string them all together in the report. However, I am having trouble keeping the month and day in 2 digit format for dates smaller than 9, and I need to figure out how to get the year in 2 digit format. Any ideas? Here is my code...

    DECLARE @BUSINESS_DATE DATETIME

    SET DATEFORMAT MDY

    SET @BUSINESS_DATE='06/29/2002'

    SET @BUSINESS_DATE= CAST(DATEPART(MM, @BUSINESS_DATE)AS VARCHAR(2))+ '/' +

    CAST(DATEPART(DD, @BUSINESS_DATE)AS VARCHAR(2))+'/'+

    CAST(DATEPART(YY, @BUSINESS_DATE)AS VARCHAR(4))

    SELECTSTORE_ID,

    BANK_CODE,

    DBR_DATE,

    DATEPART(MONTH, DBR_DATE),

    DATEPART(DD, DBR_DATE),

    DATEPART(year,DBR_DATE),

    REPLACE(CONVERT(char(8),@Business_Date-1,1),'/', ''),

    TOTAL

    FROMCSDEPOSIT

    WHERE DBR_DATE+1=@BUSINESS_DATE

  • DECLARE @Store_ID varchar(10), @Business_Date datetime
    
    SET @Store_ID = ''
    SET @Business_Date='06/29/2002'

    IF @Store_ID <> ''
    SELECT Store_ID, Bank_Code, DBR_Date, Total
    FROM CSDEPOSIT
    WHERE Store_ID = @Store_ID AND DBR_DATE = REPLACE(CONVERT(char(8),@Business_Date-1,1),'/','')
    ELSE
    SELECT Store_ID, Bank_Code, DBR_Date, Total
    FROM CSDEPOSIT
    WHERE DBR_DATE = REPLACE(CONVERT(char(8),@Business_Date-1,1),'/','')

    --Jonathan



    --Jonathan

  • Thank you Jonathan, but this resulted in an out of range datetime value. Any ideas why that would come up?

  • quote:


    Thank you Jonathan, but this resulted in an out of range datetime value. Any ideas why that would come up?


    I suppose that just declaring and instantiating the variables (i.e. the first three lines of the script) causes the error... Did you SET DATEFORMAT DMY or use a language setting that does this?

    If the DBR_Date column is not a string but instead a temporal data type, then you would get an error like this due to implicit conversion; but then the whole excercise is unnecessary...

    --Jonathan



    --Jonathan

  • I think what you need is:

    DECLARE @Store_ID varchar(10), @Business_Date datetime, @Compare_date datetime

    SET @Store_ID = ''

    SET @Business_Date='01/04/2001' -- or whatever...

    set @Compare_date=dateadd(day, -1, @Business_Date)

    IF @Store_ID <> ''

    SELECT StoreID, Bank_Code, REPLACE(CONVERT(char(8),DBR_DATE, 3),'/','') as FormattedDate, Total FROM CSDEPOSIT

    WHERE StoreID = @Store_ID AND DBR_DATE = @Compare_date

    ELSE

    SELECT StoreID, Bank_Code, REPLACE(CONVERT(char(8),DBR_DATE, 3),'/','') as FormattedDate, Total FROM CSDEPOSIT

    WHERE DBR_DATE = @Compare_date

    You need to apply the REPLACE(CONVERT... stuff to the result column to get what you want.

    Is this OK?

  • Oops, got the wrong style in the CONVERT: it should be 1 instead of 3

  • How about this:

    DECLARE @STORE_ID VARCHAR (10), @BUSINESS_DATE DATETIME

    SET @STORE_ID = ''

    SET @BUSINESS_DATE = '06/30/2002'

    IF @STORE_ID <> ''

    BEGIN

    SELECT STORE_ID,

    BANK_CODE,

    DBR_DATE,

    CONVERT(char(6), REPLACE(CONVERT(char(8),DBR_DATE - 1,1),'/','')) as DBR_Date_Char,

    TOTAL

    FROM CSDEPOSIT

    WHERE @STORE_ID = STORE_ID

    AND DBR_DATE = @BUSINESS_DATE - 1

    END

    ELSE

    SELECT STORE_ID,

    BANK_CODE,

    DBR_DATE,

    CONVERT(char(6), REPLACE(CONVERT(char(8),DBR_DATE - 1,1),'/','')) as DBR_Date_Char,

    TOTAL

    FROM CSDEPOSIT

    WHERE DBR_DATE = @BUSINESS_DATE - 1

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply