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.
| Author |
Topic |
|
maihabib
Starting Member
22 Posts |
 Posted - 2013-12-16 : 05:40:43
|
I have a date column in oracle database in format CYYMMDD where the first byte is the century (1=20, 0=19)
and another column (time)
i want to put it in sql table in format 2013-12-10 14:15:39.000
(date time)
i'm using ssis 2005 and can't manage to do it
please help
thanks,
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-16 : 05:51:17
|
do it like
(LEFT((DT_WSTR,30)DateColumn,1) == "1"? "20" : "19") + "-" + SUBSTRING((DT_WSTR,30)DateColumn,2,2) + "-" + SUBSTRING((DT_WSTR,30)DateColumn,4,2) + "-" + RIGHT((DT_WSTR,30)DateColumn,2) + " " + (DT_WSTR,30)TimeColumn
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
 |
|
|
maihabib
Starting Member
22 Posts |
Posted - 2013-12-16 : 06:29:25
|
Hi visakh16,
I'm working on ssis 2005, in a derived column. I get an error that the function LEFT is not recognized :(
waiting for your appreciated help,
thanks,
quote:
Originally posted by visakh16
do it like
(LEFT((DT_WSTR,30)DateColumn,1) == "1"? "20" : "19") + "-" + SUBSTRING((DT_WSTR,30)DateColumn,2,2) + "-" + SUBSTRING((DT_WSTR,30)DateColumn,4,2) + "-" + RIGHT((DT_WSTR,30)DateColumn,2) + " " + (DT_WSTR,30)TimeColumn
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-16 : 06:30:42
|
ah ok..just modify it as below
(SUBSTRING((DT_WSTR,30)DateColumn,1,1) == "1"? "20" : "19") + SUBSTRING((DT_WSTR,30)DateColumn,2,2) + "-" + SUBSTRING((DT_WSTR,30)DateColumn,4,2) + "-" + RIGHT((DT_WSTR,30)DateColumn,2) + " " + (DT_WSTR,30)TimeColumn
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
maihabib
Starting Member
22 Posts |
Posted - 2013-12-16 : 06:41:04
|
I'm sorry but i found that the date and time column in oracle are numeric like below
date column 2131208
time column 104146
how can i write the expression now
apologies for bothering you
quote:
Originally posted by visakh16
ah ok..just modify it as below
(SUBSTRING((DT_WSTR,30)DateColumn,1,1) == "1"? "20" : "19") + SUBSTRING((DT_WSTR,30)DateColumn,2,2) + "-" + SUBSTRING((DT_WSTR,30)DateColumn,4,2) + "-" + RIGHT((DT_WSTR,30)DateColumn,2) + " " + (DT_WSTR,30)TimeColumn
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-16 : 06:55:22
|
what does 2131208 represent? as per your earlier explanation first digit was supposed to be 0 or 1.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
maihabib
Starting Member
22 Posts |
Posted - 2013-12-16 : 07:00:50
|
sorry . you are right first digit is 1 or 0
1 for 20th and 0 for 19th
quote:
Originally posted by visakh16
what does 2131208 represent? as per your earlier explanation first digit was supposed to be 0 or 1.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
maihabib
Starting Member
22 Posts |
Posted - 2013-12-16 : 07:03:34
|
so date 1131208
means 2013-12-08 00:00:00.000
and time means 10:41:46.000
quote:
Originally posted by maihabib
sorry . you are right first digit is 1 or 0
1 for 20th and 0 for 19th
quote:
Originally posted by visakh16
what does 2131208 represent? as per your earlier explanation first digit was supposed to be 0 or 1.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-16 : 07:40:34
|
then my previous suggestion should be enough
(SUBSTRING((DT_WSTR,30)DateColumn,1,1) == "1"? "20" : "19") + SUBSTRING((DT_WSTR,30)DateColumn,2,2) + "-" + SUBSTRING((DT_WSTR,30)DateColumn,4,2) + "-" + RIGHT((DT_WSTR,30)DateColumn,2) + " " + + " " + SUBSTRING((DT_WSTR,30) TimeColumn ,1,2) + ":" +SUBSTRING((DT_WSTR,30) TimeColumn ,3,2) + ":" + RIGHT((DT_WSTR,30) TimeColumn,2)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
maihabib
Starting Member
22 Posts |
Posted - 2013-12-16 : 08:24:46
|
million thanks for your help.
another small thing.
i want to insert it into datetime column in sql table but it failed with error code code 0xC0049064
quote:
Originally posted by visakh16
then my previous suggestion should be enough
(SUBSTRING((DT_WSTR,30)DateColumn,1,1) == "1"? "20" : "19") + SUBSTRING((DT_WSTR,30)DateColumn,2,2) + "-" + SUBSTRING((DT_WSTR,30)DateColumn,4,2) + "-" + RIGHT((DT_WSTR,30)DateColumn,2) + " " + + " " + SUBSTRING((DT_WSTR,30) TimeColumn ,1,2) + ":" +SUBSTRING((DT_WSTR,30) TimeColumn ,3,2) + ":" + RIGHT((DT_WSTR,30) TimeColumn,2)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-16 : 10:25:58
|
cast it to (DT_DBTIMESTAMP) and it should work
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
maihabib
Starting Member
22 Posts |
Posted - 2013-12-17 : 06:40:57
|
i wrote it in the derived column as below but got an error:
(DT_DBTIMESTAMP)((SUBSTRING((DT_WSTR,30)DATE_STAMP,1,1) == "1" ? "20" : "19") + SUBSTRING((DT_WSTR,30)DATE_STAMP,2,2) + "-" + SUBSTRING((DT_WSTR,30)DATE_STAMP,4,2) + "-" + RIGHT((DT_WSTR,30)DATE_STAMP,2)) + " " SUBSTRING((DT_WSTR,30)TIME_STAMP,1,2) + ":" + SUBSTRING((DT_WSTR,30)TIME_STAMP,3,2) + ":" + RIGHT((DT_WSTR,30)TIME_STAMP,2))
WHAT'S THE CORRECT expression please
quote:
Originally posted by visakh16
cast it to (DT_DBTIMESTAMP) and it should work
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-17 : 07:45:05
|
quote:
Originally posted by maihabib
i wrote it in the derived column as below but got an error:
(DT_DBTIMESTAMP)((SUBSTRING((DT_WSTR,30)DATE_STAMP,1,1) == "1" ? "20" : "19") + SUBSTRING((DT_WSTR,30)DATE_STAMP,2,2) + "-" + SUBSTRING((DT_WSTR,30)DATE_STAMP,4,2) + "-" + RIGHT((DT_WSTR,30)DATE_STAMP,2)) + " " SUBSTRING((DT_WSTR,30)TIME_STAMP,1,2) + ":" + SUBSTRING((DT_WSTR,30)TIME_STAMP,3,2) + ":" + RIGHT((DT_WSTR,30)TIME_STAMP,2))
WHAT'S THE CORRECT expression please
quote:
Originally posted by visakh16
cast it to (DT_DBTIMESTAMP) and it should work
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Are you trying to replace an existing column with this expression? You need to give option Add as a new column and finally in mapping use this new column to map to datetime typed column.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
maihabib
Starting Member
22 Posts |
Posted - 2013-12-17 : 08:06:24
|
yes, i chose add as new column
i'm afraid the issue is with the expression
casting with (DT_DBTIMESTAMP)only works with the date part and fails with the time part added.
what shall i do?
thanks,
waiting for your reply
quote:
Originally posted by visakh16
quote:
Originally posted by maihabib
i wrote it in the derived column as below but got an error:
(DT_DBTIMESTAMP)((SUBSTRING((DT_WSTR,30)DATE_STAMP,1,1) == "1" ? "20" : "19") + SUBSTRING((DT_WSTR,30)DATE_STAMP,2,2) + "-" + SUBSTRING((DT_WSTR,30)DATE_STAMP,4,2) + "-" + RIGHT((DT_WSTR,30)DATE_STAMP,2)) + " " SUBSTRING((DT_WSTR,30)TIME_STAMP,1,2) + ":" + SUBSTRING((DT_WSTR,30)TIME_STAMP,3,2) + ":" + RIGHT((DT_WSTR,30)TIME_STAMP,2))
WHAT'S THE CORRECT expression please
quote:
Originally posted by visakh16
cast it to (DT_DBTIMESTAMP) and it should work
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Are you trying to replace an existing column with this expression? You need to give option Add as a new column and finally in mapping use this new column to map to datetime typed column.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-17 : 08:10:12
|
quote:
Originally posted by maihabib
yes, i chose add as new column
i'm afraid the issue is with the expression
casting with (DT_DBTIMESTAMP)only works with the date part and fails with the time part added.
what shall i do?
thanks,
waiting for your reply
quote:
Originally posted by visakh16
quote:
Originally posted by maihabib
i wrote it in the derived column as below but got an error:
(DT_DBTIMESTAMP)((SUBSTRING((DT_WSTR,30)DATE_STAMP,1,1) == "1" ? "20" : "19") + SUBSTRING((DT_WSTR,30)DATE_STAMP,2,2) + "-" + SUBSTRING((DT_WSTR,30)DATE_STAMP,4,2) + "-" + RIGHT((DT_WSTR,30)DATE_STAMP,2)) + " " SUBSTRING((DT_WSTR,30)TIME_STAMP,1,2) + ":" + SUBSTRING((DT_WSTR,30)TIME_STAMP,3,2) + ":" + RIGHT((DT_WSTR,30)TIME_STAMP,2))
WHAT'S THE CORRECT expression please
quote:
Originally posted by visakh16
cast it to (DT_DBTIMESTAMP) and it should work
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Are you trying to replace an existing column with this expression? You need to give option Add as a new column and finally in mapping use this new column to map to datetime typed column.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
does your timepart come with or without : characetrs?
Also is it consistent across all rows?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
maihabib
Starting Member
22 Posts |
Posted - 2013-12-17 : 08:55:23
|
the original time is int as 122959 means 12:29:59
it's not consitent. it's different in every row
and i want the final datetime to look like 2013-12-10 12:29:59.000
quote:
Originally posted by visakh16
quote:
Originally posted by maihabib
yes, i chose add as new column
i'm afraid the issue is with the expression
casting with (DT_DBTIMESTAMP)only works with the date part and fails with the time part added.
what shall i do?
thanks,
waiting for your reply
quote:
Originally posted by visakh16
quote:
Originally posted by maihabib
i wrote it in the derived column as below but got an error:
(DT_DBTIMESTAMP)((SUBSTRING((DT_WSTR,30)DATE_STAMP,1,1) == "1" ? "20" : "19") + SUBSTRING((DT_WSTR,30)DATE_STAMP,2,2) + "-" + SUBSTRING((DT_WSTR,30)DATE_STAMP,4,2) + "-" + RIGHT((DT_WSTR,30)DATE_STAMP,2)) + " " SUBSTRING((DT_WSTR,30)TIME_STAMP,1,2) + ":" + SUBSTRING((DT_WSTR,30)TIME_STAMP,3,2) + ":" + RIGHT((DT_WSTR,30)TIME_STAMP,2))
WHAT'S THE CORRECT expression please
quote:
Originally posted by visakh16
cast it to (DT_DBTIMESTAMP) and it should work
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Are you trying to replace an existing column with this expression? You need to give option Add as a new column and finally in mapping use this new column to map to datetime typed column.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
does your timepart come with or without : characetrs?
Also is it consistent across all rows?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-17 : 09:00:03
|
I was asking on format not actual value. So all rows its without : characetrs?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
maihabib
Starting Member
22 Posts |
Posted - 2013-12-17 : 09:04:24
|
yes, all rows without :
quote:
Originally posted by visakh16
I was asking on format not actual value. So all rows its without : characetrs?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
|
|
|
|
|
how to convert cyymmdd in oracle to datetime in sq