Converting Number & Combine Fields to make a 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

Other Forums

Other Topics

Converting Number & Combine Fields to make a Date

Author

Topic

Shane104
Starting Member

5 Posts

Posted - 2008-09-12 : 07:49:47

I have a database full of dates is the following format

YYYYMMDD
20090326
20030624
20051011
20030829

I need a query to convert these numbers into a date (DD/MM/YYYY)

I've managed to write the following query to extract the year, month and day but am unable to combine the three fields to create a date.

--------------
Select CONS0R.TravDat,

SubString(CONS0R.TravDat, 1, 4) As "YEAR",
SubString(CONS0R.TravDat, 5, 2) As "Month",
SubString(CONS0R.TravDat, 7, 2) As "DAY"

From CONS0R
--------------

Can anyone help?

Regards,
Shane.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-12 : 07:55:56

what is the data type of the column in your database ?

If it is string you can just to datetime data type using
convert(datetime, CONS0R.TravDat, 112)

and as for the format (DD/MM/YYYY), don't worry about it here. Just format it in your front end application where you are displaying the date.

If you can change the data type for the date column to date time instead of string type.

KH
[spoiler]Time is always against us[/spoiler]

Shane104
Starting Member

5 Posts

Posted - 2008-09-12 : 08:20:02

I'm not sure as i'm using this application to ODBC to the data source: http://www.sqlexcel.net and requiring this for reporting purposes.

If I enter the code you have suggested I get the following error:

Does this suggest that the application cannot handle this function?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-12 : 08:22:45

sorry, i missed out one parameter. see my edited post

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-12 : 08:27:59

quote:
Originally posted by Shane104

I have a database full of dates is the following format

YYYYMMDD
20090326
20030624
20051011
20030829

I need a query to convert these numbers into a date (DD/MM/YYYY)

I've managed to write the following query to extract the year, month and day but am unable to combine the three fields to create a date.

--------------
Select CONS0R.TravDat,

SubString(CONS0R.TravDat, 1, 4) As "YEAR",
SubString(CONS0R.TravDat, 5, 2) As "Month",
SubString(CONS0R.TravDat, 7, 2) As "DAY"

From CONS0R
--------------

Can anyone help?

Regards,
Shane.

It is already in a universal format
Cast it to datetime

Select CAST(CONS0R.TravDat as DATETIME) From CONS0R

and leave formation at front end

Madhivanan

Failing to plan is Planning to fail

Shane104
Starting Member

5 Posts

Posted - 2008-09-12 : 08:28:11

I receive the same error as before.... I don't think the application can handle it?? -

Shane104
Starting Member

5 Posts

Posted - 2008-09-12 : 08:34:27

Yet again it errors.....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-12 : 08:36:20

quote:
Originally posted by Shane104

Yet again it errors.....

Sybase ASA ? You are not using MS SQL Server ?

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-12 : 08:36:54

Select CAST(CONS0R.TravDat as DATETIME) From CONS0R
where isdate(CONS0R.TravDat)=1 and LEN(CONS0R.TravDat)=8

Run this in Query analyser and see

Madhivanan

Failing to plan is Planning to fail

Shane104
Starting Member

5 Posts

Posted - 2008-09-12 : 08:56:30

Yes this looks to be the problem - dodgy legacy systems! .... Would the code be different for Sybase ASA database? - Obviously it is?... Any suggestions or have I hit a deadend?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-12 : 09:03:32

This is a Microsoft SQL Server forum. For Sybase ASA, try dbforums.com

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources