August 13, 2009 at 5:59 am
I am having trouble with the time format in Excel; I just want to view the time by itself not with the date or anything else…
My source query is as follows
SELECT
CONVERT(varchar,[TimeField1],108) 'TimeField1'
, CONVERT(varchar,[TimeField2],108) 'TimeField2'
, CONVERT(varchar,[[TimeField3],108) 'TimeField3'
FROM
myTable
When I run the query in SQL I get the following results
TimeField1 TimeField2 TimeField3
08:35:59 08:55:16 10:43:01
In the data conversion section of the data flow the following changes have been made
TimeField1 Unicode string [DT_WSTR], Length 50
TimeField2 Unicode string [DT_WSTR], Length 50
TimeField3 Unicode string [DT_WSTR], Length 50
The output file in Excel is as follows
TimeField1 TimeField2 TimeField3 Copy of TimeField1 Copy of TimeField2 Copy of TimeField3
00/01/1900 00/01/1900 00/01/1900 1899-12-30 08:35:59 1899-12-30 08:55:16 1899-12-30 10:43:01
As requested you mentioned to use the data viewer,
Before data conversion
TimeField1 TimeField2 TimeField3
30/12/1899 08:35:59 30/12/1899 08:55:16 30/12/1899 10:43:01
After data conversion
TimeField1 TimeField2 TimeField3 Copy of TimeField1 Copy of TimeField2 Copy of TimeField3
30/12/1899 08:35:59 30/12/1899 08:55:16 30/12/1899 10:43:01 1899-12-30 08:35:59 1899-12-30 08:55:16 1899-12-30 10:43:01
So there is something going wrong during the conversion, clearly you can see before the conversion the view of the results is totally different to the view I got initially from my SQL script. Which should only show the time (without date) but as you can see above the time is viewed along with the date field.
August 13, 2009 at 6:18 am
Your source data is already varchar (maybe Varchar(8) would be better), so why not use DSTR and avoid any need for data conversions?
The Data Viewer results you mention are consistent with what would appear if the field had a date datatype, not string.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 13, 2009 at 7:12 am
Phil Parkin (8/13/2009)
Your source data is already varchar (maybe Varchar(8) would be better), so why not use DSTR and avoid any need for data conversions?The Data Viewer results you mention are consistent with what would appear if the field had a date datatype, not string.
ok i'll change it to varchar(8)
how do i use DSTR and avoid data conversion?
August 13, 2009 at 7:22 am
Use the Advanced Editor for your source data and check/change the datatypes of the fields there.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 13, 2009 at 9:21 am
still not working
August 13, 2009 at 9:26 am
If I told you that my car was "not working", would you be able to tell me how to fix it?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply