datetime and varchar fields concatenation

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

Transact-SQL (2000)

datetime and varchar fields concatenation

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-03-19 : 14:43:10

Guys,

I have datetime datatype field populated with date '1919-02-23 00:00:00.000' and I also have varchar datatype field which stores time. How can I manipulate sql statement to add time to the existing date field. I have tried below 2 sql statements but it didnt seem to work

RECDATE2 DATETIME '1919-02-23 00:00:00.000'
cBTIME VARCHAR(4) '0152'

UPDATE NBIRTH
SET RECDATE2 = SUBSTRING (CAST (RECDATE2 AS VARCHAR), 1, 11)+SUBSTRING (CBTIME, 1, 2)+':'+SUBSTRING (CBTIME, 3, 2)+':00.000'

UPDATE NBIRTH
SET DATEPART(HH, RECDATE2) = SUBSRTRING (CBTIME, 1, 2)

UPDATE NBIRTH
SET DATEPART(MI, RECDATE2) = SUBSRTRING (CBTIME, 3, 2)

any suggestions/inputs would help

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 14:48:43

UPDATE Table1
SET RecDate2 = RecDate2 + '01:00:00' -- add 1 hour

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-19 : 15:58:06

I strongly recommend using the correct datatypes; you can use a DateTime datatype to store a time quite easily, and then you can simply add it to other datetimes.

see:
http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx
and
http://weblogs.sqlteam.com/jeffs/archive/2004/12/02/2954.aspx

for some ideas and some functions to help you with this.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources