?
Solved

How to import "hhh:mm:ss" time field into SQL?

Posted on 2003-03-27
23
Medium Priority
?
431 Views
Last Modified: 2012-06-27
I am trying to import a text file into SQL 2k and I am stuck on the time field.  This particular time field is in the format hhh:mm:ss.  SQL doesn't seem to like this format when using the DateTime Transform.  Does anyone know how I can import this field???
0
Comment
Question by:watbs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 9
  • 2
23 Comments
 
LVL 1

Expert Comment

by:JWT
ID: 8220204
Are you trying to drop the time portion of this?

If so you can try an Active X transform.  Simply import the column to a variable, then clip the string to the size you want.  

eg strLongDate = DTSsource("textdate")

strShortDate = left ( strLongDate , 8 ) 'this number may vary depending on the date format

DTSDestination("newdate") = strShortDate
0
 
LVL 1

Author Comment

by:watbs
ID: 8227054
Thanks JWT - This almost got me there.  Now I receive errors if any of the datetime fields are Null.  In SQL I'm allowing Nulls.  Any ideas?
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 8227158
You can import in date type field datatype your text and use Convert function or in Varchar data type field:

See example for datetime:

create table #test (dt datetime)

insert into #test values('14:07:15')
insert into #test values('14:08:25')
insert into #test values('14:09:35')
insert into #test values('14:10:45')

select * from #test
/*

dt
---
1900-01-01 14:07:15.000
1900-01-01 14:08:25.000
1900-01-01 14:09:35.000
1900-01-01 14:10:45.000


*/


select convert (varchar(8), dt,108) from #test

/*
14:07:15
14:08:25
14:09:35
14:10:45

*/
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 1

Expert Comment

by:JWT
ID: 8227265
OK...  So if I read you right, you are receiving errors during the transform portion (and not the insert portion)

If that is true then you need to check for nulls before you do any string manipulations.  Use the IsNull function in vbScript.  It returns a boolean.  

EG

If ISNull (dateInQuestion) then
    DTSDestination ("DateHome") = dateInQuestion
Else
    'String manipulation goes here
End IF

BTW - If it is actually during the insert portion that means your destination is not accepting nulls, so you need to go into the table design and make your adjustment there.
0
 
LVL 1

Author Comment

by:watbs
ID: 8238993
Okay JWT.  You are correct I am doing this during the transform.  When I tried your example I receive all NULLS in my SQL after the transfrom and insert.  Perhaps my syntax is off?  Would you mind giving me an example if the field I'm trying to import is MYDATE in the text file(source)and MYDATE in SQL.  Also I'm unclear on the string manipulation (I believe I have to conver string to datetime.
I promise that's my last comment :)

Thanks
0
 
LVL 1

Expert Comment

by:JWT
ID: 8239275
So you don't want nulls in your final column?  If not, what do you want to put in there.  A default?

If so replace:
DTSDestination ("DateHome") = dateInQuestion
with
DTSDestination ("DateHome") = '01/01/2000' 'Default Date

today's date?

If so replace:
DTSDestination ("DateHome") = dateInQuestion
with
DTSDestination ("DateHome") = Now()

As far as the string manipulation you will want to use the manipulation I showed you in the first comment

eg strLongDate = DTSsource("textdate")

strShortDate = left ( strLongDate , 8 ) 'this number may vary depending on the date format

DTSDestination("newdate") = strShortDate

That should get you there.  You shouldn't need to convert to datetime, SQL will do it for you.  
0
 
LVL 1

Author Comment

by:watbs
ID: 8239620
Thanks for the quick response JWT.
I tried your code and I received an error...  "DTS transformation encountered an invalid data value for 'REQD_DATE' destination column.  So I changed the strShortDate to datetime and it ran without error although only <NULL> showed up in the column in SQL.

I should probably explain myself better.  Basically I want to import a column from a text file into a column in SQL which is a datatype of datetime.
When I try a "DateTime String" transfer in SQL this works fine.  The problem is if there is no data in my source text file for that column, SQL will error.
0
 
LVL 1

Expert Comment

by:JWT
ID: 8240132
Can you tell me what you want in your final column?  Did you go for the default or the current date - or do you wish to transfer the null over?

Let me give you some background here.  DTS uses a scripting language - you are probably using vbScript.  In VBScript there are no datatypes except variant - which is a catch-all datatype.

You date transform will not work because it does not know how to process a null result.  So your option is then to use the Active X script.  You can handle nulls here, but it is a little more difficult, and requires examining the data every step of the way.  That is where the ISNull function comes in.  It allows you to look at a variable and see if it is null.  If it is the function returns True.

You may also want to use the IsDate function.  You can use this to tell if a particular variable is convertable to a date.  The thing to remember is that variables in VBScript don't neccessarily translate to the right data types in SQL - you have to check them to be sure.

However - for any of this to work, you need to know what you want your data to look like when you are done.  You have nulls in your base data on the text table, and you need a strategy for how to handle them.  Once you decide what you want done with them then we can figure how how to get there. ;-)  I am getting conflicting info about how comfortable you are with Nulls in your final database in this field.
0
 
LVL 1

Expert Comment

by:JWT
ID: 8240172
Can you tell me what you want in your final column?  Did you go for the default or the current date - or do you wish to transfer the null over?

Let me give you some background here.  DTS uses a scripting language - you are probably using vbScript.  In VBScript there are no datatypes except variant - which is a catch-all datatype.

You date transform will not work because it does not know how to process a null result.  So your option is then to use the Active X script.  You can handle nulls here, but it is a little more difficult, and requires examining the data every step of the way.  That is where the ISNull function comes in.  It allows you to look at a variable and see if it is null.  If it is the function returns True.

You may also want to use the IsDate function.  You can use this to tell if a particular variable is convertable to a date.  The thing to remember is that variables in VBScript don't neccessarily translate to the right data types in SQL - you have to check them to be sure.

However - for any of this to work, you need to know what you want your data to look like when you are done.  You have nulls in your base data on the text table, and you need a strategy for how to handle them.  Once you decide what you want done with them then we can figure how how to get there. ;-)  I am getting conflicting info about how comfortable you are with Nulls in your final database in this field.
0
 
LVL 1

Author Comment

by:watbs
ID: 8240223
Okay.  I don't care if there are Nulls in the final SQL table or not.  I just want the data to come over without error when there is no data for that column in my text file.  Which ever way is the easist is fine with me.  
0
 
LVL 1

Author Comment

by:watbs
ID: 8240244
The format of the date field in my text file is yyyymmdd and the format of the date field in my SQL table is mm/dd/yyyy.  I'm truncating the table and then importing the data.  So from what you're saying I'll definately have to use the ActiveXscript to transform this date field from text to SQL because there are Null values.
0
 
LVL 1

Expert Comment

by:JWT
ID: 8240253
OK - so do this:

If ISNull (dateInQuestion) then
   DTSDestination ("DateHome") = dateInQuestion
Else
   strLongDate = DTSsource("textdate")
   strShortDate = left ( strLongDate , 8 ) 'this number may vary depending on the date format
   DTSDestination("newdate") = strShortDate
end if
0
 
LVL 1

Expert Comment

by:JWT
ID: 8240445
Your end result should look something like this:

'**********************************************************************
'  Visual Basic Transformation Script
'************************************************************************

'  Copy each source column to the destination column
Function Main()

Dim dateInQuestion

dateInQuestion = DTSsource("textdate")

If ISNull (dateInQuestion) then
  DTSDestination ("DateHome") = dateInQuestion
Else
  strLongDate = DTSsource("textdate")
  strShortDate = left ( strLongDate , 8 )
  DTSDestination("newdate") = strShortDate
end if

Main = DTSTransformStat_OK

End Function
0
 
LVL 1

Author Comment

by:watbs
ID: 8240481
Okay, I ran your above code and received an error.  Here's my exact code...

Function Main()
     If ISNull (REQD_DATE) Then
         DTSDestination("REQD_DATE") = REQD_DATE
     Else
         strLongDate = DTSsource("REQD_DATE")
         strShortDate = left ( strLongDate , 8)
         DTSDestination("REQD_DATE") = strShortDate
     End If
     Main = DTSTransformStat_OK
End Function

I received the error...
"DTSTransform4 encountered an invalid data value for 'REQD_DATE' destination column."

So I changed strShortDate to datetime because my datatype for this field is datetime.  I didn't recive any errors but I only recived Nulls in SQL?

I'll have to bump up the points on this questions.  Thanks in advance for all help JWT.
0
 
LVL 1

Expert Comment

by:JWT
ID: 8240761
see my revised code - I gave you a snippet before, and I realized that you were probably only running the snippet.  My bad.  Anyhoo...  Try the second posting.  It has the all important line

dateInQuestion = DTSsource("textdate")

which gets the info from your text file.
0
 
LVL 1

Expert Comment

by:JWT
ID: 8240778
see my revised code - I gave you a snippet before, and I realized that you were probably only running the snippet.  My bad.  Anyhoo...  Try the second posting.  It has the all important line

dateInQuestion = DTSsource("textdate")

which gets the info from your text file.
0
 
LVL 1

Author Comment

by:watbs
ID: 8241012
Okay JWT I added the...
Dim dateInQuestion
dateInQuestion = DTSsource("textdate")

and I'm still receving the error

"DTSTransform4 encountered an invalid data value for 'REQD_DATE' destination column."

Any ideas?


0
 
LVL 1

Expert Comment

by:JWT
ID: 8241086
OK - Write it exactly like this below:

Function Main()

Dim DteTrans
Dim strShortDate


    DteTrans = DTSsource("REQD_DATE")

    If ISNull (REQD_DATE) Then
        DTSDestination("REQD_DATE") = DteTrans
    Else
        strShortDate = left ( dteTrans , 8)
        DTSDestination("REQD_DATE") = strShortDate
    End If

    Main = DTSTransformStat_OK

End Function
0
 
LVL 1

Accepted Solution

by:
JWT earned 240 total points
ID: 8241194
Sorry - write it like this:

Function Main()

Dim DteTrans
Dim strShortDate


   DteTrans = DTSsource("REQD_DATE")

   If ISNull (DteTrans) Then
       DTSDestination("REQD_DATE") = DteTrans
   Else
       strShortDate = left ( dteTrans , 8)
       DTSDestination("REQD_DATE") = strShortDate
   End If

   Main = DTSTransformStat_OK

End Function

Also I assume the column name in the text file is REQD_DATE, and also the name in the SQL File?
0
 
LVL 1

Author Comment

by:watbs
ID: 8241469
JWT,
I tried your code from the last comment and got the same error...
"DTSTransform4 encountered an invalid data value for 'REQD_DATE' destination column."

Yes the name of the field in the text file and SQL dtb are the same, REQD_DATE.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 8245534
Just use 'Bulk insert ...' as part of SP (for e.g) and use my recommendations ......
0
 
LVL 1

Expert Comment

by:JWT
ID: 8247786
OK try changing strShortDate to datetime before you put it into the database.  That should do it.
0
 
LVL 1

Author Comment

by:watbs
ID: 8261873
I ended up changing the source of my datatype in SQL and made that dtb a stage table, then DTS into the live table.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question