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

Transact-SQL (2005)

QUERY PROBLEM

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-02-19 : 00:56:38

SELECT DISTINCT ltrim(rtrim(convert(varchar,DATE_OF_CREATION,105))) + ' ' +
rtrim(ltrim(replace(replace(substring(convert(varchar,DATE_OF_CREATION,9),13,8),'AM',' AM'),
'PM',' PM'))) AS DATE_OF_CREATION FROM PatientInsu WHERE PATIENT_ID = '211167'
ORDER BY DATE_OF_CREATION DESC

getting output as

19-02-2010 11:01:00

but i want it to be 19-02-2010 11:01:00 AM also

how to do

for mat like
DD-MM-YYYY HH:MI:SS AM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 01:03:59

try to do it at your front end if you can as this is an formatting issue
If you still want to do it in T-SQL use CONVERT as below

SELECT CONVERT(varchar(11),DATE_OF_CREATION,105) + ' ' + STUFF(RIGHT(CONVERT(varchar(30),DATE_OF_CREATION,109),15),9,4,'') FROM Table

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-19 : 01:04:23

You stopped as soon as you got answer

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139987
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139742&whichpage=2

Madhivanan

Failing to plan is Planning to fail

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-02-19 : 01:13:49

Its working.thanks for that but can't we write in my query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 01:16:22

quote:
Originally posted by rajasekhar857

can't we write in my query?

Is it so difficult to apply the logic I've shown in your query? At least do an effort to copy paste

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

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-02-19 : 01:20:16

ok thank you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-19 : 01:21:16

quote:
Originally posted by visakh16

quote:
Originally posted by rajasekhar857

can't we write in my query?

Is it so difficult to apply the logic I've shown in your query? At least do an effort to copy paste

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

He doesn't have an ability to Learn

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-19 : 01:21:57

Also see that He didn't respond me

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 01:37:34

quote:
Originally posted by madhivanan

Also see that He didn't respond me

Madhivanan

Failing to plan is Planning to fail

Have he ever did that?

The next time he come back will be for starting another thread

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-19 : 01:58:38

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

Also see that He didn't respond me

Madhivanan

Failing to plan is Planning to fail

Have he ever did that?
The next time he come back will be for starting another thread

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

No doubt

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources