|
You are nearly there try this
<br />
select Convert(DATETIME, '17/01/2006 14:52:48')<br />
There are options that you can use as well; as described below.
example convertimg German date for today without time
<br />
select Convert(DATETIME, CAST(Day(getdate())as char(2)) + '.' + CAST(Month(getdate()) as char(2)) + '.' + CAST(Year(getdate())as char(4)) , 104)<br />
Without century (yy) (1) With century (yyyy) Standard Input/Output (3)
0 or 100 (1, 2) Default mon dd yyyy hh:miAM (or PM)
1 101 U.S. mm/dd/yyyy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 (1) - dd mon yy
7 107 (1) - Mon dd, yy
8 108 - hh:mm:ss -
9 or 109 (1, 2) Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 JAPAN yy/mm/dd
12 112 ISO yymmdd
13 or 113 (1, 2) Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
20 or 120 (2) ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
21 or 121 (2) ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)
126 (4) ISO8601 yyyy-mm-ddThh:mm:ss.mmm (no spaces)
127(6) ISO8601 with time zone Z. yyyy-mm-ddThh:mm:ss.mmmZ (no spaces)
130 (1, 2) Hijri (5) dd mon yyyy hh:mi:ss:mmmAM
131 (2) Hijri (5) dd/mm/yy hh:mi:ss:mmmAM
-- modified at 7:05 Tuesday 8th August, 2006
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
For consistent date-time conversion behaviour, always use the ISO format: yyyyMMdd hh:mm:ss. All other formats are subject to the user's current locale setting.
Best practice when passing dates from an application into a query is to use parameterised queries with parameter objects. For example, in ADO.NET with SQL Server, use @name placeholders to mark the parameters, and SqlParameter objects to set the values of the parameters.
|
|
|
|
|
i need to select random row from table in my database to dir the contents of that row every time i dir different row how?!!!!!!!!!!!!!!!!
ma_refay
|
|
|
|
|
You can use newID() if your database is sqlserver for this purpose. It will give you random row from the table.
Best Regards,
Apurva Kaushal
|
|
|
|
|
NEWID() on its own won't give you a random row. It will only give you a new GUID.
|
|
|
|
|
But if we want to get the random row from a particular table then can't we get that with the help of newID().
Best Regards,
Apurva Kaushal
|
|
|
|
|
SELECT TOP 1 * FROM Table1 ORDER BY newid()
|
|
|
|
|
Make as stored procedure:
CREATE PROCEDURE GetRandomRow AS
declare @nRand int
declare @cSQL varchar(200)
select @nRand = 1 + (rand() * (select count(*) from MyTestTable))
select @cSQL = 'SELECT TOP 1 * FROM (SELECT TOP ' + cast(@nRand as varchar)
+ ' * FROM MyTestTable ORDER BY IDColumn ASC) as tbl ORDER BY IDColumn DESC'
exec(@cSQL)
You could send in the table name and ORDER BY field as parameters.
--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
|
|
|
|
|
Your website is getting so many hits and visitors are running so many queries Temp DB logs starts growing exponentially thus your sites starts slowing down and even crashing. What would be the most effecient way to quickly clear temp DB without restarting the servers. I wrote this script which worked but it took a while, but it helped. Would you use another method is this script the best its gonna get?
DBCC SHRINKFILE(‘YourDataBase_log’)
BACKUP LOG YourDataBase WITH TRUNCATE_ONLY
After that is done if the log files still have not shrunken then I do this:
SET NOCOUNT ON
CREATE TABLE dummy
(a VARCHAR(8000))
WHILE (0 < 1)
BEGIN
INSERT INTO dummy SELECT REPLICATE(‘a’, 8000)
DELETE FROM dummy
END
-- modified at 0:45 Tuesday 8th August, 2006
Greg
Coding makes the world go round!!!
|
|
|
|
|
Put tempdb on a seperate diskdrive if possible and this will also improve performance.
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
I have been reading this article, but can't seem to get my application to do what I need.
Here is the situation :
Data parsed from text file, dumped into table. User selects options for the data he needs. Data dumped into datagrid via dataset. I want the user to be able to edit info in the datagrid and save the changes to the dataset, which will now be dumped into a table to be used with Word.
I can't seem to find a way to save the changes to the dataset.
Any help very well appreciated.
Jude
|
|
|
|
|
|
Thanx for the reply...it was human error..I was calling the function to update the database before the changes were made in the datagrid
Jude
|
|
|
|
|
Hi All,
Can anyone tell me the order in which sql Query executes
Example:
select C.*,RenewalDate=(select * From CustomerApprovals where CustNo=1 Order By ApprovalDate Desc)
from Customers C where C.CustNo=1
|
|
|
|
|
All at once - SQL is a set based language not a procedural language, although for certain things you can act in a procedural manner (e.g. using cursors) but this is slow because it is not using the strengths of the language.
Basically a SELECT statement is telling SQL Server what you want, and then SQL Server figures out itself how to achive the result you want.
If it makes it any easier for you to think about you can think of the subquery executing first. However, the reality is that many parts of the query will execute in parallel.
Aside from that I cannot see what you are tying to do with your statement because it is invalid.
|
|
|
|
|
Look at the execution plan to see the order in which it will be executed.
A man said to the universe:
"Sir I exist!"
"However," replied the Universe, "The fact has not created in me A sense of obligation."
-- Stephen Crane
|
|
|
|
|
Hi
I want to create a report that lists the customers and all the items that they have not bought from us for a certain period. I have 3 relevant tables, CustomerMaster, InventoryMaster, SalesDetail.
SalesDetail holds all the sale info, for example
CustomerCode : 1AFR204
StockCode : FRA202
QtyInvoiced : 2
NetSalesValue: 1295
Year : 2006
Month : 7
The master tables holds all customers and inventory.
I want the report to basicaly look like this:
Ultra liquors Newton Park
Not listed:
Boland Cabernet
Spier Sauvignon Blanc
To be able to do this I need a recordset that consists of the stockcode and customercode of items not sold.
Any ideas how to do this? I have been playing with not in subqueries. Its easy to display items not sold or customers that did not buy, seperately, but to combine them is a different animal.
Thanks
Izak
|
|
|
|
|
SELECT CM.CustomerCode, IM.StockCode
FROM CustomerMaster CM, InventoryMaster IM
WHERE CM.CustomerCode NOT IN(SELECT SD.CustomerCode
FROM SalesDetail SD
WHERE SD.StockCode = IM.StockCode
AND SD.Year = 2006
AND (SD.Month >= 6 and SD.Month <= 7))
ORDER BY CustomerCode
--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
|
|
|
|
|
Thanks!
If I understand it correctly, the customermaster and the inventorymaster creates all possible combinations except for the combinations in the NOT IN clause?
So...
If customermaster has 200 records and inventorymaster has 100 records and the salesdetal has 750 distinct records. 200 * 100 = 20000 - 750 = 19250, the result will have 19250 possible sales combinations that did not happen.
Am my logic correct?
Thanks again Eric, this has been very helpfull! 
Izak
|
|
|
|
|
Izak - wrote: Am my logic correct?
Seems so to me.
--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
|
|
|
|
|
I want use the msde bootstrapper with my app to install both on my client machine. Currently I use the .netframework bootstrapper to install check if the framework is on the client machine, if not it must be installed first, now I want to add the msde to the installation, so that it installs the msde from the setup.exe, then checks for the framework and finnaly install my app.
Does anyone know how I can do this??
Thanx in advance
He who laughs last is a bit on the slow side
|
|
|
|
|
Check this useful link
http://codeproject.com/managedcpp/dotnetsetup.asp
|
|
|
|
|
Because I'm an beginner to NHibernate.
So I don't know what i need to do before running an very easy example success .
|
|
|
|
|
Hi all,
I want to create dynamic tree view menu in HTML by javacript.The condition is user don't know how many subfolders or files are in a root folder.
please help me .It's very urgent.
Regards & Thanks
Sunil
|
|
|
|
|
This question doesn't appear to have anything to do with databases. Pehaps you might find an answer if you post in an appropriate forum[^]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
