date convert - 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

date convert

Author

Topic

jhon11
Starting Member

42 Posts

Posted - 2007-12-17 : 09:26:57

hi,

I have a one column for date.
it displys in following manner.

1952-12-05 00:00:00

and i want to convert in

12/05/1952

can anybody help me out with this....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-17 : 09:29:39

Where do you want to show data?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 09:34:50

If you absolutely want to do it in SQL Server, use

SELECT CONVERT(VARCHAR, DateTimeValue, 103)

E 12°55'05.25"
N 56°04'39.16"

jhon11
Starting Member

42 Posts

Posted - 2007-12-17 : 09:37:55

i want to chage my that DOB column in to that type ....which shows me all date in that column in 12/05/1952 manner..instead of 1952-12-05 00:00:00 manner

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-17 : 09:39:19

quote:
Originally posted by jhon11

i want to chage my that DOB column in to that type ....which shows me all date in that column in 12/05/1952 manner..instead of 1952-12-05 00:00:00 manner

Dont worry on how it is stored in a table. When you want to show them just use format function

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 09:40:36

If the datatype for that columns is DATETIME, you can't change it!
Please read and learn about DATETIME datatype, and what a date really is.

To accomplish what you want, you need to change the column to varchar, which is REALLY BAD IDEA!
You better add a calculated column like this

ALTER TABLE Table1
ADD MyCol AS (CONVERT(VARCHAR(10), DOB, 103))

E 12°55'05.25"
N 56°04'39.16"

jhon11
Starting Member

42 Posts

Posted - 2007-12-17 : 09:52:05

i just want to show them in that formate....i m not worrying about how it stored in table....so can you help me with that....i just want to display result in that manner..i just want to show that kind of result...and my original column is in datetime formate...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 09:55:04

I did 12 minutes earlier.

E 12°55'05.25"
N 56°04'39.16"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-12-17 : 09:55:10

Again.. WHERE do you want to show them?
Crystal Reports?
Web page?
Sheet of paper?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

jhon11
Starting Member

42 Posts

Posted - 2007-12-17 : 09:58:33

i want to copy that into excel and make a ad-hoc report...
and convert(datetime,dob,103) is not working...my original datatype of that column is datetime

jhon11
Starting Member

42 Posts

Posted - 2007-12-17 : 10:00:19

thanks peso and thank you guys for ur help..it works....thanks a lot

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-17 : 11:22:01

Simply format your dates in Excel! Never, never, never format data in a in SQL and return VARCHARs instead of actual correct data types unless you have no other options -- in this case, Excel works fine!

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources