How to: Extract only Date Part From DateTime

It is common practice to extract only date part from datetime format, such as for a datetime of 2014-05-30 15:31:58.860, only 2014-05-30 is returned. Today we will show three main methods to choose from.

Method 1:

SELECT convert(DATE, GETDATE() )

Method 2:

SELECT CONVERT(VARCHAR(10),GETDATE(),126)

Method 3:

SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))

will mask the time part as 00:00:00.000.  It sometimes is regarded as better performance here.

SSIS's BULK insert error msg

Problem

When you are trying Bulk insert from csv file, here is the error message you may run into:

“Could not bulk insert because file 'C:csv_filename.txt' could not be opened. Operating system error code 3(The system cannot find the path specified.).”

Solution

First of all, confirm that you are specifying the UNC (Universal Naming Convention) path and not just drive letters. If you are trying to create the file to a remote location then the path should follow UNC, i.e:

             \\Server_Name or IP_ADDRESS\Shared_Folder_PATH(Name)\FILE_NAME

Note that the path may be OK if you are trying to create the file on the SAME server running SQL Server.

Secondly, make sure that the SQL Server service account has permissions to SQL Server instance.
For doing this you can use a Domain user or, create a new local user and start the services with that account.
Go to the lower left corner of the desktop, click START, input “services.msc”, then choose “SQL Server” as shown below:

Check Log On AS, a new window will pop up.

The default setting is Local System account. Try changing it with a domain user account and restart SQL Server Service after new changes.

Last but not least, make sure that this account has Read and Write permissions on the folder where you are creating the file. To do so, right click on the folder --> sharing and security --> permissions.

That’s it! Now the error message should have been swept away.

SSAS error msg: Unable to retrieve data mining algorithms

Problem

When you tried to create a new mining structure in SSAS, you may notice a warning message appears in the window as shown below:

“Unable to retrieve a list of supported data mining algorithms. Make sure you are connected to the correct Analysis Services server instance and the Analysis Services server named localhost is running and configured properly. You can continue with a default list of data mining algorithms.”

Solution:

Step 1: Check Analysis Service has been started from SQL Server Configuration Manager.

Step 2: Check the deployment property to make sure that it has been set up correctly.