February 26, 2019 at 7:43 am
Hi,
I have a variable that shows the date as month and year only. It has data format like 2018-11. I want to show the last six months data from this variable value. So if the variable is 2019-02 it should show data from 2018-09 to 2019-02. How do i get to past 6 month date.Current variable as below-
declare @date varchar(100)
set @date='2019-02'
Thanks
February 26, 2019 at 7:47 am
Use date variable instead and then add a couple of extra clauses to your WHERE. In very simple Pseudo-SQL terms:
DECLARE @date varchar(100);
SET @date='2019-02';
DECLARE @StartDate date = DATEADD(MONTH, -5, CONVERT(date,@date + '-01'));
SELECT {YourColumns}
FROM {Your Tables}
WHERE {Where Criteria}
AND {YourDateColumn} >= @StartDate
AND {YourDateColumn} < DATEADD(MONTH, 6, @StartDate);
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 26, 2019 at 7:55 am
Thom A - Tuesday, February 26, 2019 7:47 AMUse date variable instead and then add a couple of extra clauses to your WHERE. In very simple Pseudo-SQL terms:
DECLARE @date varchar(100);
SET @date='2019-02';DECLARE @StartDate date = DATEADD(MONTH, -5, CONVERT(date,@date + '-01'));
SELECT {YourColumns}
FROM {Your Tables}
WHERE {Where Criteria}
AND {YourDateColumn} >= @StartDate
AND {YourDateColumn} < DATEADD(MONTH, 6, @StartDate);
I get this error-
Conversion failed when converting date and/or time from character string.
As per the query above-"YourDateColumn" is also varchar for me. It contains only year and month(2019-02)
February 26, 2019 at 8:12 am
Papil - Tuesday, February 26, 2019 7:55 AMI get this error-
Conversion failed when converting date and/or time from character string.As per the query above-"YourDateColumn" is also varchar for me. It contains only year and month(2019-02)
Then your column isn't a date column. you didn't state that your column isn't a date, only that your variable wasn't. The better question here is, why are you storing dates as a varchar? You should always use an appropriate datatype for your data.
The fact that you are getting the error means that you have values in your date column that can't be implicitly converted to a date; that's a problem when you're trying to get data from a table between 2 dates. What sort of values does the below return?SELECT {YourDateColumn}
FROM {YourTable}
WHERE TRY_CONVERT(date,{YourDateColumn}) IS NULL;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 26, 2019 at 8:17 am
Thom A - Tuesday, February 26, 2019 8:12 AMPapil - Tuesday, February 26, 2019 7:55 AMI get this error-
Conversion failed when converting date and/or time from character string.As per the query above-"YourDateColumn" is also varchar for me. It contains only year and month(2019-02)
Then your column isn't a date column. you didn't state that your column isn't a date, only that your variable wasn't. The better question here is, why are you storing dates as a varchar? You should always use an appropriate datatype for your data.
The fact that you are getting the error means that you have values in your date column that can't be implicitly converted to a date; that's a problem when you're trying to get data from a table between 2 dates. What sort of values does the below return?
SELECT {YourDateColumn}
FROM {YourTable}
WHERE TRY_CONVERT(date,{YourDateColumn}) IS NULL;
It will be varchar only.
Thom A - Tuesday, February 26, 2019 8:12 AMPapil - Tuesday, February 26, 2019 7:55 AMI get this error-
Conversion failed when converting date and/or time from character string.As per the query above-"YourDateColumn" is also varchar for me. It contains only year and month(2019-02)
Then your column isn't a date column. you didn't state that your column isn't a date, only that your variable wasn't. The better question here is, why are you storing dates as a varchar? You should always use an appropriate datatype for your data.
The fact that you are getting the error means that you have values in your date column that can't be implicitly converted to a date; that's a problem when you're trying to get data from a table between 2 dates. What sort of values does the below return?
SELECT {YourDateColumn}
FROM {YourTable}
WHERE TRY_CONVERT(date,{YourDateColumn}) IS NULL;
Sorry i missed to mention that. Thats how the data is stored as per the requirements.
Above query return data as -2019-02
February 26, 2019 at 8:44 am
Is the data in the column stored at "2019-01"?
You are going to have problems at some point trying to do range work. What I'd suggest is you create a computed column that has a date value. Use DATEFROMPARTS() to do this.
ALTER TABLE dbo.Inventory
ADD RealDate AS DATEFROMPARTS(SUBSTRING(DateCaptured, 1, 4), SUBSTRING(DateCaptured, CHARINDEX('-',DateCaptured)+1,2),1)
February 26, 2019 at 8:44 am
Papil - Tuesday, February 26, 2019 8:17 AMIt will be varchar only.Sorry i missed to mention that. Thats how the data is stored as per the requirements.
Above query return data as -2019-02
Provided that all your varchar dates are of the format yyyy-MM then this "should" work:
DECLARE @date varchar(100);
SET @date='2019-02';
DECLARE @StartDate date = DATEADD(MONTH, -5, CONVERT(date,@date + '-01'));
DECLARE @StartVar varchar(7) = STUFF(CONVERT(varchar(6),@StartDate,112),5,0,'-'),
@EndVar varchar(7) = STUFF(CONVERT(varchar(6),DATEADD(MONTH,6,@StartDate),112),5,0,'-');
SELECT {YourColumns}
FROM {Your Tables}
WHERE {Where Criteria}
AND {YourDateColumn} >= @StartVar
AND {YourDateColumn} < @EndVar;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 26, 2019 at 8:46 am
Steve Jones - SSC Editor - Tuesday, February 26, 2019 8:44 AMIs the data in the column stored at "2019-01"?You are going to have problems at some point trying to do range work. What I'd suggest is you create a computed column that has a date value. Use DATEFROMPARTS() to do this.
ALTER TABLE dbo.Inventory
ADD RealDate AS DATEFROMPARTS(SUBSTRING(DateCaptured, 1, 4), SUBSTRING(DateCaptured, CHARINDEX('-',DateCaptured)+1,2),1)
Yes data in the column will always be varchar like 2019-02. and it should show data from 2018-09 till 2019-02
February 26, 2019 at 8:47 am
Steve Jones - SSC Editor - Tuesday, February 26, 2019 8:44 AMIs the data in the column stored at "2019-01"?You are going to have problems at some point trying to do range work. What I'd suggest is you create a computed column that has a date value. Use DATEFROMPARTS() to do this.
ALTER TABLE dbo.Inventory
ADD RealDate AS DATEFROMPARTS(SUBSTRING(DateCaptured, 1, 4), SUBSTRING(DateCaptured, CHARINDEX('-',DateCaptured)+1,2),1)
I agree that having a proper date exposed in the data is a far better idea here. It's probably worth adding PERSISTED to this, as the value is deterministic, and then any indexes can include the column as well.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 26, 2019 at 9:16 am
Thom A - Tuesday, February 26, 2019 8:44 AMPapil - Tuesday, February 26, 2019 8:17 AMIt will be varchar only.Sorry i missed to mention that. Thats how the data is stored as per the requirements.
Above query return data as -2019-02Provided that all your varchar dates are of the format yyyy-MM then this "should" work:
DECLARE @date varchar(100);
SET @date='2019-02';DECLARE @StartDate date = DATEADD(MONTH, -5, CONVERT(date,@date + '-01'));
DECLARE @StartVar varchar(7) = STUFF(CONVERT(varchar(6),@StartDate,112),5,0,'-'),
@EndVar varchar(7) = STUFF(CONVERT(varchar(6),DATEADD(MONTH,6,@StartDate),112),5,0,'-');SELECT {YourColumns}
FROM {Your Tables}
WHERE {Where Criteria}
AND {YourDateColumn} >= @StartVar
AND {YourDateColumn} < @EndVar;
Thanks this worked.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply