|
Don't write your SQL Statements in a culture specific way. Use the ISO date format (yyyy-mm-dd). Or, if the date is coming from an application, use parameters and let ADO.NET do the conversion for you.
|
|
|
|
|
I changed my code. Now it wirks fine...
Colin, I thank you a lot
|
|
|
|
|
I have an SQL statement that works for one database but when I try it on a microsoft access database it doesn't work.
SELECT DIGITS(field1) FROM myTable
The DIGITS() function will convert the field1 from a numeric into a string, but will ensure that the size is maintained. For example, if field1 contained the number 12 and the size of the field was 5 (precision 5, scale 0), this select statement would return 00012.
So my question is, what can I use that is valid in access SQL to accomplish the same goal?
|
|
|
|
|
You can try the Format function. For example,
SELECT Format(field1,"00000") from myTable
should give you all the numbers in field1 with 0 prefixed.
Hope this helps
Chandra
|
|
|
|
|
That is exactly what I was looking for. Thanks!
|
|
|
|
|
Hi,
I have an application that uses ODBC to connect to a SQL Server database. The database includes a number of tables which have columns (of datetime type) to timestamp the row entry with the current date and time. There is no way to get the current date/time from within my application, and so include it in my SQL INSERT INTO statement. Is there a way to have the SQL server automatically insert a datetime value? I have looked at the timestamp column, but I need the actual date and time.
Thanks,
Alice
|
|
|
|
|
CREATE TABLE [MyTestTable] (
[IDColumn] [int] IDENTITY (1, 1) NOT NULL ,
<code>[DateCreated] [datetime] NOT NULL CONSTRAINT [DF_MyTestTable_DateCreated] DEFAULT (getdate())</code> ,
CONSTRAINT [PK_MyTestTable] PRIMARY KEY CLUSTERED
(
[IDColumn]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Or, in the Enterprise Manager's Design Table window, select the datetime column and set the default value to (getdate()).
-- modified at 9:40 Friday 27th October, 2006
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
EricDV wrote: [DateCreated] AS (getdate()) ,
Surely that defines a computed column, in which case the date will update each time you select rows from that table?
What you need to do is create a default value so that if no date is supplied in an INSERT statement it defaults to the time of the INSERT.
|
|
|
|
|
Dammit man! You caught me again! 
I'm gonna have to slow down and think about what I'm posting. 
(Thanks for your correction.)
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
EricDV wrote: You caught me again!
No problem 
EricDV wrote: I'm gonna have to slow down and think about what I'm posting.
Don't worry about it - It happens from time-to-time.
|
|
|
|
|
That sounds like what I need... how do I do that?
Many thanks,
A
|
|
|
|
|
|
Thanks every so much Colin and Eric. I think you've fixed me.
Best regards,
Alice
|
|
|
|
|
Hello im confused. i do not understand the difference between
ADO, SQL, ACCESS, MICROSOFT SQL SERVER, VBA
i need to develop data-base project and do not know where to start and what is the newest methoods
thank you
|
|
|
|
|
Haoman17 wrote: ADO
ActiveX data objects
Haoman17 wrote: SQL
Structured Query Language
Haoman17 wrote: ACCESS
A desktop database procuct by Microsoft
Haoman17 wrote: MICROSOFT SQL SERVER
A server database product by Microsoft
Haoman17 wrote: VBA
Visual Basic for Applications.
Haoman17 wrote: i need to develop data-base project and do not know where to start and what is the newest methoods
SQL Server 2005 with Visual Studio 2005 using C# or VB.NET
Start with SQL Server 2005 Express (it is free) and one of the Visual Studio Express editions (they are free also)
|
|
|
|
|
Thank you, but i dont understand, should i use sql or ado?
|
|
|
|
|
In the nicest possible fashion, if you don't know what these are, you probably should get some reading material before embarking on a database project...
ADO is a method used to connect to a database (superseded by ADO.net in the .net suite of languages).
Friday afternoons - should connect brain before thinking...
ADO contains a set of functions that are used to connect to and query a database. (ADO.net is the newer version of this).
SQL is a language used within a database to query the data.
|
|
|
|
|
You should use ADO.NET. You will also use SQL because ADO.NET is a way of sending SQL from a .NET application to a database.
|
|
|
|
|
Well, then there is NJDX, which is beyond ADO and SQL.
Damodar Periwal
Software Tree, Inc.
Simplify Data Integration
http://www.softwaretree.com
|
|
|
|
|
|
i not sure wat this error mean it just not working with my SQL
ODBC: Msg 0, Level 19, State 1<br />
SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.<br />
<br />
Connection Broken
Here is my SQL command, the underline code which i added then the error came out. B4 add the command so working correctly
SELECT Query1.bizdate, TSuper,TSoft, THard, (TSoft+ THard+ TSuper) as TOTAL ,<br />
((TSoft+ THard+ TSuper)-(TsCost+ThCost+TsuCost))as GP <br />
FROM <br />
(SELECT bizdate, SUM(totalsales+returnamt) AS TSoft ,sum(totalcost+returncost)as TsCost FROM tot_item WHERE deptcode IN (11, 12, 13, 14, 15, 16, 17) <br />
GROUP BY bizdate) AS Query1<br />
<br />
LEFT JOIN (SELECT bizdate, SUM(totalsales+returnamt) AS THard ,sum(totalcost+returncost)as ThCost FROM tot_item WHERE deptcode IN (21, 22, 23, 24, 25, 26, 27, 28, 29) <br />
GROUP BY bizdate) AS Query2 ON Query1.bizdate = Query2.bizdate<br />
LEFT JOIN (SELECT bizdate, sum(totalsales+returnamt) AS TSuper ,sum(totalcost+returncost)as TsuCost FROM tot_item WHERE deptcode IN (30, 40, 50, 60, 70, 80, 90) <br />
GROUP BY bizdate) AS Query3 ON Query1.bizdate = Query3.bizdate<br />
<br />
where query1.bizdate>= '2006-10-01' and query1.bizdate<= '2006-10-15'<br />
order by query1.bizdate
|
|
|
|
|
Contact Microsoft support. There's a bug somewhere in your copy of SQL Server which is causing an error to occur in the database engine.
You should first ensure that you're running one of the supported service packs. For SQL Server 2000 this is SP3a or SP4, for SQL Server 2005 it's either the original (RTM) release or SP1.
|
|
|
|
|
Mike what you pointed out could be the reason. But this need not be the so in this case. MS KB Article ID: 892451 might have been what you were referring to. The dump also occurs in case of excessive memory usage.
Thanks and Regards,
Aby
|
|
|
|
|
I am using xml(.) datatype in sql ser 2005
I want to get the value as string without using dataset
because the data is very huge, if i took by dataset the end values were truncated
i want to get the value directly inside a stored procedure and to pass the value as an input to openXML in the same stored procedure
Please guide me
-
|
|
|
|
|
Hi All,
Glad to be here!
Very strange results from DataReader... I'm using the code from MS examples to populate ListBox with items from SQL 2000 CE 2.0 data file. And if "n" is number of records in database I always get "n-1" records from DataReader!
Otherwise, DataSet returns all needed records with the same SQL string!
I'm on VB.Net 2005
Thanks in advance!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
