Date Formatting - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Date Formatting

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2007-11-15 : 07:08:22

Hi

I am importing an Excel spreadsheet into a table within a database.
There is one column within the Excel spreadsheet that contains a figure for call durations in a custom format of hh:mm so it would show something like 02:15, however when I import it into the database table the format of the value changes to something like Jan 1 1900 12:00AM, is it possible to get the original formatting back through a select statement or another way?

Thanks

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-11-15 : 07:11:23

You MAY be able to change it back. I would simply re-import it, but NOT store it as a DateTime. Store it as a nvarchar.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2007-11-15 : 07:15:47

Hi

Its already being imported as nvarchar

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-15 : 07:18:58

you may be importing as nvarchar but the you are apparently storing it as datetime, that's why it put the date bit on for you.

What Don is telling you to do is change your column datatype to nvarchar. have a look at the table what is the datatype of that column?

Em

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2007-11-15 : 07:22:05

Hi

I am already doing that but still doesnt work.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-15 : 07:31:36

what is your column datatype then?

examples below....


select    getdate() as proper_datetime
	 ,cast('2:25' as datetime) as time_as_datetime
	 ,right(getdate(),7) as time_only_from_datetime_as_string
	 ,right('Jan 1 1900 12:00AM',7) as time_only_from_string_datetime

proper_datetime         time_as_datetime        time_only_from_datetime_as_string time_only_from_string_datetime
----------------------- ----------------------- --------------------------------- ------------------------------
2007-11-15 12:30:26.120 1900-01-01 02:25:00.000 12:30PM                           12:00AM

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2007-11-15 : 07:46:44

Both the imported and table column data types are nvarchar.

I am trying to get it to display like the last two columns minus the pm/am

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-15 : 07:59:07

ok, look at these then...


select   cast(datepart(hh,getdate())as char(2)) +':'+ cast(datepart(mi,getdate()) as char(2))
	,cast(datepart(hh,'Jan 1 1900 12:45AM')as char(2)) +':'+ cast(datepart(mi,'Jan 1 1900 12:45AM') as char(2))

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2007-11-15 : 08:23:10

Is it possible to do it for minutes and seconds with the fieldname Duration?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-15 : 08:40:13

select cast(datepart(mi,yourCol)as char(2)) +':'+ cast(datepart(ss,yourCol) as char(2)) as duration

are you sure you wouldn't rather use a datetime?