You are currently browsing the category archive for the ‘SQL Server 2008’ category.

Quite often during data conversion processes, I am required to get only the date part from a datetime or a timestamp value. Here is a trick that does not involve CASTing and is quick to respond:

DATEADD(DAY, 0, DATEDIFF(DAY, 0, <ColumnName>))

Here is how it works: Whether smalldatetime or datetime, DATEDIFF(DAY, 0 returns the number of days since the base date, and DATEADD(DAY, 0 returns the integer as a date value.

Neat, isn’t it ?

Use this query to verify the details on the SQL Server:

SELECT
SERVERPROPERTY(‘ProductVersion’) AS ProductVersion,
SERVERPROPERTY(‘ProductLevel’) AS ProductLevel,
SERVERPROPERTY(‘Edition’) AS Edition;

Of course, there are other ways, but the above is the most ‘coherent’.

Other convenient methods:

  • SELECT @@VERSION
  • Check the file version of sqlservr.exe

SQL Server Surface area configuration is deprecated in SQL Server 2008, so this is how you should enable adhoc queries by setting OPENROWSET and OPENDATASOURCE options.

Run the following commands, one at a time:

sp_configure “show advanced options”,1

Output: Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

reconfigure

Output: Command(s) completed successfully.

sp_configure “Ad Hoc Distributed Queries”,1

Output: Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.

reconfigure

Output: Command(s) completed successfully.

And that is it.

Timeline

August 2017
M T W T F S S
« Mar    
 123456
78910111213
14151617181920
21222324252627
28293031  

Blog Stats

  • 42,577 hits