Select a text date

Question

Post reply

Select a text date

wdillon

SSChasing Mays

Points: 642
More actions
May 21, 2008 at 10:51 am

#191352

Hi All;

I have a table (tblnewsletter) that the date (SendDate) is stored in varchar(50) column. I'm trying to select rows from this table that are older than today ie (05/21/2008 01:01:01). I've played around with this syntax without any luck:

select * from TblNewsletter WHERE (CONVERT(DATETIME, SendDate, 0) < DATEADD(DAY, - 1, GETDATE()))

I've inclued the table in a compressd zip file that contains a csv format file as well, if that helps.

Thanks

Bill

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

When you say that the syntax you've come up with isn't working, what exactly do you mean? Do you get a syntax error on it? Does it not give you the results you want? Does it give you an error about "cannot convert varchar to datetime"? Something else?

The select looks like it should work. (I have to admit reluctance to download and open a zip file from an unknown source, so I can't really test it on your data.)

I just tested the query as follows:



create table #T (

ID int identity primary key,

SendDate varchar(50))

insert into #t (senddate)

select dateadd(day, number, '1/1/2000')

from dbo.numbers

select count(*) from #t

WHERE (CONVERT(DATETIME, SendDate, 0) < DATEADD(DAY, - 1, GETDATE()))

And it seems to run fine in that form.

One thing that occurs to me as a possible problem is that you state you want any data prior to today, but use dateadd(day, -1, getdate()) to get that. That will give you any data prior to the current time on the prior day.

For example, if it is 2:35 PM on 21 May 2008, then dateadd(day, -1, getdate()) will give me 2:35 PM on 20 May 2008.

If you want anything before midnight last night, use "dateadd(day, datediff(day, 0, getdate()), 0)", instead.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

wdillon SSChasing Mays Points: 642 More actions · Answer 2

Thanks and for the most part that works. Now I realize the the date in the form of DD/MM/YYYY. So, looking at other posting, I thought that I could do the following.

(CONVERT(DATETIME, (SUBSTRING(senddate,4,2) + "/" + SUBSTRING(senddate,1,2) + "/" + SUBSTRING(senddate,7,4)), 0) < DATEADD(DAY, - 1, GETDATE()))

When I do this, I get an error message saying that

Invalid column name '/'.

Thanks so far, I think we're close.

Bill

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Change the double quotes to single quotes...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions