Easy way to strip time part in sql date time

Most of the times we wouldn't want the time part of the dates to be stored or retrieved when dealing with only days. But the data type "datetime" in sql always includes the time even though you don't want it. There is no other data type in sql to store or retrieve the date without time. In this case we need to strip the time part from the date and store the remaining in database. To do this favor, I have come up with the below sql code which strips the time part from the date time.

declare @date datetime;
set @date = getdate();
select cast(convert(varchar, @date, 101) as datetime) --Strips time element from the date time

The above code will work like this:
If you have the date time 25/09/2009 12:36:40:654 then this will be converted as 25/09/2009 00:00:00:000

The user is not associated with a trusted SQL Server connection. (.Net SqlClient Data Provider)

Every time I install sql server, in the initial days I used to face problems in logging on by sql server authentication mode.

These were the steps I used to follow to create new login in order to logon using sql server authentication mode.

1. Log on to the server using windows authentication mode and create some login in the section of Sqlserver->Security->Logins->(Right click)NewLogin.

2. Restart the sql server and disconnect it.

3. This time connect to the server using the sql server authentication mode by entering new login credentials.

But it never used to work. The error I receive was “The user is not associated with a trusted SQL Server connection. (.Net SqlClient Data Provider)”.

After doing a long research on this I found the solution to this. To make sql server authentication work we need to configure the sql server to use Sql server and windows authentication mode. The following steps will solve the problem.

1. Right click on the root node of the sql server and select properties in the context menu.

2. That will open a popup window which will have server level settings.

3. Click on security tab it will show the view as in the following figure. There you have to make sure you have “Sql server and Windows authentication mode” is selected.

4. Press OK and restart the sql server again.

5. This time the sql server authentication mode using the new login credential will work.

Note: You have to make sure your login has server role as “SysAdmin” in order to logon.

How to access Remote sql server’s database table

Recently I got a requirement to migrate data from other sql server’s database to my local sql server’s database. Then I tried querying like this.

select * from ServerName.DataBaseName.dbo.TableName

When I ran the query “select * from FilterDB011.FilterWebDB.dbo.SiteUser” I got an error saying

“Could not find server ‘FilterDB011′ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.”

Then I ran the query ” execute sp_addlinkedserver FilterDB011″ as the error message suggested.

Then It worked fine.

Now I am able to access the tables and databases from that remote database server.

The syntax to add a remote sql server to sys.servers is:

execute sp_addlinkedserver sqlserverName

The syntax to access a table in remote sql server is:

SeverName.DatabaseName.dbo.TableName