Convert Date - 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

General SQL Server Forums

New to SQL Server Programming

Convert Date

Author

Topic

bielen
Yak Posting Veteran

97 Posts

Posted - 2008-09-26 : 15:06:16

I have reportDate varchar field with the dates in the following format:

Apr 10 2008 2:43AM
Aug 2 2008 12:53AM
Jul 19 2008 2:50PM
Jun 21 2008 3:15PM
May 31 2008 12:19PM
Sep 6 2008 3:02PM

I try using:

Select CONVERT(varchar, reportDate, 109) AS reportDate
from reports
group by reportDate
order by reportDate

The results are still in alphabetical instead of date order? How can I get them to appear in date order?

Thanks

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-09-26 : 15:34:26

Select reportDate
from reports
order by Cast(reportDate as datetime) asc

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-27 : 01:54:20

quote:
Originally posted by bielen

I have reportDate varchar field with the dates in the following format:

Apr 10 2008 2:43AM
Aug 2 2008 12:53AM
Jul 19 2008 2:50PM
Jun 21 2008 3:15PM
May 31 2008 12:19PM
Sep 6 2008 3:02PM

I try using:

Select CONVERT(varchar, reportDate, 109) AS reportDatedisplayfrom reports
group by reportDate
order by reportDate

The results are still in alphabetical instead of date order? How can I get them to appear in date order?

Thanks

modify like above. It takes only converted varchar value for ordering instead of datetime value as alias has same name as original column.so give some other alias name.
b/w you shouldnt be trying to convert it to varchar if its for formatting. you should try to do this at your front end application.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-10-07 : 09:15:38

order by Cast(reportDate as datetime) asc

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-07 : 09:25:59

quote:
Originally posted by bielen

I have reportDate varchar field with the dates in the following format:

Apr 10 2008 2:43AM
Aug 2 2008 12:53AM
Jul 19 2008 2:50PM
Jun 21 2008 3:15PM
May 31 2008 12:19PM
Sep 6 2008 3:02PM

I try using:

Select CONVERT(varchar, reportDate, 109) AS reportDate
from reports
group by reportDate
order by reportDate

The results are still in alphabetical instead of date order? How can I get them to appear in date order?

Thanks

Always use proper DATETIME datatype to store dates and leave the formation to front end application

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-07 : 09:29:13

quote:
Originally posted by visakh16

quote:
Originally posted by bielen

I have reportDate varchar field with the dates in the following format:

Apr 10 2008 2:43AM
Aug 2 2008 12:53AM
Jul 19 2008 2:50PM
Jun 21 2008 3:15PM
May 31 2008 12:19PM
Sep 6 2008 3:02PM

I try using:

Select CONVERT(varchar, reportDate, 109) AS reportDatedisplayfrom reports
group by reportDate
order by reportDate

The results are still in alphabetical instead of date order? How can I get them to appear in date order?

Thanks

modify like above. It takes only converted varchar value for ordering instead of datetime value as alias has same name as original column.so give some other alias name.
b/w you shouldnt be trying to convert it to varchar if its for formatting. you should try to do this at your front end application.

The column is already in varchar datatype so you need to convert it to datetime in order by

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 09:39:51

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

quote:
Originally posted by bielen

I have reportDate varchar field with the dates in the following format:

Apr 10 2008 2:43AM
Aug 2 2008 12:53AM
Jul 19 2008 2:50PM
Jun 21 2008 3:15PM
May 31 2008 12:19PM
Sep 6 2008 3:02PM

I try using:

Select CONVERT(varchar, reportDate, 109) AS reportDatedisplayfrom reports
group by reportDate
order by reportDate

The results are still in alphabetical instead of date order? How can I get them to appear in date order?

Thanks

modify like above. It takes only converted varchar value for ordering instead of datetime value as alias has same name as original column.so give some other alias name.
b/w you shouldnt be trying to convert it to varchar if its for formatting. you should try to do this at your front end application.

The column is already in varchar datatype so you need to convert it to datetime in order by

Madhivanan

Failing to plan is Planning to fail

sory i missed that part. thought it was datetime seeing the convert in select

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources