sql query to order by datetime, field type varchar

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 2005 Forums

Transact-SQL (2005)

sql query to order by datetime, field type varchar

Author

Topic

leet_uuk
Starting Member

2 Posts

Posted - 2010-04-22 : 05:26:27

Hi. I have sql server 2005 database table with 'date_recieved' coloumn varchar [50] .i'm storing dates in dd/mm/yyyy format.

I'm writing a sql query to retrive rows in order by date but failing. Its something like

SELECT *
FROM question_entry
ORDER BY CONVERT(datetime, 'date_received', 1);

Can any one guide how to convert/order by data type from varchar to datetime on the fly in sql query??

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-22 : 05:34:19

One of the way

Declare @TestDate varchar(10)
set @TestDate= '05/02/2010'

Select convert(datetime,@TestDate,105)

In your query you can try:

SELECT *
FROM question_entry
ORDER BY convert(datetime,date_received,105)

I think it should work but i didn't tried it..

Check and let us know..

Regards,
Bohra

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-04-22 : 05:46:38

105 style is for Italian standard and it represents datetime as 'dd-mm-yy'.
103 style is for British/French standard and it represents datetime as 'dd/mm/yyyy'.
So you should use 103 style, and as Bohra replied in the other thread ([url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=143377[/url]) this is a duplicate thread.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-22 : 06:26:47

But the proper way is to store dates in DATETIME datatpye

Madhivanan

Failing to plan is Planning to fail

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-04-22 : 07:19:38

I mentioned that on the other thread too.

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-22 : 07:22:41

for getting dateandtime should use 101

SELECT *
FROM question_entry
ORDER BY convert(datetime,date_received,101)

try this.

Satya

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-22 : 07:47:39

quote:
Originally posted by satya068

for getting dateandtime should use 101

SELECT *
FROM question_entry
ORDER BY convert(datetime,date_received,101)

try this.

Satya

No, you can't use 101 for this: '31/01/2010'

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 08:44:25

if its just for ordering why dont you use iso formatting (121)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-22 : 08:56:52

quote:
Originally posted by visakh16

if its just for ordering why dont you use iso formatting (121)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Because it is a given format in character to format back to datetime!

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 09:04:09

quote:
Originally posted by webfred

quote:
Originally posted by visakh16

if its just for ordering why dont you use iso formatting (121)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Because it is a given format in character to format back to datetime!

No, you're never too old to Yak'n'Roll if you're too young to die.

oh ok I saw now

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources