how to convert cyymmdd in oracle to datetime in sq

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

SSIS and Import/Export (2005)

how to convert cyymmdd in oracle to datetime in sq

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources