Convert varchar field to time

Question

Convert varchar field to time

aqsluck

SSC Enthusiast

Points: 118
More actions
April 11, 2011 at 8:04 am

#148803

Hi I am inserting records from one table to another.
In Table 1
Create table abc
(id int not null,
name varchar(4000),
Starttime varchar(4000),
Endtime varchar(4000))
Table 2
Create Table xyz
(id int not null,
name varchar(1000),
Start datetime not null)
the starttime columns has only time (eg 10:32 AM)
I want to insert this value into table two where I want to insert only time and it should not take date.
and as Start column in table xyz is not null, I want to put current time whereever it finds null in starttime column
I am using query as
Insert into xyz(id, Name, start)
select id, name, convert(datetime, isnull(starttime, getdate())) from abc

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

aqsluck (4/11/2011)
I want to insert this value into table two where I want to insert only time and it should not take date.

SQL 2005 (the forum you posted in) does not have a TIME data type, only DATETIME, so you will not be able to store a time and "not take date" unless you store your data in a character field.

Using the example time from your post, try running this to see what I mean:

SELECT CONVERT(DATETIME, '10:32 AM') as example_time_as_datetime ;

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 2

the starttime columns has only time (eg 10:32 AM)
I want to insert this value into table two where I want to insert only time and it should not take date.

Have look at this.

select CONVERT(varchar(8),getdate(),108)+' '+RIGHT(CONVERT(VARCHAR, GETDATE(), 109),2)

Thanks
Parthi

scrape SSC Enthusiast Points: 143 More actions · Answer 3

As was said above, there is no time in SQL without a date.

Because SQL uses DATETIME instead of having DATE and TIME as separate data types, you must consider two things.

First, you are correct in storing it as a datetime. The strong typing makes sure invalid times are not stored. It simply requires using a default value for the date portion of it. The example from Mr / Mrs 500 above uses the default date of 1/1/1900 with your custom time. That should be fine as long as you apply it consistently in your data, as well as any client applictions.

Secondly, and most important, you must handle this in whatever client software you use for entering or presenting the data. SQL won't have any problem with the datetime, but if you have an app that is displaying that data, you must remember to format it without the date. If you are doing any logic with it, remember that the 1/1/1900 date comes with your time and you must keep that in mind in your coding. This is very normal in environments with a SQL database back-end. If you do an export and the export is displaying the unwanted date, then you need to change your export process, not the way you store the data.