Merging Date and Time Information

[DebbieChapman]

I have a table with 2 fields Date and time, the format of which are 23/01/2003 and 23.59.01 (hh:mm:ss) is there a way to merge these fields so that if I had to add, say 30 seconds, i could then make the datetime to be 24/01/2003 00:00:31.

At the moment I can add the time, but this causes problems it the time i am looking for goes into another day.

 

[hneal98]

Convert both to a string and then back to DateTime like this:

Convert(Datetime,(convert(char(10),DateField,101) + convert(char(8), TimeField)))

 

[DebbieChapman]

Cheers, I tried this but got the following error

The converstion of char data type to a datetime data type resulted in an out-of-range datetime value

any idea's

 

[crjones]

Ah, brit time.

This is simpler...

Assuming Datefield and TimeField are char values...

Convert(DateTime, DateFiled + ' ' + TimeField, 103)

Ta! ;-)

-crj

 

[crjones]

Oh my! Sorry! Forgot a step.

The Convert(DateTime, DateFiled + ' ' + TimeField, 103) is still valid, but MS does not suggest adding binary values to datetime fields, instead, use the dateadd function.

Therefore, the final solution is:

DateAdd( s, 30, Convert(DateTime, DateField + ' ' + TimeField, 103) )