SQL Date Query- Problem - 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)

SQL Date Query- Problem

Author

Topic

dupati1
Posting Yak Master

123 Posts

Posted - 2003-11-17 : 11:58:36

Hi all,

I am using ASP and SQL Server 2000. I have a database named REX_ENG in which i have a field (named ClosedDate set to varchar by mistake instead of datetime) and when i do the below query, i get random order.

Sql= "SELECT * FROM REX_ENG WHERE ActiveInd = 0 ORDER BY ClosedDate DESC"

How can i change the query to display the records in the order of ClosedDate? considering the ClosedDate is of varchar type.

Or can someone suggest me how can i change the type of the ClosedDate to datetime type without losing any data. I tried but i am getting errors.

Any suggestions.

Thanks
VJ

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-11-17 : 12:04:28

Assuming the varchar is truly a date...

...ORDER BY CONVERT(datetime, ClosedDate) DESC

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-17 : 12:07:24

Since it's varchar, you may have invalid dates

To see how many you have do this

SELECT * FROM REX_ENG WHERE IsDate(ClosedDate) = 0

And to sort by Date with what you have

SELECT * FROM REX_ENG
WHERE ActiveInd = 0 AND IsDate(ClosedDate) = 1
ORDER BY CONVERT(datetime,ClosedDate) DESC

Brett

8-)

dupati1
Posting Yak Master

123 Posts

Posted - 2003-11-17 : 12:14:39

Thanks Guys.

The Convert Function worked for me.

But is it ok if the ClosedDate field being of varchar type.

or will i encounter any problems in the future.

VJ

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-17 : 12:20:08

Only in that you can allow non date data...which will cause you to fail, iunless you use the ISDATE function to exclude them...which a whole other problem...

Does

SELECT * FROM REX_ENG WHERE ISDATE(ClosedDate) = 0

Return anything?

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-17 : 12:25:13

[code]
USE Northwind
GO

CREATE TABLE REX_ENG (ClosedDate varchar(26))
GO

INSERT INTO REX_ENG (ClosedDate)
SELECT CONVERT(varchar(26), Getdate()) UNION ALL
SELECT CONVERT(varchar(26), Getdate()) UNION ALL
SELECT CONVERT(varchar(26), Getdate()) UNION ALL
SELECT CONVERT(varchar(26), Getdate()) UNION ALL
SELECT CONVERT(varchar(26), Getdate()) UNION ALL
SELECT CONVERT(varchar(26), Getdate())
GO

SELECT * FROM REX_ENG
GO

sp_Help REX_ENG
GO

ALTER TABLE REX_ENG ALTER COLUMN ClosedDate datetime
GO

SELECT * FROM REX_ENG
GO

sp_Help REX_ENG
GO

DROP TABLE REX_ENG
GO

[/code]

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources