CHAR to partial TIME conversion??? 1

[bonzer2u]

Greetings,

I need to convert a char(4) source to time only datatype similar to what was discussed in this thread.

http://www.tek-tips.com/viewthread.cfm?qid=1353645

Although I have tried various combinations of these and other suggestions in this forum, I cant seem to find a solution.

example:
Source Column Destination
CHAR(4) CHAR(7)???
data = 0551 05:51AM
1545 03:45PM

Any suggestions would be greatly appreciated...

 

[gmmastros]

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Char[/color](4)

[COLOR=blue]Set[/color] @Temp = [COLOR=red]'1545'[/color]

[COLOR=blue]Select[/color] [COLOR=#FF00FF]Right[/color]([COLOR=#FF00FF]Convert[/color]([COLOR=blue]VarChar[/color](20), [COLOR=#FF00FF]Convert[/color]([COLOR=#FF00FF]DateTime[/color], [COLOR=#FF00FF]Stuff[/color](@Temp, 3, 0, [COLOR=red]':'[/color])), 100), 7)

This will NOT put the leading 0 in. Is that important?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I should mention that if the value in the source column cannot be converted to a valid time, you will get an error that looks like this...

[tt][red]Server: Msg 242, Level 16, State 3, Line 5[/red]
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.[/tt]


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bonzer2u]

Hi George,

and many thanks, I do need the leading zero(s) if any AND the AM/PM suffix as this is a mainframe data source that I need to duplicate for a future SQL2000 datamart database.

I tried your first method and got an error converting datetime from char string. Also note that my source and destination datatypes are CHAR(4) and CHAR(7) respectively. I wish I was more T-SQL savvy as you and members of this forum but my strength and experience is in SQL/Oracle DBA.

 

[gmmastros]

Like I said earlier, you're getting that error because you have invalid times in your table. What value should be used when you have an invalid time? NULL? Empty String?

Here's another example that pads the value with 0. It also accomodates invalid times. For those records with an invalid time, an empty string will be put in to the destination column.

[COLOR=blue]Update[/color] [!]TableName[/!]
[COLOR=blue]Set[/color]    [!]DestinationColumn[/!] = 
            [COLOR=blue]Case[/color] [COLOR=blue]When[/color] [COLOR=#FF00FF]IsDate[/color]([COLOR=#FF00FF]Stuff[/color]([!]SourceColumn[/!], 3, 0, ':')) = 1
                 [COLOR=blue]Then[/color] [COLOR=#FF00FF]Replace[/color]([COLOR=#FF00FF]Right[/color]([COLOR=#FF00FF]Convert[/color]([COLOR=blue]VarChar[/color](20), [COLOR=#FF00FF]Convert[/color]([COLOR=#FF00FF]DateTime[/color], [COLOR=#FF00FF]Stuff[/color]([!]SourceColumn[/!], 3, 0, ':')), 100), 7), ' ', '0')
                 [COLOR=blue]Else[/color] [COLOR=red]''[/color]
                 [COLOR=blue]End[/color]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bonzer2u]

George,

Your code works perfectly and I sincerely appreciate your efforts. Nulls will be used for invalid times.

I'm not worthy ... U.B.D.MAN

 

[gmmastros]

In that case, you'll want to change the code above.

Change:
Else ''

To:
Else NULL

With the code above, empty strings will be copied in to the destination. By changing the code as I suggest in this post, NULL will be used instead.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bonzer2u]

Will do, Many thanks again George...