Regarding dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Regarding dates

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-03-04 : 07:30:07

Mamata writes "Well i have a table that has already been created by somebody else,by giving the data type as varchar for a date column.My problem is we insert data to the table by entering from the asp page and since in asp page that field is entered as text it is no problem inserting that text to sql table(For ex: 01/01/2003 is entered from asp and is stored as varchar in the table).
My problem is that now i have to write a select statement inorder to pull those records that have dates today + 7 days ahead or dates between today and another 14 days ahead.
Is there a way i can leave the database as it is and in my query from asp get the records that have dates between today and untill 14 days ahead.
How do i convert varchar to date from the asp page.
here my problem is in the table the column is varchar, and i have to query that varchar column to get records for all dates that are between today and 3/9/2003.
Please help.
Thanks
Mamata
"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-04 : 07:51:36

Well, first off, I hope the dates were validated before being stored as a varchar().

The other problem might be if people are mixing the mm/dd/yy format and the dd/mm/yy format. Validation doesn't always help, because 2/1/2003 is valid in both formats but is definitely two different dates.

But assuming both of those are OK, just convert the varchar() field to a datetime datatype.

SELECT Date, (other fields)
FROM YourTable
WHERE Convert(DateTime,Date) BETWEEN GetDate() and GetDate()+7

I actually don't have access to BOL right now ... goes GetDate return a time also? If so, you will have to strip those off ...

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-04 : 09:53:20

But that will fail if you have a non date value. To eliminate non date values you need to add to the predicate (and you wanted 14 days ahead)

SELECT DateColumn, (other fields)
FROM YourTable
WHERE Convert(DateTime,DateColumn) BETWEEN GetDate() and GetDate()+14
AND IsDate(DateColumn) = 1

Good Luck

Brett

PS Any reason why the DBA doesn't want to store a valid date?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-03-04 : 10:16:29

quote:

But that will fail if you have a non date value.

Unfortunately, yours ~~will~~ may fail too -- the AND ISDATE(DateColumn) = 1 won't protect you from errors in the rest of the WHERE clause. You need to make sure the CONVERT is performed only for date values:

WHERE CASE WHEN IsDate(DateColumn) = 0 THEN NULL ELSE Convert(DateTime,DateColumn) END BETWEEN GetDate() and GetDate()+14

Edited by - Arnold Fribble on 03/04/2003 10:18:55

Edited by - Arnold Fribble on 03/04/2003 10:29:11

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-04 : 11:49:29

Right you are! I should be more careful before posting and check it out first. Thanks

Brett

8-)

CREATE TABLE DT_Test (Col1 varchar(255))
GO

INSERT INTO DT_Test (Col1) Values ('03/12/2003')
INSERT INTO DT_Test (Col1) Values ('DOG')
GO

Select IsDate(Col1), Col1 From DT_Test
Go

SELECT IsDate(Col1), Col1 FROM DT_Test
WHERE CASE WHEN IsDate(Col1) = 0 THEN NULL ELSE Convert(DateTime,Col1) END BETWEEN GetDate() and GetDate()+14
GO

Drop Table DT_Test
Go

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-03-04 : 15:29:23


-- A really strange one is that if you have a view like this:

CREATE VIEW DatesValuesOnly
AS
SELECT CONVERT(datetime, Col1) AS dt
FROM DT_Test
WHERE ISDATE(Col1) = 1
GO

-- Then a select like this can still get a conversion error:

SELECT dt
FROM DatesValuesOnly
WHERE dt BETWEEN GetDate() and GetDate()+14

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-03-04 : 15:50:50

Arnold you are the date master,

Your alias should be "Date Post Master General"

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-04 : 15:55:26

Wouldn't that be:

be "Date Post Master Yak General"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources