Converting Numeric Field to Date

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)

Converting Numeric Field to Date

Author

Topic

kjauhar
Starting Member

6 Posts

Posted - 2007-08-22 : 16:33:14

Hi all,

I have a numeric field which represents a date. The data exists in the format :

20070517 (yyyymmdd) data type= numeric

I am trying to convert it to format mm/dd/yyyy by using the following sql:

select CONVERT (VARCHAR(10),MyDate,101)
from OOHEAD

it gives me back the same results (20070517) and not 05/17/2007. Can anyone please let me know the correct way to do this ASAP please? I looked at various examples online and it seems I am doing everything correct. any help is greatly appreciated.

Thanks,
KJ

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 16:40:28

SELECT CAST(CONVERT(VARCHAR, Col1) AS DATETIME)
FROM Table1

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

kjauhar
Starting Member

6 Posts

Posted - 2007-08-22 : 16:45:00

Peso,

Thanks for the reply. With your solution I get date along with time. I do not want time and I am specifically looking in format mm/dd/yy.
Please advise.

For date 20070517, expecting result= 05/17/07.

Thanks,
KJ

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 16:53:57

Then it is not a date anymore, it is a string that looks like a date.

declare @i int
set @i = 20070517 

select @i, convert(varchar, cast(convert(varchar, @i) as datetime), 101),convert(varchar, cast(convert(varchar, @i) as datetime), 1)

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 16:59:27

I am really confused right now.

In the original topic you wrote you want result as date. In your second post, you want it as string.
Also in your first post, you want year with century, and in your second post, you no longer want century with the year?

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

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-08-22 : 17:02:16

is this what you want?

SELECT CONVERT(varchar,convert(datetime,'20070517',101),101)

kjauhar
Starting Member

6 Posts

Posted - 2007-08-22 : 17:07:42

In your code when you set i=20070517, can I specify the column name there?

declare @i int
set @i = (SELECT Mydate from OOHEAD)
select @i, convert(varchar, cast(convert(varchar, @i) as datetime), 1)

I also wanted more fields from this table, can I select more fields along with that column's date conversion? I am new to this. I really appreciate ur help.

KJ

kjauhar
Starting Member

6 Posts

Posted - 2007-08-22 : 17:14:39

I am sorry for the confusion. Let me revamp my question clearly now:

I have a field in table which specifies a date but is stored with data type= numeric. The data exists the following way:

20070517.

I am trying to convert this numeric field to varchar such that I can format it the following way :05/17/07

Therefore, from original data 20070517 (numeric data type) I want the result to look like 05/17/07.

Thanks,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 17:15:43

I posted the solution to you at 08/22/2007 : 16:53:57
Please look it up!

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

kjauhar
Starting Member

6 Posts

Posted - 2007-08-22 : 17:23:25

In your code when you set i=20070517, can I specify the column name there?

like:

declare @i int
set @i = (SELECT Mydate from OOHEAD)
select @i, convert(varchar, cast(convert(varchar, @i) as datetime), 1)

as the field I am trying to convert exists in a table and has lots of values in format 20070517.

Thanks,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 17:24:56

Yes, of course!

select MyDate,
convert(varchar, cast(convert(varchar, MyDate) as datetime), 101),
convert(varchar, cast(convert(varchar, MyDate) as datetime), 1)
from oohead

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

kjauhar
Starting Member

6 Posts

Posted - 2007-08-22 : 17:32:17

Awesome!!!

That works like a CHARMMM!!

Thank you so much Peso :) for your great help. you rock!

kriti

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-24 : 01:47:59

Always use proper DATETIME datatype to store dates and let front end do the formation. Also create new DATETIME column and update dates there and use that column for further manipulations

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources