Data isssue - with time

Question

Data isssue - with time

Eshika

SSCrazy

Points: 2046
More actions
November 30, 2010 at 12:33 pm

#388336

I have a time data which is in the format - '10:15 AM' in excel, but when I am loading it to nvarchar datatype it takes the following format -'Dec 30 1899 2:30PM'.
I do not want the default value, Can you please suggest any work arround.
Thanks,
Swetha K
INSERT INTO [Applicant]
([FirstInterviewTime])
SELECT substring(convert(varchar(32),[Time],121),12,5)
FROM dbo.exportedxls

Results in 14:00 format which is not desired .. I need it in hr:min AM/PM format.

Please suggest.

Swetha

GSquared SSC Guru Points: 260824 More actions · Answer 1

Excel is assuming it's a time, because of the format. Without a date on it, Excel adds a default date. You'll need to convert it to time-only during your import process.

Are you using SSIS to import, or the Import/Export Wizard, or OpenRowset, or a linked server, or something else?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Eshika SSCrazy Points: 2046 More actions · Answer 2

I am using a script, I need to insert into the datatype nvarchar(50). I shouldnt be changing the datatype.

Thanks,

Swetha K

GSquared SSC Guru Points: 260824 More actions · Answer 3

Can you post the script here?

If not, try adding a conversion to datatype "time" on the column and see if that does what you need.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Eshika SSCrazy Points: 2046 More actions · Answer 4

I have exported the data from excel as table and tried converting data.

source table has data in '10:15 AM' format and destination table has time column as nvarchar which shouldnt be changed and it takes the default value as posted with date with this code change I could get the time value but not absolutely.

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

Hi,

Check this

declare @date datetime

set @date='2010-11-30 22:30:38.047'

SELECT substring(convert(varchar(32),@date,121),12,5)

SELECT convert(varchar(32),Case When substring(convert(varchar(32),@date,121),12,2)>12 then

substring(convert(varchar(32),@date,121),12,2)-12 else substring(convert(varchar(32),@date,121),12,2) end )

+':'+substring(convert(varchar(32),@date,121),9,2)+' '+

Case When substring(convert(varchar(32),@date,121),12,2)>12 then 'PM' Else 'AM' end

go

declare @date datetime

set @date='2010-11-30 10:30:38.047'

SELECT substring(convert(varchar(32),@date,121),12,5)

SELECT convert(varchar(32),Case When substring(convert(varchar(32),@date,121),12,2)>12 then

substring(convert(varchar(32),@date,121),12,2)-12 else substring(convert(varchar(32),@date,121),12,2) end )

+':'+substring(convert(varchar(32),@date,121),9,2)+' '+

Case When substring(convert(varchar(32),@date,121),12,2)>12 then 'PM' Else 'AM' end

This is what you need.

Thanks

Parthi

Thanks
Parthi

Eshika SSCrazy Points: 2046 More actions · Answer 6

Thanks Parthi. that was really helpful.

Thank you for your time & prompt response.

Swetha K