-1

I have 3 columns that are defined as varchar and should be dates. They show up as Date1: 20171029, Date2: 20171027, Date3: 2017028. I want to convert them to 17-10-27. I also want to filter Date3 between 21-01-01 and Today + 30.

SELECT
    first_name,
    last_name
    date1,
    date2,
    date3
FROM 
    Data;

I have not tried to change the varchar to date yet.

I have tried to use CAST to filter the dates between 21-01-01 and today+30. It doesn't filter the dates.

SELECT
    first_name,
    last_name,
    department,
    date1,
    date2,
    date3
FROM 
    Data
WHERE 
    department = 'accounting'
    AND (CAST(date3 AS Date) >= (GETDATE() - 730) 
         AND CAST(date3 AS Date) <= (GETDATE() + 30));
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Stormy00
  • 1
  • 3
    Which dbms are you using? (Your query is using product specific functionality.) – jarlh May 17 '23 at 19:01
  • *It doesn't filter the dates.* Does it return anything? If yes, what? If not, does it return error? What error? – Shmiel May 17 '23 at 19:05
  • 1
    Does this answer your question? [Getdate(), -1 day](https://stackoverflow.com/questions/34738836/getdate-1-day) – Shmiel May 17 '23 at 19:07
  • There is no error. It just returns the same data and doesn't filter the dates. – Stormy00 May 17 '23 at 19:09
  • Shmiel. I am not sure what the -1 day means. Do I replace -730 with -1? I am new to SQL. – Stormy00 May 17 '23 at 19:13

1 Answers1

0

Use Convert and DateTime:

SELECT
    first_name,
    last_name,
    department,
    date1,
    date2,
    date3
FROM 
    Data
WHERE 
    department = 'accounting'
    AND
    Len([date3]) = 8
    AND
    Convert(DateTime, [date3], 112) 
        Between '2021-01-01' 
        And DateAdd(Day, 30, DateDiff(Day, 0, GetDate()))
Gustav
  • 53,498
  • 7
  • 29
  • 55