Convert dattime if time unavailable

Question

Hi

I have an issue with converting and concatenating the date and time fields in my database.

I have 2 fields in the database: date (datetime) and time (time(7))   If the time is missing from the time field, then the date is not brought over by itself in the new column DateTime.

This is the code I am using:

CONVERT(DATETIME, Date) + CONVERT(DATETIME, Time) AS DateTime

Is there anyway I am able to change this so that if the time is missing from the Time feild that the date is brought over by itself?

Thanks

Answer 1

Hi, try this:

CASE WHEN [Time] IS NULL THEN CONVERT(DATETIME, Date) ELSE CONVERT(DATETIME, Date) + CONVERT(DATETIME, Time) END AS DateTime

David.

Answer 2

CREATE TABLE t (dt VARCHAR(20),tm VARCHAR(20))


INSERT INTO t VALUES ('20110101','15:25')
INSERT INTO t VALUES ('20110102',NULL)

SELECT CONVERT(DATETIME, dt) + COALESCE(CONVERT(DATETIME, tm),0) AS date_time
FROM t

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 3

select CONVERT(datetime,datecol+' '+isnull(timecol,'00:00')) from yrtable

---

Thanks and regards, Rishabh , Microsoft Community Contributor

Answer 4

why don't you bind a "Default" in those column...?

Answer 5

Brilliant thanks :-)

Answer 6

This has resolved if the time is unavilable, but What if both the date and time were unavailable?  Thanks

Answer 7

I have 2 fields in the database: date (datetime) and time (time(7))  

How about date (DATE) and time (TIME(7)) ? 

Related article:

http://www.sqlusa.com/bestpractices/datetimeconversion/

DATE is only 3 bytes as opposed 8 bytes DATETIME.

One option is NULL if nullable columns.

The second option 19010101 for date and 00:00 for time.

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 8

DECLARE	@Sample TABLE
	(
		[Date] DATETIME,
		[Time] TIME(7)
	)

INSERT	@Sample
VALUES	(NULL,NULL),
	(NULL, '10:11:12.3456789'),
	('20111213', NULL),
	('20111009', '10:11:12.3456789')

-- SwePeso
SELECT	[Date],
	[Time],
	CASE
		WHEN [Date] IS NULL AND [Time] IS NULL THEN CAST(NULL AS DATETIME2(7))
		WHEN [Time] IS NULL THEN CAST([Date] AS DATETIME2(7))
		WHEN [Date] IS NULL THEN CAST([Time] AS DATETIME2(7))
		ELSE CAST(CONVERT(CHAR(8), [Date], 112) + ' ' + CONVERT(CHAR(16), [Time]) AS DATETIME2(7))
	END AS Yak
FROM	@Sample

/*
	ALTER TABLE	dbo.YourTableNameHere
	ADD		FullTime
	AS		CASE
				WHEN [Date] IS NULL AND [Time] IS NULL THEN CAST(NULL AS DATETIME2(7))
				WHEN [Time] IS NULL THEN CAST([Date] AS DATETIME2(7))
				WHEN [Date] IS NULL THEN CAST([Time] AS DATETIME2(7))
				ELSE CAST(CONVERT(CHAR(8), [Date], 112) + ' ' + CONVERT(CHAR(16), [Time]) AS DATETIME2(7))
			END
*/

---

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