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

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2006-08-11 : 12:26:29

In a fix to resolve a y2k issue my date format within a table has been changed, and I would like to display it in more of a user friendly way.

Currently 1YYMMDD and i would like it to display as mm/dd/yyyy
1060811 -> 08/11/06

Thanks!

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-08-11 : 12:36:11

Review the CONVERT function in Books Online.

There are many date formats to choose from, but as always, data manipulation for display purposes should be done in the presentation layer.

The date conversion in your case:


declare @Date datetime
set @Date = getdate()

select  @Date, convert(varchar, @Date, 101)

Nathan Skerl

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2006-08-11 : 12:54:59

Thanks for the responce.

I took a look at a SQL Book I have here and got a little better understanding of CONVERT and CAST.

Still having trouble though, inserting my field into the select statement.

Ex: SELECT dbo.CALLDETAIL.ORIG_DATE
FROM dbo.CALLDETAIL

I would like 08/11/(20)06 to display.

How can I merge ur post into my select statement.

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-08-11 : 13:12:14

tmaiden,

quote:

How can I merge ur post into my select statement.

Like this:

SELECT Convert(varchar, dbo.CALLDETAIL.ORIG_DATE, 101) FROM dbo.CALLDETAIL

Ken

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-11 : 13:13:17

Use front end application to format the date

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-14 : 04:05:40

What is 1 for? Denotes 20th century or 21st century?

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-14 : 10:27:18

[code]declare @date varchar(10)

select @date = '0060811'

select convert(varchar(10), convert(datetime, right(@date, 6), 12), 101)[/code]

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2006-08-16 : 11:14:19

Thanks khtan

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-16 : 11:20:15

interesing way to solve y2k bug though...

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-16 : 11:31:44

quote:
Originally posted by spirit1

interesing way to solve y2k bug though...

Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp

Yup. Seen this type of date format in a legacy system call SDB400.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources