Skip to content

Getting the date difference in MS SQL

February 4, 2013

Today, I had been sorting out more date time differences between two dates using MS SQL with batch scripting.

To get the time difference in seconds with an update, here’s the script: –

DECLARE @INT INT
UPDATE [portal].[dbo].[sessions]
SET @INT = DATEDIFF(SECOND,StartTime,EndTime), SessionLength = @INT

This sets a variable called @INT, and assigns the seconds differences between EndTime and StartTime into the column SessionLength.

I then had to incorporate the date difference of one day between two dates using MS SQL in batch scripting, and from my previous post getting yesterday from a date in batch scripting isn’t easy. So instead use   the mssql command, its especially useful for within looping functions.

DECLARE @INT INT
UPDATE [database].[dbo].[sessions]
SET @INT = DATEDIFF(SECOND,StartTime,EndTime), SessionLength = @INT

SQLCMD -S DATABASE-SERVER -E -Q “set nocount on; set ansi_warnings off; select * from [database_name].[dbo].[sessions] where StartTime >= DATEADD(Day, 0, DATEDIFF(Day , 0, GetDate() – 1)) AND StartTime <= DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate()))

That’s it useful for getting today’s date and yesterday’s date inside the table sessions.

 

Advertisements

From → Uncategorized

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: