Time in 00:00:00 text format with the need to convert it to seconds then back

This is just a quick example of converting a text format "time" field back and forth into seconds so that summations and such can be done.

DECLARE @fauxTime VARCHAR(8) = '00:01:00'; -- we have a free form "time" field

DECLARE @fauxTimeInSeconds INT = (SELECT Datediff(second, Cast('00:00:00' AS TIME), Cast(@fauxTime AS TIME))) -- Convert it to seconds\

DECLARE @fauxTimeBackToTimefromSeconds TIME =(SELECT Cast(CONVERT(VARCHAR, Dateadd(ss, @fauxTimeInSeconds, 0), 108) AS TIME))

DECLARE @fauxTimeBackToHHMMSSfromSeconds VARCHAR(8) =(SELECT CONVERT(VARCHAR,Dateadd(ss, @fauxTimeInSeconds, 0), 108)) -- convert back to a character based time field

PRINT @fauxTime

PRINT @fauxTimeInSeconds

PRINT @fauxTimeBackToTimefromSeconds

PRINT @fauxTimeBackToHHMMSSfromSeconds

OUTPUT:

00:01:00 -- Original.

60 -- Converted to seconds.

00:01:00.0000000 -- Converted back into a time field.

00:01:00 - Converted back to a character field.

Error: 8624, Severity: 16, State: 116 -> When things go really wrong

If you are reading about this, it is likely that you just hit the error: Error: 8624, Severity: 16, State: 116

This is an error that means you are having a bad day, because somewhere in your code SQL was generated and that SQL isn't working.

If you haven't already figured out what the SQL is, the pro tip is this: If it the SQL is like 100 lines of joins, then it has some IN statements and in that IN statement is a 100 lines of joins, then that is likely the SQL you are looking for.

If you can't find the SQL with that tip, then go through SQL Server Profiler and figure out where it is.  Remember, somewhere you have a mess that looks something like

SELECT * FROM table join (lots of other stuff()  where mykey in (select mykey from table join (lots of other stuff)) over and over and over.

Now that you have found this code, here are my recommendations:

Figure out how to craft the SQL better so that it isn't using so much logic.

The place that I found this problem was with code that was generated dynamically by other code, and my suggestion is that you fix the underlying problem of having generated unusable code. If you can't do it, you have an option to "just make it run" but this option is going to trade the code working, for very slow code.

You can put an option called "OPTION (FORCE ORDER)" at the end of the code, this will FORCE SQL Server to execute the code with the most naive execution plan possible.  This isn't great, in fact, it is really bad. Any code with that many joins is probably going to be slow even in a perfect world, but it will work.

Here are some good articles I found on this when we hit this problem where I work.

https://blogs.msdn.microsoft.com/sqlserverfaq/2014/07/15/troubleshooting-error-8624-severity-16-state-21-internal-query-processor-error-the-query-processor-could-not-produce-a-query-plan/

https://www.simple-talk.com/sql/performance/controlling-execution-plans-with-hints/

Count vs Count distinct in SQL

Had a new coder ask me how count works, vs count distinct. So I made a small example:

create table #tmp_example

(

       id int identity(1,1)

       , value varchar(255)

)

insert into #tmp_example

(

       value

)

select '1'

UNION select '2'

UNION select '3'

UNION select NULL

select

count(*)

, count(distinct value)

, count(distinct case when value = 1 then null else value end)

from #tmp_example

In this example it returns 4, 3, 2

4 because there are 4 rows (*) in the system.

3 because there are 3 distinct values where NULL is not counted a value in count distinct.

2 because there are 2 distinct values when 1 is cast as null during the counting process.

Google Chrome does not work but Internet Explorer does

This is for those of you who have gone through the effort of checking the internet explorer->internet options-> and UNCHECK the proxy server, and it didn't work for you.

Another possible solution is to flush the DNS cache of your computer. Sometimes Google just outright loses the ip it needs to connect to and won't work on getting a new one.

Go to the command prompt by going to the start menu, and in the command box tying "cmd" this will bring up a dos box.

In the dos box type "Ipconfig /flushdns" and see if that works for you.  I have seen people think I am "amazing" for knowing how to do this, but this used to be quite a common problem in older versions of windows, and the trick still works sometimes with common connectivity problems.  Now why it allows one browser to work when the other won't... that still confuses me, but there you go.