Varchar to time conversion - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Varchar to time conversion

Author

Topic

scholin
Yak Posting Veteran

56 Posts

Posted - 2009-10-11 : 22:17:20

Due to my application, I have to use a varchar() to hold a military time value(ie 13:33). I am using SQL Server Management Studio 2008 and trying to create a second computed field to convert this varchar to a time field.

I am using what I believe is a correct function:
CONVERT(datetime,[est_start_time],108)

This works perfectly if I type in the '13:33' value, but it does not work if try using the field that contains the value.

What am I missing here? I have tried a lot of different ways of expressing my field [est_start_time].

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-11 : 22:42:58

check your data. that varchar column might contain value that not able to convert to datetime


select est_start_time
from   yourtable
where  isdate(est_start_time) <> 1

KH
[spoiler]Time is always against us[/spoiler]

scholin
Yak Posting Veteran

56 Posts

Posted - 2009-10-11 : 23:32:08

It is a new table with only 4 entries - all with the exact time entered: 13:33. I ran your select statement and it came up empty so I am pretty confident that part is correct.

When I enter the function: CONVERT(datetime,[est_start_time],108) in the "Formula" field in Studio, it won't accept it unless I enter the actual value = '13:33'. How should I enter the field into that function?

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-11 : 23:37:50

What's the error message you got ?

also try see if this is ok


alter table <table name> add start_time as convert(datetime, [est_start_time], 108)

KH
[spoiler]Time is always against us[/spoiler]

scholin
Yak Posting Veteran

56 Posts

Posted - 2009-10-12 : 01:14:56

Thank you KH - interesting, when I used a query to create start_time, as you suggested it created it just fine with the exact function I was trying to use directly in the Studio. I am learning more and more why the "pros" like direct scripting better than using studio.

Thanks again - that did exactly what I needed!

scholin
Yak Posting Veteran

56 Posts

Posted - 2009-10-13 : 23:07:34

I thought this was working but it gives me an SQL Execution error about the "varchar data type is a datetime data type resulted in an out-of-range value"

Here is where I am now: I have two pull down fields on my web for [hour] and [minutes] for 24 hour time. Both fields are varchar(15).

I need to get them into a sql datetime column that can be used elsewhere for formulas and other functions.

What is the best way to convert these varchar into datetime column?

Thank you!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-14 : 00:15:28

check your data. See if there are in valid datetime format.

KH
[spoiler]Time is always against us[/spoiler]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-10-14 : 05:28:09

You are talking about *time* here right? As far as I can see there is no mentioning of dates here...and '13:30' is not compatible with a datetime datatype. So unless I'm reading it wrong you're looking for something like this:

alter table <table name> add start_time as convert(time, [est_start_time])

..but I could be wrong...

- Lumbago
http://xkcd.com/327/

scholin
Yak Posting Veteran

56 Posts

Posted - 2009-10-14 : 07:54:46

Ah, I think I see the problem (I am guessing).

[est_start_time] is also a calculated field = [hour]+':'+[minutes]

So the error, I guess is that that field must calculate first before the other [start_time] can calculate.

If I base [start_time] on a column with varchar like 13:33 entered directly, then it all works without errors.

So what is needed here to get around this problem??

I appreciate any help here! Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-14 : 08:00:42

[code]alter table <table name> add start_time as convert(datetime, [hour]+':'+[minutes], 108)[/code]

KH
[spoiler]Time is always against us[/spoiler]

scholin
Yak Posting Veteran

56 Posts

Posted - 2009-10-14 : 09:56:24

I just don't get it - When I create the column with any of the above examples, no errors. But when I try to update any rows, I continue to get the same error.

I only have 4 rows of data and I confirmed that Hour and Minutes are indeed numbers. If I use any of the above examples on a column that has the time in there (ie 13:30) I never get the error so it is clear that it is related to this one computed field.

This seems like it should be so simple but I have literally spent hours on this one item trying lots of different configurations - driving me crazy! LOL

Any other thoughts? Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-14 : 10:04:33

can you provide the sample data that generate the error ? ?

KH
[spoiler]Time is always against us[/spoiler]

scholin
Yak Posting Veteran

56 Posts

Posted - 2009-10-14 : 10:46:14

As usual . . . it had to be user error - that is me!

Although I am not sure exactly what was wrong, but I created a simple test table with just hour and minute and the computed field and of course it worked without problems.

So I deleted these fields on my main table and recreated them and now they too worked as expected.

I hate spending hours learning simple lessons . . .

Thank you for your help and patience!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources