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

June 2017
M T W T F S S
« Mar    
 1234
567891011
12131415161718
19202122232425
2627282930  

Blog Stats

  • 42,228 hits