Sql-server – Adding a varchar to a datetime column and dealing with nulls

sql servert-sql

I have two columns in my table a varchar(8) titled Trans_Time which holds data in an XX:XX:XX time format and a DateTime column named Trand_Date which holds just a the date component in XX-XX-XXXX format.

I am trying to combine the two columns to get a full date and time (i.e. if Trans_Time is 08:10:14 and Trand_Date is 04-04-2007 the output should be 04-04-2007 08:10:14).

My problem is that some times the Trans_Time is null and this produces an output of null.

Is there some way I can treat null as 00:00:00?

Best Answer

You can use ISNULL or COALESCE in T-SQL to replace a null value:

SELECT 
  Trand_Date + ' ' + COALESCE(Trans_Time, '00:00:00') AS Trand_DateTime1,
  Trand_Date + ' ' + ISNULL(Trans_Time, '00:00:00') AS Trand_DateTime2
FROM MyTable

Both columns above should return the same value. Note that if you want to perform date-based comparisons, e.g. DATEDIFF, DATEADD or even something as simple as WHERE Trand_DateTime < GETDATE(), you can create a view of the table that presents the values as an actual SQL DateTime datatype like so:

CREATE VIEW [dbo].[ViewTransDateTimes]
AS
SELECT 
  CAST(Trand_Date + 'T' + COALESCE(Trans_Time, '00:00:00') as DateTime) AS Trand_DateTime
  --, [OTHER COLUMNS]
FROM MyTable
GO

Then you can do things like:

SELECT * FROM ViewTransDateTimes
WHERE DATEDIFF(day, Trand_DateTime, GETDATE()) < 1000

Related Solutions

Sql-server – how to convert this varchar to datetime format

There is too much precision in the varchar to be converted into datetime.

One option(better in my opinion) would be to change the target column to datetime2(7). Then you can convert like this:

declare @dt varchar(50)
set @dt = '2015-12-02 20:40:37.8130000'

select cast(@dt as datetime2(7));

If changing the column is not an option the you can do the conversion like this:

declare @dt varchar(50)
set @dt = '2015-12-02 20:40:37.8130000'

select cast(cast(@dt as datetime2(7))as datetime)

Sql-server – What date/time literal formats are LANGUAGE and DATEFORMAT safe

In the documentation, it is very explicitly stated that the only safe formats are the ones I demonstrated at the very beginning of the question:

yyyyMMdd                 -- unseparated, date only
yyyy-MM-ddThh:mm:ss.fff  -- date dash separated, date/time separated by T

However, it was recently brought to my attention that there is a third format that is equally immune to any language or dateformat settings:

yyyyMMdd hh:mm:ss.fff    -- unseparated date, no T separator

TL;DR: This is true. For datetime and smalldatetime.

Read on for the longer version, and about as much proof as you're going to get.

There is a loophole that explains this - while the main text body fails to acknowledge yyyyMMdd hh:... as a format safe from transposed language or date format interpretations, there is a little blurb that says the date portion of such a string is not validated depending on dateformat settings:

It is rather unlike me to just take the documentation at its word, usually. You can say I'm a bit skeptical. And the language is ambiguous here, too - it just states that this is about the combination of date and time, not calling out the space explicitly (that could be a carriage return, for all I know). It also says that it isn't multi-language, which means it could fail in certain languages, but we'll find out shortly that that is incorrect as well.

So I set out to prove that no combination of language/dateformat could make this specific format fail.

First, I created a little block of dynamic SQL for each language:

EXEC sys.sp_executesql @sql, N'@lang sysname', N'us_english';

This produced 34 rows of output like this:

EXEC sys.sp_executesql @sql, N'@lang sysname', N'us_english';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'Deutsch';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'Français';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'日本語';
...
EXEC sys.sp_executesql @sql, N'@lang sysname', N'简体中文';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'Arabic';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'ไทย';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'norsk (bokmål)';

I copied that output to a new query window, and above it, I generated this code, which would hopefully try to convert that same date (the 13th of March) to the 3rd day of the 13th month in at least one case:

DECLARE @sql nvarchar(max) = N'
SET LANGUAGE @lang;
SET DATEFORMAT ydm;
SELECT @@LANGUAGE, CONVERT(datetime, ''20170313 23:22:21.020'');';

Nope, every language worked just find in ydm. I tried every other format, too, and also every date/time data type. 34 successful conversions to March 13th, every time.

So, I do concede to @AndriyM and @ErikE that, indeed, there is a 3rd safe format. I will keep this in mind for future posts, but I've pounded the drums about the other two in so many places, I'm not going to hunt them all down and correct them now.

By extension, you would think this one would be safe, but no:

yyyyMMddThh:mm:ss.fff    -- unseparated date, T separator

I think in every language, this will yield the equivalent of:

Msg 241, Level 16, State 1, Line 8
Conversion failed when converting date and/or time from character string.

For completeness, there is a fourth safe format, but it is only safe for conversions to the newer date/time types (date, datetime2, datetimeoffset). In these cases the language settings can't interfere:

yyyy-MM-dd hh:mm:...

However, I highly recommend against its use because it only works for the newer types, and the old ones are still in a massive amount of use, in my experience. Why have the dashes there when everywhere else (or in fact in that same code, if the data type changes) you have to remove them?

SET LANGUAGE Deutsch;
DECLARE @dashes char(10) = '2017-03-07 03:34';
DECLARE @d date = @dashes, @dt datetime = @dashes, @dt2 datetime2 = @dashes;

SELECT DATENAME(MONTH,@d), DATENAME(MONTH,@dt), DATENAME(MONTH,@dt2);

Even given the same source string, the conversions yielded quite different results:

März    Juli    März

The format that works for datetime (yyyyMMdd) will also always work for date and the other new types. So, IMHO, just always use that. And given the third format for types with date/time (yyyyMMdd hh:...), this will actually allow you to be more consistent - even if the date component is always a little bit less readable.

Now it will just take me a few years, give or take, to get into the habit of demonstrating the three safe formats when I'm talking about string representation of dates.

Best Answer

Related Solutions

Sql-server – how to convert this varchar to datetime format

Sql-server – What date/time literal formats are LANGUAGE and DATEFORMAT safe

Related Question