December 16, 2003 at 10:48 am
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
SELECT STORE_ID,
BANK_CODE,
DBR_DATE,
TOTAL
FROM CSDEPOSIT
WHERE (@STORE_ID = STORE_ID AND DBR_DATE+1=@BUSINESS_DATE)
END
ELSE
SELECT STORE_ID,
BANK_CODE,
DBR_DATE,
TOTAL
FROM CSDEPOSIT
WHERE (DBR_DATE+1=@BUSINESS_DATE)
Any feedback that anyone can offer would be greatly appreciated. Thanks
December 16, 2003 at 11:51 am
REPLACE(CONVERT(char(8),@Business_Date-1,1),'/','')
--Jonathan
--Jonathan
December 16, 2003 at 11:56 am
Where is this being placed within the code?
December 16, 2003 at 12:20 pm
Is there a way to remove the spaces between the month, day, and year?
December 16, 2003 at 12:58 pm
Make sure there's no space between the final two single quotes:
'' not ' '
That should work.
-SQLBill
December 16, 2003 at 1:30 pm
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))
SELECT STORE_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
FROM CSDEPOSIT
WHERE DBR_DATE+1=@BUSINESS_DATE
December 16, 2003 at 2:12 pm
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
December 16, 2003 at 2:27 pm
Thank you Jonathan, but this resulted in an out of range datetime value. Any ideas why that would come up?
December 16, 2003 at 3:32 pm
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
December 17, 2003 at 3:30 am
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?
December 17, 2003 at 3:31 am
Oops, got the wrong style in the CONVERT: it should be 1 instead of 3
December 17, 2003 at 7:53 am
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