We help IT Professionals succeed at work.

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
Comment
Watch Question

rewriting ansewer just a sec

Author

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


the where statement i am having problem
IT Director
CERTIFIED EXPERT
Commented:
SELECT * FROM TABLE WHERE CONVERT(DATETIME,deal_end,104)<=getdate()

Commented:
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

CERTIFIED EXPERT

Commented:
where convert(datetime, deal_end, 104) <= getdate()
deightonprog
CERTIFIED EXPERT

Commented:
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 FEA
CERTIFIED EXPERT

Commented:
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)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.