CODEWITHSUNDEEP
sql-serversql-server-2008dateconverters
Ryan Neff
Ryan Neff

Reputation: 29

SQL Server 2008 R2 - Converting varchar to date using column as a variable

I have a column made up of dates, like "20141030", but they column property is varchar(8). I need to query this column in order to do comparisons on it to pull the correct information for a report.

This is my original code but returns no data:

select appt_date 
from appointments 
where convert(date,appt_date,112) 
between getdate() and dateadd(month,-24,getdate())

Same with this one:

select appt_date 
from appointments 
where convert(date,appt_date,112) 
between convert(date,getdate(),112) and dateadd(month,-24,convert(date,getdate(),112))

Then I saw an example that was posted so I tried it, but I don't know what to put as the expression for the convert function because it's not a constant. I want it to be a variable of the column, but it gives me the error below:

declare @ColumnName varchar(8)
declare @sql nvarchar(max)
set @ColumnName = 'appt_date' + convert(date,appt_date,112)
set @sql = 'select ' + @ColumnName + ' from appointments'

Msg 207, Level 16, State 1, Line 4 Invalid column name 'appt_date'.

So, how do I go about finding the varchar dates in the appt_date column that are 24 months back from today?

Upvotes: 0

Views: 687

Answers (2)

Svein Fidjestøl
Svein Fidjestøl

Reputation: 3206

Simply cast it to datetime like this

cast(appt_date as datetime)

or to date (only possible on SQL Server 2008 or later)

cast(appt_date as date)

so your query (including swapping the first and last values of the BETWEEN operator, so that the oldest comes first) will be something like

select appt_date
from appointments
where cast(appt_date as datetime)
      between dateadd(month,-24,getdate()) and getdate()

Also, note that getdate() actually returns a datetime that holds the current date and time, not just the current date. To get only the date (i.e. a datetime with 00:00:00.000 as the time part), use

dateadd(day,datediff(day,0,getdate()),0)

so your query would then be something like

select appt_date
from appointments
where cast(appt_date as datetime)
      between
      dateadd(month,-24,dateadd(day,datediff(day,0,getdate()),0))
      and
      dateadd(day,datediff(day,0,getdate()),0)

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

You need to put the earlier date FIRST in the BETWEEN clause:

 between dateadd(month,-24,getdate()) and getdate()

Upvotes: 1

Related Questions