asked on

sql date query

Hey guys i have a table and want to find out if my voucher is Expired or not in a sql query

i have a coloum called deal_end which as varchar(50) data type

and the format the date is entered is like this 13.07.2012 which goes date month year

now i need a sql query to select all records where the deal_end <= todays date ?

please help

kristof1104

rewriting ansewer just a sec

JCWEBHOST

ASKER

SELECT SUBSTRING(description, 0, 40) as description, voucher_code, u_first_name, u_last_name from groupon

the where statement i am having problem

ASKER CERTIFIED SOLUTION

EvilPostIt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jonnidip

You can cast your value to date:

declare @test as table
(
	dt varchar(50)
)

insert into @test
select '31.12.2011'
insert into @test
select '01.06.2012'
insert into @test
select '31.12.2012'

select dt from @test
where cast(dt as date) < getdate()

Open in new window

Lee

where convert(datetime, deal_end, 104) <= getdate()

deighton

you could run into trouble casting date strings in UK format into DATETIME

how about

SELECT * FROM (
     SELECT * FROM (
             select *,  substring(deal_end,7,4) + substring(deal_end,4,2) + left(deal_end,2) DS   from    groupon) SQ 
          WHERE ISDATE(SQ.DS) = 1) SQ2 
WHERE CAST(SQ2.DS AS DATETIME) <= getdate()

Open in new window

Lara F

You can use dateformat to specify format you use

Here is sample
SET DATEFORMAT dmy
GO
DECLARE @datevar varchar(50)
set @datevar= '10.11.2012'
SELECT CONVERT(varchar(50), convert(datetime,@datevar),109)

SET DATEFORMAT mdy
SELECT CONVERT(varchar(50), convert(datetime,@datevar),109)

set @datevar = '10.11.12'
SET DATEFORMAT ydm
SELECT CONVERT(varchar(50), convert(datetime,@datevar),109)