SQL Date Manipulation Functions and Examples

Date manipulation in SQL Server there are several functions, lets see same examples of that:

-- Example 1

select getdate()

these function returns current date time like 2009-07-26 14:55:03.210

-- Example 2

select GETDATE()+60

On these example we add 60 days and the result is 2009-09-24 14:56:40.210

-- Example 3

select DATEADD(M,2,getdate()) -- M month, y year, d day

The DateAdd function adds months, years or days to date supplied

So you can see that adding 2 month to the current date and the result is 2009-09-26 14:57:48.117

see the differente results between example 2 and 3 ( 2 days), because in the example 2 we add 60 days not 2 month.

-- Example 4

select DATEDIFF(DAY,(DATEADD(M,2,getdate())),(GETDATE()+60))

The function DateDiff calculates the difference between to dates and returns int, using the examples that you saw the result is -2

-- Example 5

SET DATEFORMAT dmy;

GO

DECLARE @datevar datetime

Set @datevar=GETDATE()

select @datevar

On this example you will saw how to format the date to British format

-- Example 6

select substring((convert(varchar(10),@datevar,127)),1,10)

result 2009-07-26

Converting date type to output varchar(10) using parameter 127 ( yyyy-mm-dd)

-- Example 7

select substring((convert(varchar(10),@datevar,103)),1,10)

result 26/07/2009

Converting date type to output varchar(10) using parameter 103 ( yyyy-mm-dd)

See more about convert function in http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx

See more on Date on http://msdn.microsoft.com/en-us/library/aa258863(SQL.80).aspx

Post that can help you avoid some problems:

• How to solve problems with Identity 
• About SQL Bulk Insert
• Create Stored Procedure with Cursor

Anniversary of IT Tech Buz EN Technical

I would like to thanks our readers, sponsors and My wife and son for one more year with in a time of change.I see that our visitors are growing on a daily basis for me it's really a pleasure.
To improve I will need some feedback, only 2 questions !!!

Database Triggers

One more post about SQL Tips and Tricks that i think that have is own value. Today I write about Database Triggers, they are used for security reasons and audit operations, let's see the example.

Use Example_DB
go
Create TRIGGER [DBTRG_Test_Drop_Create]
ON DATABASE
FOR DDL_TABLE_VIEW_EVENTS
AS
DECLARE @data xml
DECLARE @cmd nvarchar(350)
DECLARE @logMsg nvarchar(400)

SET @data = eventdata()
SET @cmd = @data.value
('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(350)')
SET @logMsg = @cmd + ' (' + SYSTEM_USER + ' on ' + HOST_NAME() + ')'

RAISERROR (@logMsg, 10, 1) WITH LOG

-- To visualize the command uncomment the line bellow
-- select @cmd


-- Here we don't allow command that begin with "Create"
-- if the condition is true we do rollback

if left(@cmd,6)='Create'
rollback

-- Here we don't allow command that begin with "Drop"
-- if the condition is true we do rollback

if left(@cmd,4)='drop'
rollback

Note: We can create a table with type specified in variables and insert the attemps of create and drop objects.

Related Posts:

• Stored procedure to copy files

• SQL date manipulation

• Database Triggers

• Create SP with Cursor

Stored Procedure to Copy Files

Stored procedure to help you out to copy files see the code bellow

USE [aspnetdb] -- Replace with your DB

GO

/****** Object: StoredProcedure [dbo].[usp_copyfile] Script Date: 04/21/2009 09:22:20 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create procedure [dbo].[usp_copyfile]

@msg as varchar(200),

@input as varchar(100)

,@retorno as varchar(200) output

as

Begin

declare @drive as varchar(100)

declare @drive_dest as varchar(100)

declare @path as varchar(800)

declare @path_dest as varchar(800)

declare @cmd as varchar(800)

declare @ext as char(3)

set @path_dest='\Dest_Path\'

-- Para Exemplo, não esquecer barra de directorio final

-- Do not forget of blackslash

--

set @path='\PROGRA~1\DATA\'

set @drive='c:'

set @drive_dest='d:'

set @cmd='copy '+@drive+@path+@msg+@ext

set @cmd=@cmd+@drive_dest+@path_dest+ltrim(rtrim(@input))+(select cast(day(getdate()) as varchar(2)))

+(select cast(month(getdate()) as varchar(2)))

+(select cast(year(getdate()) as varchar(4)))

+replace((select convert(varchar(8),getdate(),108)),':','')+@ext

-- Para Testar Caminhos e Visualizar

-- For testing paths and to show

-- select @cmd

exec master..xp_cmdshell @cmd ,no_output

Set @retorno=@drive_dest+@path_dest+ltrim(rtrim(@input))+(select cast(day(getdate()) as varchar(2)))

+(select cast(month(getdate()) as varchar(2)))

+(select cast(year(getdate()) as varchar(4)))

+replace((select convert(varchar(8),getdate(),108)),':','')+@ext

end

Related Posts:

• Stored procedure to copy files

• SQL date manipulation

• Database Triggers

• Create SP with Cursor

More on Crystal Reports SQL Expressions and WhilePrintingRecords

I decided to talk about more on Crystal Reports and about SQL expressions fields that can be tricky I have made some screenshoots that can help YOU. See below

Let's see Closer the intruction remenber on

Convert text to varchar

• The formula bellow sums field1 group by field2

Whileprintingrecords;
sum ({table1.field1},{table1.field2})

• Sums two fields in the bellow example
  

WhilePrintingRecords;
({file.Qty1} + {file.Qty2})

more on these useful  Link


Related Posts:

• Crystal SQL Expressions
• Crystal Reports While Printing

Create SP with Cursor

These is my first post in the New Year of 2009, because i had lot's of thing in my professional side, you already notice the change in the logo and in the name of the Blog, i had register the domain www.ITTechBuZ.com e novo logo.
The logo, pretends to give the communication as the major role between Business and IT.
This blog continues to post for Tech people. Today I post one more of the saga of SQL Tip and Tricks about a Store procedure (SP) with a cursor inside to update maintance contracts

--
-- Creates SP com o nome sp_update_onsite
--
create procedure [dbo].[sp_update_onsite]
as Begin


--
-- Inserts ticket in a table when the status is 'Closed'
-- I use an Insert with a Select
--



INSERT INTO [HEAT].[dbo].[Tickets] ([CallId] ,[CallType] ,[CriData] ,[Valor] ,[Saldo] ,[UIDMANUT])


select d.callid,c.calltype,c.closeddate, d.horas_gastas, d.horas_saldo
,d.uidmanut,c.custid
from detail d
inner join calllog c on c.callid=d.callid
inner join subset s on s.callid=c.callid
inner join config cfg on cfg.u_idreg=d.uidmanut
where (c.calltype='Onsite' or c.calltype='Packs') and c.callstatus='Closed' and c.actualiza<>'SIM'
and c.callid not in (select callid from tickets )

--
--
-- Cursor used to update
--

-- Declares the Cursor Cursor_Tickets
-- and Select where it runs the Cursor

DECLARE Cursor_tickets CURSOR for
SELECT t.CallId,c.custid,t.Valor,t.UIDMANUT
from tickets t
inner join calllog c on c.callid=t.callid
where t.[CallType]='Onsite' and c.actualiza <>'SIM'

--
-- Declares the variables to pass througth
--

Declare @Callid varchar(8)
declare @custid varchar(50)
declare @valor decimal(17,2)
declare @uidmanut varchar(25)

set @callid=''
set @custid=''
set @valor=0
set @uidmanut=''


Open Cursor_tickets /* abrir o cursor */

fetch next from Cursor_tickets
into @callid,@custid,@valor,@uidmanut
while @@fetch_Status=0
begin

--- Updates hours spent
--- select @valor,@custid,@uidmanut (for Testing purposes)

update config set horas_gastas=isnull(@valor,0)+(isnull(horas_gastas,0)) ,horas_saldo=isnull(horas_saldo,0)-isnull(@valor,0)
where u_idreg=@uidmanut and custid=@custid

set @callid=''
set @custid=''
set @valor=0
set @uidmanut=''

fetch next from Cursor_Tickets
into @callid,@custid,@valor,@uidmanut
end
close Cursor_tickets
Deallocate Cursor_tickets

--
-- In the end updates the table for not processing agains
--

update calllog set actualiza='SIM' where callid in (select callid from tickets) and actualiza<>'SIM'


end

Merry Christmas and Happy New Year

I whish you all a merry christmas and a better New Year.

New Toolbar Feature

I was surfing around and i rediscover a free online toolbar for those who want just click on the image bellow or go to http://jconline.ourtoolbar.com/

Yes you can make your own !!!

Create trigger for a table After Inserted

These sp creates automatically a trigger when invoked by job in SQL Server, these is very usefull and handy.

Create procedure [dbo].[sp_creates_trigger]
as
begin



declare @text nvarchar (800 ) -- Declares a variable called texto

set @text='CREATE TRIGGER [subset_trig] ON [dbo].[Subtable] AFTER INSERT AS BEGIN SET NOCOUNT ON; declare @tfield as varchar(8) set @tfiled = (select id_num from inserted)
update subtable set ufield = @tifield where customer<>''Web'' and id_num=@tfield
END'
-- set the trigger in table subtable for an action After Inserted that updates the table
exec sp_executesql @texto -- executes sp_exceutesql with the text command
end

Google Analytics Blog: More Enterprise-Class Features Added To Google Analytics

Google Analytics Blog: More Enterprise-Class Features Added To Google Analytics


Still not available to all users but a huge step forward see the video


But we need to track an user and see what is the navigation of the content for example:

city ->New York -> Content visited
region ->California -> Content visited
Network location -> Tv Cabo Portugal-> Content visited
and so one with all the metrics including goal's, % exit's and Bounce Rate.

And with Advance segmentation it's huge step forward

Email Tracking with Google Analytics

Never was so simple to track your email such as Newsletter so i am going to explain you just how easy if you already don´t know, if i make a newsletter your links have to go like these

http://jorge.m.cunha.googlepages.com/newsletter3.html?utm_source=Newsletter&utm_medium=email&utm_content=Newsletter&utm_campaign=Newsletter3

so after the your page link or your link you to add ?utm_source=Newsletter&utm_medium=email&utm_content=Newsletter&utm_campaign=Newsletter3

- Where is green you have to made changes to your

- utm_source=Newsletter (Which action)

- utm_medium=email (Which medium)

- utm_content=Newsletter (What kind of content)

- utm_campaign=Newsletter_561 ( Which campaign)

Then you can see the results in your Google Analytics account

Don´t forget to publish the html file in your site and include the script of Google Analytics to track your page

Renaming SQL SERVER

When you need to change the name of the server in the operating system and you need to change the name of the SQL Server

1. First of all Backup your databases
2. select @@SERVERNAME then it returns the the name of the SQL Server
3. sp_dropserver 'jmc-pc' name of the server
4. go
5. sp_addserver 'ws-jmc', local
   
6. go
7. repeat step 2 after restarting SQL Server to check if the name of SQL Server is OK
8. More Information in http://msdn.microsoft.com/en-us/library/ms143799.aspx
   

Related Posts:

• Stored procedure to copy files

• SQL date manipulation

• Database Triggers

• Create SP with Cursor

My Holidays

I have been on holiday in Trás os Montes, Portugal and i share some photos

In Pinhão

Rio de Onor

Museum of Iberian Masc

Bragança seen from the Castel

Miranda Do Douro

Trancoso

How to Solve problems in SQL with Identity

I was working with identity and i had found an problem and a solution to solve these kind of problems.


To see which number is on the identity column:

--
-- See what is the Identity in the table Articles
--
DBCC CHECKIDENT

(

articles


)
Response is:
Checking identity information: current identity value '6', current column value '6'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

To setup with a new number on the identity column

DBCC CHECKIDENT

(

articles,reseed ,6000


)

it returns

Checking identity information: current identity value '1000', current column value '6000'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Note:
Don´t use truncate to delete table records it´s faster but it resets the identity column use instead the delete statement

Select Excel

I am going to show on select i use to read excel files XLS




Example:
Select *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:\import\Excel_file.xls;Extended Properties=Excel 8.0')...Sheet1$

To do a proper use of these you must have your excel with headings and these heading must be unique and where you see Sheet1$ is the name of your sheet in your excel remember to keep these name simple and without spaces. The columns must be well formated or you don´t get the results you expect.


Help the world go to http://www.ami.pt

Crystal Reports Tip While Printing Records

Crystal Reports

These tip is very useful when you need to evaluate on run time total's or if specify page footer only prints while the condition is true for example:

WhilePrintingRecords; Booleanvar myvar true

So you can do special calculations.

Crystal Reports SQL Expressions

Very nice explantion about SQL expressions in Crystal

The Crystal Reports Underground News: "Doing a SELECT in a SQL Expression field:


When teaching SQL expression fields I have always tried to stress that SQL Expressions are different from SQL Statements. A SQL Expression is a column in the report, where a SQL statement is a full query. My short version of this was to say 'a SQL Expression can't do a 'SELECT'. Well I recently learned that this is not precisely true. Under certain situations, a SQL Expression CAN do a completely separate select from the main report.

The main limitation is that it can only return a single value. So you probably will need a summary function. The following example comes from the Xtreme Sample Database:
(SELECT Max ( Orders.`Order ID`)
FROM `Orders` Orders)

Normally a CR SQL Expression would error on the SELECT, but if you put this expression in parentheses, Crystal will pass it to the database as a separate query. Amazingly, the column being queried does not even have to come from one of the tables in the report, but can be from another table in the database. In the past I would have recommended doing this via subreport. The advantage of a SQL Expression is that the value returned can be used to control things like Selecting, Sorting and Grouping in the report. To learn more about using complex SQL Expressions see my Experts Guide to SQL Expressions, Options and Commands."

Vista Status

Nowadays Vista has become more stable, in my
experience with Vista in English and in Portuguese I think that company's now can put in to dos list an adoption on Vista but we to consider some details first:

- Hardware
Hardware requirements to a smooth the PC's need at least 2 Gb of Ram and processor Duo core or smilar. You have to check if the hardware is Vista Compliant and check if you the drivers. You can follow these link
http://www.microsoft.com/downloads/details.aspx?
FamilyId=67240B76-3148-4E49-943D-4D9EA7F77730&displaylang=en
To do an inventory in your network such as hardware inventory, compatibility list and reporting.
For those who want to do on single XP PC you can follow these link for microsoft http://www.microsoft.com/downloads/details.aspx?
displaylang=en&FamilyID=42b5ac83-c24f-4863-a389-3ffc194924f8


- Software

In these area you to make an inventory and see if any software that you have isn't compatible with Vista and you must have a special concern regarding legacy applications.
The next step is to do pilot critical application to your business to make these transition smooth like river with no obstacles

If your site PC's are for the major Vendors you should consider to go to the vendor website.


the version of Vista that i recommend is Vista Business
of course in English, because is the most tested and native for the OS





JC

Friend Connect, Open Social, and Ning

Good post relating social Networking

read more | digg story

Google Custom Search and my work

I have been very busy in my work so today i am only speak about Google Co-op and how they contribute to do a better job as you could see in the images below

If you can contribute with a better search option please tell me about i can improve