Alias name should be preceded by “AS”

In my earlier post on Understanding Column alias I have shown different methods of using a column alias name

SELECT 100number
SELECT 100.number
SELECT 100'number'
SELECT 100"number"
SELECT 100[number]

As you see all works fine with number as the column alias name

But However you need to use AS before the alias name for better reading and standard
Now execute the following and see the result

drop table if exists #employee_master;
create table #employee_master
(
emp_id int, emp_name varchar(100), dob datetime
)
insert into #employee_master
select 1,'Sankar','19760910' union all
select 2,'Kamal','19680707' union all
select 3,'Rajesh','19661222'
Select emp_id, emp_name dob from #employee_master

The result is

emp_id      dob
----------- ------------
1           Sankar
2           Kamal
3           Rajesh

The SELECT statement lists out three columns but the output has only two columns. The data of emp_name are listed under the column name dob. It is because the missing comma between column names makes that dob becomes alias name for emp_name which is confusing and hard to detect if there are hundreds of lines in the procedure.

So in my opinion ,Alias name should be preceded by “AS” to avoid confusion and unexpected result.

What do you think of this?

Adding time values stored as numbers

One of my friends told me that he had a data set in which numbers are expressed in decimal format.

For example 3.45 means 3 hours and 45 minutes, 4.30 means 4 hours 30 minutes etc.

The challenge is to add these numbers not as decimal values but as time values so adding 3.45 and 4.30 should be 8.15 and not 7.75.

Let us create this data set

create table #t (gtime numeric(8,2)) 
insert into #t values (3.45),(4.30),(4.45),(4.45) 

There are two methods that I come up with

Method 1 : Use CONVERT function

select 
convert(varchar(10), 
	dateadd(minute,sum(floor(gtime)*60+gtime%1*100),0),
108)  
        as time_value
from 
	#t   

The result is

time_value
----------
17:45:00

Method 2 : CONVERT the sum into TIME

select 
	cast(dateadd(minute,sum(floor(gtime)*60+gtime%1*100),0) as time)  
        as time_value
from 
	#t 

The result is

time_value
----------------
17:45:00.0000000

How to know the start time of SQL Server?

One of my friends asked me “How do I find out when the SQL Server is started/restarted?”

Well. There can be many ways to know this. The two methods I know are querying the system views sysdatabases and dm_os_sys_info

Method 1 : Query sysdatabases

select
	crdate
from
	master..sysdatabases
where
	name='tempdb'

The result is

crdate
-----------------------
2017-09-01 15:58:28.417

Method 2 : Query dm_os_sys_info

select
	sqlserver_start_time
from
	sys.dm_os_sys_info

The result is

sqlserver_start_time
-----------------------
2017-09-01 15:58:12.613

Note : This shows the time that my SQL server instance is started. You will get different time