Trying 2 Convert 0833 to time...Im Getting an Error 1

[jojo79]

Not Working:

convert(varchar, convert(datetime, [Time]), 109) AS "Formatted Int"

Error:

Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Anybody have any reason????? or Thoughts...


I am looking for and output of "8:33

 

[gmmastros]

Do you want the field to return as a date time value, or would you be satisfied with a string (for your output)?

If a string is ok, then take a look at this code.

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

[COLOR=blue]Set[/color] @Data = [COLOR=red]'0833'[/color]

[COLOR=blue]Select[/color] [COLOR=#FF00FF]Replace[/color]([COLOR=#FF00FF]LTrim[/color]([COLOR=#FF00FF]Replace[/color]([COLOR=#FF00FF]Stuff[/color](@Data, 3, 0, [COLOR=red]':'[/color]), [COLOR=red]'0'[/color], [COLOR=red]' '[/color])), [COLOR=red]' '[/color], [COLOR=red]'0'[/color])

-George

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

 

[jojo79]

Don't really understand what you did?? It worked, but can you explain a little more??? Also will this work if it is 1033? Thanks again.

 

[gmmastros]

Sure. I'll explain.

Suppose you start with "0833"

The first thing we do is STUFF. This puts a colon in the string, so it looks like this...

"08:33"

Then, we replace 0 with SPACE, so it looks like this...

" 8:33"

Then we LTRIM it so it becomes this....

"8:33"

Then, we replace spaces with 0's. In this example, it has no effect, but, depending on the data, it would.

Suppose you started with "1030"

1. "10:30"
2. "1 :3 "
3. "1 :3 "
4. "10:30"

Understand now?

-George

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

 

[bonzer2u]

Hi George,

I thought my 'light' was starting to flicker abit, but its likely just a flashback. I see the structural similarities in code between this post and a recent question you answered for me as follows.

Declare @Temp Char(4)
Set @Temp = '1545'
Select Right(Convert(VarChar(20), Convert(DateTime, Stuff(@Temp, 3, 0, ':')), 100), 7)

and will attempt my feeble understanding as follows:

1. STUFF the ':' starting at the 3rd position deleting 0 from the initial string, 1545

resulting in
15:45

2. CONVERT to datetime format 100?

I understand this is the format I want for my hh:mmAM(PM) time but please enlighten me here..........as to how the initial characters are automagically known as hour:minute values?

3. CONVERT to varchar(20), leading zero's stripped?

I'm not getting what conversion needs to takes place first, chichen or the egg thing....

4. select up to the RIGHT 7 characters,

resulting in
3:45PM

I'm sure I totally abused something..... which brings me to my point that inidividuals are either born with the 'knack' or not, myself being the latter of the 2.

 

[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]Stuff[/color](@Temp, 3, 0, [COLOR=red]':'[/color])
[COLOR=green]-- 14:45
[/color]
[COLOR=blue]Select[/color] [COLOR=#FF00FF]Convert[/color]([COLOR=#FF00FF]DateTime[/color], [COLOR=#FF00FF]Stuff[/color](@Temp, 3, 0, [COLOR=red]':'[/color]))
[COLOR=green]-- 1900-01-01 15:45:00.000
[/color]
[COLOR=blue]Select[/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)
[COLOR=green]-- Jan  1 1900  3:45PM
[/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)
[COLOR=green]-- 3:45PM
[/color][COLOR=green]-- You can't tell, but there is a leading space here.
[/color]
[COLOR=blue]Select[/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](@Temp, 3, 0, [COLOR=red]':'[/color])), 100), 7), [COLOR=red]' '[/color], [COLOR=red]'0'[/color])
[COLOR=green]-- 03:45PM[/color]

The thing to realize is the sql server knows how to convert from string to Date/Time and vice versa.

Also, realize that without a date component, the time will get a date of Jan 1, 1900. For this query, it doesn't matter because we will be formatting the time ONLY.

By the way, I am extremely impressed that you want to understand this process. If there is anything here that you don't completely understand, please let me know and I will explain it more.

-George

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

 

[bonzer2u]

Jeeez, 'abused' was quite the understatement, your step-through has really made an impression of how I will look at and (try to) create code in the future.