|
Hello!
I have tested this code in SQL Query Analyser:
--/////////////////////////////////////////////
CREATE TABLE TESTDATE(
T_ID INT NOT NULL PRIMARY KEY,
TDATE DATETIME
)
-- ///////////////////////////////////////////////////
INSERT INTO TESTDATE(T_ID,TDATE) VALUES(1,'12/07/2003')
INSERT INTO TESTDATE(T_ID,TDATE) VALUES(2,'13/07/2003')
First row is inserted correctly (with date '12/07/2003') but while inserting
second row with date '13/07/2003' it gives following error:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated
My date is of formate DD/MM/YYYY but SQL Server is reading it as MM/DD/YY.
and not accepting 13 as a valid month.....
How can i resolve this problem?
colgateas900197
|
|
|
|
|
You should write SQL to be culture independent. It is the reponsibility of the Presentation layer to deal with rendering and parsing culture dependent information. You should use the ISO format when writing SQL Scripts e.g. 2007-02-14
|
|
|
|
|
You are right!
But i think i have to discuss my problem in detail!
It was a test code... Actually Database is created in ACCESS and date is stored as text in previous database. While generating reports using access database i use 'Weekday' function to get day i.e SUNDAY for 12/07/2003.
Now, I have fetched data from ACCESS to SQL server and again i put date as string using vnarchar(10) datatype. When i use
datename(dw,DateFieldName) to get name of day on specific date i get an 'Arithmatic Overflow Error'
But when i created this Test code(with datefield with datatype datetime) i have posted it was also creating problem!
I am sorry! i have written too much... but the only thing i want to do is
"Date is stored in SQL table as string with date formate DD/MM/YYYY (13/07/2003) and i want to get the name of day i.e SUNDAY"
Thank you for your response! and again i hope you will help me..
colgateas900197
|
|
|
|
|
You can use "SET DATEFORMAT dmy". However using a date format of "20070214" or "14 feb 2007" means that you don't have to muck-around with the dateformat setting.
Andy
|
|
|
|
|
Thanks!!
It worked.
colgateas900197
|
|
|
|
|
Hi,
At the top of the stored procedure, change the dateformat as given below. Hope this helps 
SYNTAX:
SET DATEFORMAT dmy
GO
For Example:-
SET DATEFORMAT dmy
GO
DECLARE @datevar datetime
SET @datevar = '13/07/2003'
SELECT @datevar
GO
- Harini
|
|
|
|
|
Please see this database diagram. I need your suggestion on the database I have created.
http://www.geocities.com/amit_logicon/diagram.jpg
Thank you
|
|
|
|
|
Very pretty.
I'm not sure what you want us to comment on? The fact that you have odd field names? (Why have VCode when Code will do just as well)? The fact that you have address information in multiple tables, suggesting that this should be in its own table?
Without more information about what you want, there's not a lot that we can do.
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.
|
|
|
|
|
VCode to differentiate in queries that it is Code of Vendor. I need review on its design. I have kept address of different entities in different table, i.e., Address of Vendors, Branches in respective tables.
I was asking for your review because I am having really great difficulties in creating queries.
|
|
|
|
|
amit_logicon wrote: VCode to differentiate in queries that it is Code of Vendor
Yup. Not being an idiot and able to read database diagrams, I figured that out. No, my comment was that you were prefixing fields with an identifier to show the table they were in. It's something I don't like stylistically.
amit_logicon wrote: I have kept address of different entities in different table, i.e., Address of Vendors, Branches in respective tables
See the opening to my comment above. Consider normalising the structure a bit. Remember, if you have a vendor and branch address that are the same, you have duplicated the data. Having a separate address table would help with this.
amit_logicon wrote: I was asking for your review because I am having really great difficulties in creating queries.
What difficulties? I don't see how my looking at the structure will pass the ability on to you to ascertain what Inner Joins, Left Joins, Right Joins, etc to do. Only you know what your queries are going to do, so the ball is firmly in your court.
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.
|
|
|
|
|
Hi,
The scenario is like this, -
Company has several branches and Vendors are associated with branches separately. That means each vendor is attached to one branch only. Now company sales products through Vendors. Products which are being sold have different price on different week days. Vendors when take products from branch either pay cash or submit coupons. These coupons have some price and vendors can submit different types of coupons in different quantity. So suppose they take product of value 200, he can either pay full cash or submit coupons of worh 50 and cash 150. This paid amount can be less that total purchase sometime.
There are different queries like take one Branch Summary for any day -
In this we have to summaries sales and Payment for all branch like this -
------| Products |---------|--Received-|Total|--Day--|-Previous--|--Net--|
Branch| A | B | C| NetValue|Cash|Coupon|Value|Balance|Outstanding|Balance|
Here in Product Main Column we have to just sum the quantity of product sold that day,
Net value is sum of Price*Qnty for that branch,
Total Value is Cash + Coupon value (Coupon Value = Qnty * Price),
Day Balance = NetValue - Total Value
Previous Outstanding = Sum of all previous sale - Sum of all previous Cash Return - Sum of all previous Coupon Price * Qnty + sum of all opening balances of vendors of that branch
NetBalance = Day balance + Previous out standing
I have done this currently using many functions in VB but i want to do this using SQL only.
Thanks
|
|
|
|
|
The ProductPriceHistory may need to be normalised so that it doesn't have a different price for each day of the week). It looks like you would need some ugly case statements to find out the price for any specific date.
You might want to set up a Query like:
SELECT A.* FROM ProductPriceHistory A
INNER JOIN (
SELECT C.PCode, Min(C.EffectiveDate) AS EffectiveDate
FROM ProductPriceHistory C
WHERE C.EffectiveDate < MyPriceDate
GROUP BY C.PCode
) B
ON B.PCode = A.Pcode
AND B.EffectiveDate = A.EffectiveDate
ORDER BY A.PCODE ; to allow you to easily find the effective price at any point in time (assuming that you do normalise theis table). This will ask for the appropriate PriceDate to be specified as a parameter.
I would recommend changing the column names of "Date" and "Value" to names that are not reserved words.
I would denormalise slightly and store the current balance on the Vendor table. This information is likely to be used a lot (so performance may be an issue), and means that you would never be able to cleardown your sales history (which means you may have scalability issues). If you are only going to have a few thousand records then this may not be a problem(and you could create another Query that tells you the current balance for each vendor).
As Pete indicated, you might want to remove the address details from the Vendor table - and instead indicate which office is the main address. You might consider linking from Sale to Branch (instead of Vendor). That would allow you to tell which branch the goods should be shipped-to and potentially provide useful management reporting.
Hope that helps.
Andy
|
|
|
|
|
Unfortunately The Price of Products are not same on different week days. SO I decided to Record price for each days separately. Current Balance can be stored in the Vendor table but if wee need to see for example back one months each day report and each days previous balance then in that case it will not be helpful and most of the required reports are like that.
|
|
|
|
|
hi every buddy,
i just made alogin form in a C# 2.0 (SQL Server 2000) Windows Application and I wanted the password to be case sensitive but its not case sensitive u know. the query I'm using to validate user is this
select u.userName, u.password, e.empName, ,u.empid from users u inner join emp e on u.empid = e.empid
Where u.username like @UserName AND password = @Password;
I thought it shold work as case sensitive for passwords but it isnt.
can u plz help
tahnks in advance
Rocky
|
|
|
|
|
Hi Rocky
It is more secure to hash the password using PWDENCRYPT and PWDCOMPARE than to store it unencrypted in the database.
However, if you need to do a case-insensitive comparison then you can use COLLATE to cast strings into a binary collation.
Andy
|
|
|
|
|
yes hashing is indeed a better option than this. if u dont mind can u plz elaborate on using that, preferably by an example or something
thanks alot
Rocky
|
|
|
|
|
One way to do this is to cast the password and @Password to varbinary. This performs a binary check which, by it's very nature, compares the actual data and not the case-sensitive version.
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.
|
|
|
|
|
Hi all.
I have created my website and now I want to upload it in the internet ( on my server )
I have host any everything which is needed.
But the question is :
How Should I move my database ( which is SqlServer2000 ) into my server so that my website will work correctly ...
please help me and tell me how to put my database on my server ?
thank you.
|
|
|
|
|
Assuming that you have access to the remote server from Enterprise Manager, you could always attempt to Export Data. To do this, select the database in the Enterprise Manager, right click and select All Tasks > Export Data. Follow the steps in the wizard, and you can copy the objects and the data up to your website.
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.
|
|
|
|
|
thank you. is it the only way ?
|
|
|
|
|
It's not the only way, but it is the easiest.
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.
|
|
|
|
|
Hi
I am using a view say v_xyz and I am using table tbl1 and tbl2 in the view
Do i have any method to find out the unique key in view so that I can do the
differentiation in the records
thanks 
Karan
|
|
|
|
|
If I wanted to see the differences between 2 tables then I would do the following:
select T1.Id, T2.Id,
case
when T1.Id is null then 'Record only exists in T2'
when T2.Id is null then 'Record only exists in T1'
when IsNull(T1.MyString, '') <> IsNull(T2.MyString, '') then 'MyString is different'
when IsNull(T1.MyNumber, -999) <> IsNull(T2.MyNumber, -999) then MyNumberis different'
--other when clauses to compare other columns
else 'Records are the same'
end
from T1
full outer join T2
on T2.Id = T1.IdHope this helps.
Andy
|
|
|
|
|
Hi all, I'm using SQL Express 2005, it has been installed to use windows authentication for the login and not mixed mode, how and where do I change it so that it uses mixed mode for the login?
Thanks in advance
He who laughs last is a bit on the slow side
|
|
|
|
|
I a project we are having following tables -
Branch
BCode
BName
Vendor
VCode
VName
VendorPaymentHistory
VCode
PaymentDate
CashAMount
VendorCouponHistory
VCode
SCode
Date
Quantity
Price
Sale
SaleCode
VCode
SaleDate
SalesLine
SaleCode
PCode = Product Code
Price
Quantity
Now I am trying to write a query which will return following, -
For any date -
VendorCode(VCode) - Sum(Qty) Product Wise - Total Value of Coupons - Total Cash Paid - Day Balance - Previous Outstanding - Net Balance
I am not able to complete this? Any Help?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
