DateTime Columns - 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 2000 Forums

SQL Server Development (2000)

DateTime Columns

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-02-21 : 14:36:07

Guys,

I have following scenario

I have table with 3 columns

RECDARE DATETIME
DATE_RECORDED VARCHAR(50)
TIME_RECORDED VARCHAR(50)

RECDATE DATE_RECORDED TIME_RECORDED
_____________________________________________________
1998-07-01 00:00:00.000 07/01/1998 16:01
1998-07-01 00:00:00.000 07/01/1998 16:43

Initially I had 2 columns date_recorded and time_recorded, I converted date_recorded into recdate which is of 'datetime' datatype. However I have not been able to find a way to add mins and secs stored in time_recorded to the recdate field.
After adding the time_recorded field should read (1998-07-01 16:01:00.000 instead of 1998-07-01 00:00:00.000)

Any suggestions/inputs should be helpful

Thanks

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-21 : 15:20:19

Try using the Date related functions (refer BOL) as DateDiff, DatePart, DateAdd

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-21 : 15:48:43

Here is how to concatenate your two columns to get your new column:


DECLARE @s varchar(20), @t varchar(20), @u datetime

SELECT @s = '06/07/2005', @t = '12:31PM'

SELECT @u = @s + ' ' + @t

PRINT @u

Tara Kizer
aka tduggan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-22 : 01:43:46

Why did you use Varchar datatypes to store Date and time seperately. Use DateTime datatype which has both.

PRINT @u will convert the date to convert(varchar,@u,100)

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-22 : 02:18:30

[code]update yourtable
set RECDATE = convert(datetime, DATE_RECORDED + ' ' + TIME_RECORDED, 101)[/code]

----------------------------------
'KH'
It is inevitable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources