February 3, 2015

DateAdd

The DateAdd() Functions adds/subtracts a specific interval to a specified date

Dateadd(datepart,interval,date)

--datepart:

--year

--quarter

--month

--dayofyear

--day

--week

--weekday

--hour

--minute

--second

--millisecond

--microsecond

--nanosecond

Dateadd(datepart,interval,date)

--datepart:

--year

--quarter

--month

--dayofyear

--day

--week

--weekday

--hour

--minute

--second

--millisecond

--microsecond

--nanosecond

Example:

Using the DateAdd function in a loop to add 1 month to each date

Declare @Counter int
Set @Counter = 0

&nbsp;

--Add for 24 months

While @Counter &lt; 24

Begin

--increment counter
set @Counter+= 1

--increment by month for given counter against todays date

select DateAdd(MONTH, @Counter, GetDate())

End

Declare @Counter int

Set @Counter = 0

--Add for 24 months

While @Counter < 24

Begin

--increment counter

set @Counter+= 1

--increment by month for given counter against todays date

select DateAdd(MONTH, @Counter, GetDate())

End

November 19, 2014

“The terminal server has exceeded the maximum number of allowed connections”

If you ever receive the following; it means the terminal server can only accept a certain amount of simultaneous connections.

To get around this try the following:

mstsc /v:x.x.x.x /admin

Replace the x.x.x.x with the address of the terminal server.

After connecting you will need to log out the duplicate user.

Hope that helps 🙂

November 12, 2014

Data Execution Prevention workaround

If you ever get those applications that wont allow you to run more than one instance, try the following:

– Right click on “Computer” or “My Computer”
– Select “Advanced” or “Advanced System settings”
– Under Performance heading; select “Settings”
– Select “Data Execution Prevention”
– Select “Turn on DEP for all programs and services except those I select.
– Select “Add” and add the required programs to the list.
– Hit Ok

Hope that helps!! 🙂

October 1, 2014

Configure Windows Firewall for SQL Server

After a fresh install of an SQL instance, you may find you cannot connect to it from another machine on the network. It might be that the firewall is blocking it. Try the following on the server:

– Open Windows Firewall:
– Allow programs to connect
– Add sqlbrowser and sqlserver found in
C:\Program Files\Microsoft SQL Server\MSSQL10_50.[INSTANCENAME]\MSSQL\Binn\sqlservr.exe
C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe

August 13, 2014

SQL date time formatting

When writing queries you might sometimes come across those people clients who want the dates formatted in a certain way (pesky clients *shakes fist). Don’t stress its nothing too difficult.

Take the date 20th May 2014 and you get a request to convert this to dd/mm/yyyy.

We usually work in iso dates (yyyymmdd). For the example I will cast the iso date as a string and then cast it as a datetime and work from there.

Our final product will look something as follows:

--
--
select Convert(varchar,Cast(Cast(20140520 as varchar) as datetime),103)
--Output will be '20/05/2014'

select Convert(varchar,Cast(Cast(20140520 as varchar) as datetime),103)

--Output will be '20/05/2014'

I will go through what each of the steps entail.

--
--
-- First we will need to cast our integer as a varchar
select Cast(20140520 as varchar)
--Output will be 20140520 (obviously as a string)

--Next we will need to cast our varchar as datetime
select Cast(Cast(20140520 as varchar) as datetime)
--Output will be '2014-05-20 00:00:00.000'

--Now we will need to convert the datetime to a varchar
select Convert(varchar,Cast(Cast(20140520 as varchar) as datetime),103)
--Output will be '20/05/2014'

-- CONVERT(data_type , expression , style)
-- data_type = is the type you want to convert your expression to.
-- expression= the current expression
-- style = the style you want the expression converted to.
-- 0(yy) or 100(yyyy) = mon dd yyyy hh:miAM (or PM)
-- 1(yy) or 101(yyyy) = mm/dd/yy
-- 2(yy) or 102(yyyy) = yy.mm.dd
-- 3(yy) or 103(yyyy) = dd/mm/yy
-- 4(yy) or 104(yyyy) = dd.mm.yy
-- 5(yy) or 105(yyyy) = dd-mm-yy
-- 6(yy) or 106(yyyy) = dd mon yy
-- 7(yy) or 107(yyyy) = Mon dd, yy
-- 8(yy) or 108(yyyy) = hh:mm:ss
-- 9(yy) or 109(yyyy) = mon dd yyyy hh:mi:ss:mmmAM (or PM)
-- 10(yy) or 110(yyyy) = mm-dd-yy
-- 11(yy) or 111(yyyy) = yy/mm/dd
-- 12(yy) or 112(yyyy) = yymmdd
-- 13 or 113(yyyy) = dd mon yyyy hh:mi:ss:mmm (24h)
-- 14(yy) or 114(yyyy) = hh:mi:ss:mmm (24h)
-- 20 or 120(yyyy) = yyyy-mm-dd hh:mi:ss (24h)
-- 21 or 121(yyyy) = yyyy-mm-dd hh:mi:ss.mmm (24h)
-- 126(yyyy) = yyyy-mm-ddThh:mi:ss.mmm (no spaces)
--130(yyyy) = dd mon yyyy hh:mi:ss:mmmAM
--131(yyyy) = dd/mm/yy hh:mi:ss:mmmAM

-- First we will need to cast our integer as a varchar

select Cast(20140520 as varchar)

--Output will be 20140520 (obviously as a string)

--Next we will need to cast our varchar as datetime

select Cast(Cast(20140520 as varchar) as datetime)

--Output will be '2014-05-20 00:00:00.000'

--Now we will need to convert the datetime to a varchar

select Convert(varchar,Cast(Cast(20140520 as varchar) as datetime),103)

--Output will be '20/05/2014'

-- CONVERT(data_type , expression , style)

-- data_type = is the type you want to convert your expression to.

-- expression= the current expression

-- style = the style you want the expression converted to.

-- 0(yy) or 100(yyyy) = mon dd yyyy hh:miAM (or PM)

-- 1(yy) or 101(yyyy) = mm/dd/yy

-- 2(yy) or 102(yyyy) = yy.mm.dd

-- 3(yy) or 103(yyyy) = dd/mm/yy

-- 4(yy) or 104(yyyy) = dd.mm.yy

-- 5(yy) or 105(yyyy) = dd-mm-yy

-- 6(yy) or 106(yyyy) = dd mon yy

-- 7(yy) or 107(yyyy) = Mon dd, yy

-- 8(yy) or 108(yyyy) = hh:mm:ss

-- 9(yy) or 109(yyyy) = mon dd yyyy hh:mi:ss:mmmAM (or PM)

-- 10(yy) or 110(yyyy) = mm-dd-yy

-- 11(yy) or 111(yyyy) = yy/mm/dd

-- 12(yy) or 112(yyyy) = yymmdd

-- 13 or 113(yyyy) = dd mon yyyy hh:mi:ss:mmm (24h)

-- 14(yy) or 114(yyyy) = hh:mi:ss:mmm (24h)

-- 20 or 120(yyyy) = yyyy-mm-dd hh:mi:ss (24h)

-- 21 or 121(yyyy) = yyyy-mm-dd hh:mi:ss.mmm (24h)

-- 126(yyyy) = yyyy-mm-ddThh:mi:ss.mmm (no spaces)

--130(yyyy) = dd mon yyyy hh:mi:ss:mmmAM

--131(yyyy) = dd/mm/yy hh:mi:ss:mmmAM

July 2, 2014

Hello dev world!

A little about me
A big hello from The Lazy developer here is Aus. Been in development for a good 10 years now and have to say there’s been a few ups and downs here and there. Started learning Java at uni and progressed in various languages throughout my studies. Have to say though was a pretty fun experience learning new languages and techniques to develop solutions.

Current
Currently been involved with silverlight, vb .net, vb6 and SQL dbs. Been at it for the last 5 years or so; so been an quite interesting learning curve.

The Lazy Developer

So so lazy….