[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql date query

Posted on 2012-08-14
8
Medium Priority
?
502 Views
Last Modified: 2012-08-15
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
0
Comment
Question by:JCWEBHOST
7 Comments
 
LVL 2

Expert Comment

by:kristof1104
ID: 38291365
rewriting ansewer just a sec
0
 

Author Comment

by:JCWEBHOST
ID: 38291374
SELECT SUBSTRING(description, 0, 40) as description, voucher_code, u_first_name, u_last_name from groupon


the where statement i am having problem
0
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 2000 total points
ID: 38291375
SELECT * FROM TABLE WHERE CONVERT(DATETIME,deal_end,104)<=getdate()
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:jonnidip
ID: 38291376
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

0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 38291377
where convert(datetime, deal_end, 104) <= getdate()
0
 
LVL 18

Expert Comment

by:deighton
ID: 38291444
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

0
 
LVL 11

Expert Comment

by:Lara F
ID: 38292544
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)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question