|
Hi,
I am using DateTime data type in my SQL Server 2000 to store the date.
When I am retrieving the date from my DB, it will appended with TimeStamp
eg. 01/04/2007 12:00:00 . But I wish to display only the Date in the TextBox of my Web Page.
I am confused whether I can use Char or Varchar data type in my SQL Server DB to store Date Value or Any other way to convert the DateTime format of SQL Server into Date only format.
Kindly help me.
Regards,
Jay 
|
|
|
|
|
Hi
Use This
select
Convert(varchar(12),DateTime,101) AS DateTime
from
Student
this will work for you
|
|
|
|
|
Jay_se wrote: When I am retrieving the date from my DB, it will appended with TimeStamp
eg. 01/04/2007 12:00:00 . But I wish to display only the Date in the TextBox of my Web Page.
I am confused whether I can use Char or Varchar data type in my SQL Server DB to store Date Value or Any other way to convert the DateTime format of SQL Server into Date only format.
It is considered bad practice to store dates as strings. SQL Server has a DATETIME column type that you can use that is culture indepenent.
It is better to render the date in a culture specific way in the application's presentation layer. When a DATETIME column is read into a .NET application you get a .NET DateTime object. You can use its ToString() method to define exactly how you want the date to be formatted. e.g.
myDate.ToString("dd/MM/yyyy");
A quick reference off the top of my head:
d: day
dd: day with leading zero
h: hour (12)
hh: hour with leading zero (12)
H: hour (24)
HH: hour with leading zero (24)
m: minute
mm: minute with leading zero
M: month
MM: month with leading zero
MMM: short month name
MMMM: long month name
yy: short year
yyyy: long year with century
|
|
|
|
|
Thanks Mr.Colin,
Its very much helpful for me to implement in efficient way.
Regards,
Jay
|
|
|
|
|
Hello,
I am trying to connect to a sql server 2005.
Under the surface server configuration - remote connections i have set it for named pipes and tcp/ip. Is there something else i should do with the configuration?
I have also turned of the firewall, in case that has caused a problem.
my connection string is below.
<br />
Try<br />
cnn.ConnectionString = "data source=1.1.1.1,1433\ssd01;Network Library=DBMSSOCN;database=serviceAcc; user id=; pwd=;persist security info=True; encrypt=yes"<br />
<br />
Catch ex As Exception<br />
Console.WriteLine(ex.Message)<br />
End Try<br />
I have changed this a few time and the error message:
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)"
Thanks in advance,
Steve
|
|
|
|
|
Hi,
It seems that you are using named instance. In such a case you should check what is the port of that named instance in order to point your connection string correctly.
For example, if your port number is 1550 you should specify the connection string as follows:
Network Library=dbmssocn;Data Source=127.0.0.1,1550;...
Hope it helps,
Uri
|
|
|
|
|
Using SQL Server (2005), and performing an INNER JOIN, let's say...
SELECT * FROM Employee E INNER JOIN Address A ON E.EmployeeId=A.EmployeeId WHERE E.Department='Sales'
It has occurred to me to wonder; mightn't it be more efficient to write that as...
SELECT * FROM Employee E INNER JOIN Address A ON E.Department='Sales' AND
E.EmployeeId=A.EmployeeId
But then I think; surely the engine is smart enough to optimize something so simple if it makes a difference.
I've tried executing a few of my joins both ways, and there's little difference between the two forms.
Can anyone here tell me whether or not such an optimization is performed? Or worthwhile?
|
|
|
|
|
I'd write it this way instead
select e.*
from employee e
where e.department = 'Sales'
and exists ( select a.EmployeeId
from Address a
where e.EmployeeId = a.EmployeeId
)
;
I've always found using the 'exists' clause with a second query to be more performant than trying to use a join (even though logically they are equivalent).
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
Ah, right, but that doesn't perform the join.
|
|
|
|
|
The two statements should be the same. Check the Execution Plan (CTRL + M).
|
|
|
|
|
That only seems to show the indices and such involved, which are of course the same.
My interest is more concerned with how big a temporary table gets generated by the query.
The first form may generate a very large temporary table and then pare it down based on the WHERE clause,
whereas the second form should generate a much smaller temporary table to begin with.
Look at it this way: what would you do if asked, "make a list of all the addresses for all the employees and give me the ones for the sales personnel"? Wouldn't you simply ask only the sales personnel and call it good?
|
|
|
|
|
That is where the cost estimator comes in. The query optimizer will choose the same plan either way based on the cost. If the table statistics (or the lack thereof) show that it will be more effective to filter the results from the employee table first then join to addresses it will do that in both cases. However, if the number of rows in the address table is less than the number of employees in the sales department then the optimizer may decide to pull all the rows from the address table and use them to filter the results from the employee table and then apply the filter on department.
The question is simply: how will the query optimizer interpret the statement based on the data provided by the cost estimator? Unless you enforce join order by using a join hint, the query optimizer will choose the best join order for the plan based on the cost data. If you want to enforce join order, then you will effectively tie the optimizer's hands and bend it to your will.
But no matter what you do the temporary table from the employee table will only ever contain the rows from the sales department. The statement is only parsed in the order you write it, then it becomes a data structure used by the query optimizer which is why, in this case it doesn't matter weither your filter is contained w/in the WHERE clause or the JOIN clause.
If you use SET STATISTICS IO ON you will see that the logical IO is the same either way, combine this with the execution plan of both queries and that tells me that there is no difference between the two statements at all.
|
|
|
|
|
plz guys
i need a help
how to put sql server instances running on the local area network in a combo or a list in vb.net 2003 and not in 2005.
i can put them in 2005 but in 2003 i can't
and i need it badly
plz plz plz
-- modified at 6:52 Thursday 4th January, 2007
|
|
|
|
|
|
Hi
I am learing ADO at the moment have just created a simple application to display & update the data in access data base. I have used data connection wizard to connect to the database. I dragged the table on to the form from the datasource viewer to create a simple datagrid along with navigator control. But now the problem is that when i add the new data in the datagrid and try to save it using the default save button on the navigator window, The changes just dosent get saved. Can anyone tell me why?. I am using C# 2005 express edition. Below it the details code that is created automatically for save item click
private void addressDataBindingNavigatorSaveItem_Click_1(object sender, EventArgs e)
{
this.Validate();
this.addressDataBindingSource.EndEdit();
this.addressDataTableAdapter.Update(this.addressBookDataSet.AddressData);
}
Thanks
Irfan
I am still learning
|
|
|
|
|
Verify that the event handler has been registered.
You can either :
1) set a break point in the addressDataBindingNavigatorSaveItem_Click_1 method and make sure the method is being called
2) or, look at the properties of the save button in the designer window under events. Make sure the click event has the method name as the property value.
If you try #1 and the break point isn't hit, you'll need to try #2.
|
|
|
|
|
Hi
I have check both the point. They are fine. I have tried dubugging it to make sure the on click is called & it is. It goes through the update method fine but for some reason just dosent save it
I am still learning
|
|
|
|
|
Hi, did anyone found the way how to do bulk insert on Sql Mobile 2005? MS announced bcp as a part of SqlMobile2005 but i did not find bcp.exe and SQL command like BULK INSERT are not supported ;(
|
|
|
|
|
Hello All
I need to create a SQL Query of follwing but this not work
can anyone help me to give actual query is has been work
my SQL Query is ---
select * from t_ItemPriceMaster Group BY c_DateForm Having ( ( max( c_DateForm )and c_SoftDelete='1')";
thanks for your time
|
|
|
|
|
Everything you are selecting must be in either aggregate function and/or in GROUP BY clause
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it! 
|
|
|
|
|
jaganil wrote: my SQL Query is ---
select * from t_ItemPriceMaster Group BY c_DateForm Having ( ( max( c_DateForm )and c_SoftDelete='1')";
You have an extra "(" in the query
|
|
|
|
|
Hi Friends,
I am Vijayakumar, I imported one excel file into Ms-sql it is around 2MB. After imported it's grew upto 20 MB. We didn't do any modification in MS-sql. Can anybody please help me in this regard how it grew automatically.
With regards,
Vijay
|
|
|
|
|
What's expanded? The excel file. The size of the database? We need more information before we can give you a definitive answer.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I have used the SQL Server Management Studio Express to set the default database and log file locations to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data. I then use this string to try to create a database "If Not Exists(Select * From sys.databases Where Name ='DB001') CREATE DATABASE BD001 ON PRIMARY (NAME = DB001, FILENAME = 'DB001.mdf', SIZE = 2MB, MAXSIZE = 2GB, FILEGROWTH = 10%) LOG ON (NAME = DB001_Log, FILENAME = 'DB001.ldf', SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 10%)". However, I get the following exception: "CREATE DATABASE failed. Some file names listed could not be created. Check related errors." If I specify the entire path in the create string, then I can create the database without any problems. The problem I have is that when I deploy the application, I won't know the actual database path. I was under the impression that if I set the default database and log file locations using SQL Server Management Studio Express, then I don't need to specify the path in my create database string. Any help or explanation would be appreciated, thanks.
|
|
|
|
|
Hi All,
I want to write a script/tool which would update the schema of the existing database (in SQL Server 2005)
i.e Add/drop a table, column, constraint etc. on a ongoing basis.
The input to my tool would be the latest_schema script which creates the database with all the latest tables, columns, constraints etc.
So the tool that I am going to write should perform a schema-diff with my db Vs the script provided and add/drop the tables, cols, constraints on my database.
I have a couple of solutions in my mind but feel that they are not very efficient.
1. Create a new database running the provided script, write a stored procedure to loop thro all the tables to figure out the diff between the existing db and the current db and update the current db: Problem here is my current db has data and lots of constraints, which might pose a problem.
2. Use some third party tools to generate the sync script and execute it against my current db but I am not sure about the free tools available out there.
Can somebody please provide me some guidelines/tips on how to go ahead about this.
Thanks in advance.
PJ
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
