Convert datetime and varchar to single date

Question

I have a following database columns and the values

MyDate datetime and My_Time varchar(5)

the values stored are

2006-09-05 00:00:00.000 and 16:47

Now I want to add this two columns and get a single datetime value 2006-09-05 16:47:00.000

How can I do this in SQL ?

UPDATE:

Some rows have NULL values for DocDate and DocTime.

So i am getting error like Conversion failed when converting date and/or time from character string

possible duplicate of [How to combine date from one field with time from another field - MS SQL Server](http://stackoverflow.com/questions/700619/how-to-combine-date-from-one-field-with-time-from-another-field-ms-sql-server) — DaveShaw, Oct 25 '13 at 11:29
Disagree with duplicate since as shown below, there is a cleaner answer for this particular question. — OGHaza, Dec 18 '13 at 20:41

OzrenTkalcecKrznaric · Answer 1 · 2013-10-25T13:44:39.370

1

Try this:

select dateadd(ss, datediff(ss, 0, @My_Time), @MyDate)

Key point is to understand that it's the same as...

select dateadd(ss, datediff(ss, 0, cast(@My_Time as time)), @MyDate)

...but the conversion is done explicitly.

EDIT

For default time and/or date, use ISNULL or COALESCE as appropriate.

Example:

SELECT 
  CAST(
      isnull(@date, '2000-1-1') + 
      isnull(@time, '0:0') 
    AS DATETIME)

edited Oct 25 '13 at 13:44

answered Oct 25 '13 at 11:26

OzrenTkalcecKrznaric

5,535
4
34
57

What is `ss`? can you provide some link for this? – Billa Oct 25 '13 at 11:28
ss = seconds = expression uses seconds for calculation – OzrenTkalcecKrznaric Oct 25 '13 at 11:41
You have to be clear what do you want if data is missing. If you want to throw the error, you have it. If you want to substitute NULL time for midnight, or substitute NULL date for certain year, use ISNULL or COALESCE. – OzrenTkalcecKrznaric Oct 25 '13 at 13:42

score 1 · Answer 2 · answered Oct 25 '13 at 11:31

1

Simply...

DECLARE @date DATETIME
DECLARE @time VARCHAR(5)

SET @date = '2006-09-05 00:00:00.000'
SET @time = '16:47'

SELECT CAST(@date + @time AS DATETIME) -- 2006-09-05 16:47:00.000

answered Oct 25 '13 at 11:31

Chris Pickford

8,642
5
42
73

As a matter of fact, this is a better solution than mine :P – OzrenTkalcecKrznaric Oct 25 '13 at 11:42
Sometimes I may have `NULL` values for MyDate and MyTime. So i am getting error like `Conversion failed when converting date and/or time from character string` – Billa Oct 25 '13 at 11:44

score 0 · Answer 3 · answered Oct 25 '13 at 11:33

0

Try

CAST(MyDate AS DATETIME) + CAST(MyTime AS DATETIME) as CombinedDate

answered Oct 25 '13 at 11:33

Murali Murugesan

22,423
17
73
120

Convert datetime and varchar to single date

3 Answers3