convert time - 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)

convert time

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-03-31 : 04:09:40

i have column of varchar

TIMECol
10:24

i want to be in datetime

TIMECol
10:24

so i write Convert(DateTime,TIMECol,08)
i get

TIMECol
1900-01-01 20:24:00.000

i can i delete the date(1900-01-01)?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-31 : 04:24:39

Short answer, No.

E 12°55'05.63"
N 56°04'39.26"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-03-31 : 04:24:51

No, because datetime holds it in a date and time format. 1900-01-01 is SQl server starting date and usually is treated as null by most people. When you display the date you can always convert it to just show the time, but it is worth putting it into a datetime field. Do you also have a date column on the table? If so, then why not join them as any calculations on just the time will give you generally incorrect results without a date.

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-03-31 : 04:44:53

how can i join them if both of the are varchar?

TIMECol(varchar)
10:24

DateCol(varchar)
02/11/08

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-31 : 04:59:20

[code]DECLARE @Sample TABLE
(
colTime VARCHAR(5),
colDate VARCHAR(8)
)

INSERT @Sample
SELECT '10:24', '02/11/08'

SELECT colTime,
colDate,
CAST(colDate + ' ' + colTime AS DATETIME)
FROM @Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-31 : 05:53:57


SELECT	colTime,
	colDate,
	DATEADD(day,0,colDate) + colTime 
FROM	@Sample

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-31 : 06:00:21

Or to be really sure onyl date is taken from date column and only time is taken from time column

DECLARE	@Sample TABLE
	(
		colTime VARCHAR(20),
		colDate VARCHAR(20)
	)

INSERT	@Sample
SELECT	'10:24', '02/11/08' UNION ALL
SELECT	'1899-12-30 10:24', '02/11/08 00:00:00' UNION ALL
SELECT	'1899-12-30 10:24', '02/11/08 12:00:00'

SELECT	colTime,
	colDate,
	DATEADD(DAY, DATEDIFF(DAY, 0, colDate), 0) + DATEADD(DAY, DATEDIFF(DAY, colTime, 0), colTime)
FROM	@Sample

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources