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