|
Datetime data type always contains both date and time portion.
Depending how you want to use it, you can make a decision to use for example only month and year and always set day to 1. For example you can use CONVERT function to convert literal month with year to datetime. One example is:
SELECT CONVERT( datetime, '01-DEC-08', 6)
For more info on usage see: CAST and CONVERT[^]
|
|
|
|
|
Thanks a lot
Nice answer but suppose i want it in the form
if i want to insert 'DEC-08-2009' into DateTime then what should i do ?
|
|
|
|
|
Did you have a look at CONVERT link? The last parameter defines the date format that's used in the string literal. There are only few formats SQL Server recognizes as listed in the covert description.
If you want to use custom format, you could use substring to break the string to different parts and then reformat the parts to a format recognized by convert.
|
|
|
|
|
Hi all,
I am being struglling with a query from past 2-3 days and i m unable to find a solution to it.
I am using sql 2000 and i am having the foll probs.
I have a table "FG_STK" which stores the stock of all items branch wise. The schema is
FG_ID,BranchID,Item_Code,Item_Batch_No,Item_Lot_No,Recon_Code,CLD_Qty,Sku_Qty
and other table called Recon_Codes which has foll schema..
Recon_Code,Recon_Type,Recon_Description
which has predefined values such as
100 CR Opening Stock
200 DR Stock Removed
8000 DR Damage Material Transfer
9000 DR Expired Material Transfer
and in FG_STK the data stored as
1, 2001,350154,Mar809,Mar1208,100,100,0
(item 350154 at branch 2001 with batch Mar809 and Lot Mar1208 with Opening stock of 100 Clds)
2,2002,350154,Feb0809,Feb1208,8000,0,200
(item 350154 with loose qty 200 at branch 2002 with batch Feb0809 and Lot Feb1208 is removed for the purpose of transfer because its damaged)
The rows in FG_STk with recon code not starting with 8 and 9 are considered as good stock, while rows with recon code starting with 8 are considered as damaged and with 9 are considered as expired.
Now to fetch good clds i did the following
Select item.Brand,item.Category,item.Item_Desc as Item,stock_data.batchno,stock_data.lotno,isnull((SUM(CR) - SUM(DR)),0) as Total_Clds,isnull((SUM(CRSKU)-SUM(DRSKU)),0) AS Tot_Sku
From(
SELECT Case Recon_Type
When 'CR' Then SUM(CLD_Qty)
When 'DR' Then 0 END CR,
Case Recon_Type
When 'CR' Then 0
When 'DR' Then SUM(CLD_Qty) END DR,
Case Recon_Type
When 'CR' Then SUM(Sku_Qty)
When 'DR' Then 0 END CRSKU,
Case Recon_Type
When 'CR' Then 0
When 'DR' Then SUM(Sku_Qty) END DRSKU,Item_Code,Item_Batch_No as batchno,Item_Lot_No as lotno
FROM FG_STK INNER JOIN Recon_Codes ON FG_STK.Recon_Code = Recon_Type.Recon_Code
where BranchID=@branch_ID
GROUP BY Recon_Type,Item_Code ,Item_Batch_No,Item_Lot_No
)stock_data
inner join
view_Full_Item_Details item
on
stock_data.Item_Code=item.code
group by stock_data.Item_Code,Item_Desc,stock_data.batchno,stock_data.lotno,item.Brand,item.Category
having ((SUM(CR) - SUM(DR))<>0 or (SUM(CRSKU)-SUM(DRSKU))<>0)
Now the problem is i want damaged and expired along with good stock in diff columns which has almost the same query with an additional where clause as
and Recon_Codes.Recon_Code like '8%' for damaged
and
and Recon_Codes.Recon_Code like '9%' for expired
and i want to show data as
Brand, Category, Item,Batch_no,Lot_no,Good_Cld,Good_Loose,Damaged_Cld,Damaged_Loose,Expired_CLD,Expired_Loose
also if the batch and lot of good cld, expired cld and damaged cld are same they should appear in same row else in diff row.
Any help would be kindly appreciated.
When you fail to plan, you are planning to fail.
|
|
|
|
|
It's quite hard to say the exact solution with such small amount of data, but could you simply add the conditions to the inline view and use case to differentiate good, expired and damaged amounts. If I understood the idea correctly you would use double condition in case. Something like:
WHEN RECON_TYPE = 'CR' THEN ...
...
WHEN RECON_TYPE = 'DR' AND RECON_CODE NOT IN (8000, 9000) THEN ...
...
WHEN RECON_TYPE = 'DR' AND RECON_CODE = 8000 THEN ...
...
WHEN RECON_TYPE = 'DR' AND RECON_CODE = 9000 THEN ...
...
Also if you have the possibility to re-check the E/R-model, I think there would be easier ways to handle the data than how it's currently done.
|
|
|
|
|
Hello Friends,
I've a table in which there are two columns Name and Salary as described below.
Name Salary
A 100
B 200
C 300
D null
I want to increment the salary by 100 of all employees. For it i'm using the command given below
Update Rough Set Salary=Salary+100 Where Salary in (Select Salary From Rough)
But this query is just updating the Salary of A,B,C not of D because of null So can anybody let me know how to update the value of null as well
|
|
|
|
|
You could use COALESCE function. Something like:
Update Rough
Set Salary=COALESCE(Salary, 0) + 100
|
|
|
|
|
Well, firstly that where clause is bonkers. If you want to update all records you want to do away with that.
try this:
update Rough set Salary = isnull(Salary, 0) + 100
It's a good idea to avoid nullable columns where they're not appropriate or you end up having to do stuff like this all over the place.
Regards,
Rob Philpott.
|
|
|
|
|
Hi all,
Is Sql reporting Services will be faster compare to Crystal reports ?
can any one suggest me which tool is best for loading nearly lakhs of datas ??
which tool is best ?
can any one suggest me
|
|
|
|
|
|
Thank u for ur reply..Iam not asking u the differences between them
i know to google and find the difference..
Which tool is more faster i asked..Coz this is forum to get ideas from well versed people and fix soon
|
|
|
|
|
cbenan wrote: Which tool is more faster i asked..
I believe that this is a matter of design, usage scenario, datasources etc. Since both of those tools behave very differently it's quite impossible to predefine which one is faster. If you have an open situation meaning that you can build the reporting as you like, I would say that Reporting Services has more possibilities to do things since it's more like just a layout-engine. Crystal offers more built-in capabilities but on the other hand it's perhaps restricting you more than SSRS.
Also you should note that Crystal is an older product so it's more mature (in a way) and SSRS is still developing to it's full size. I guess this means that the situation is currently something, but after few years it may be a whole lot different. This affects if you're making a long term decision.
Just added performance keyword to the query and seems that there a some comparison on this aspect also: http://www.google.com/search?hl=en&q=compare+performance+crystal+sql+server+reporting+services[^]
|
|
|
|
|
Hi
I want to know how to write storedprocedure when search creiteria is Present.
Here the query to give the list of
"select ID, Salesmennummer , Salesname Commision, Areacode from sales_master
Areacode Salesmennummer Salesname Commision
1 1000 Rams 200
2 2000 Johny 1000
I Want to search above list by areacode or Salesmennummer or Salesname.
so PlZ help me how to write storedprocedure for this.
Thanq.
|
|
|
|
|
|
Hi,
I have install SQL server 2008 on my computer ,i have already SQL server 2005,now problem is that i am not able to connect it through enterprice manager.
N!dh! 
|
|
|
|
|
Most likely the SQL Server 2008 is named instance so you should provide the server name as: machinename\instancename. You can check the instance name for example from Windows Services.
|
|
|
|
|
My server name is Nidhi\sqlexpress
N!dh! 
|
|
|
|
|
so if you specify that to server name, what happens? Do you get an error and if you do, what kind of error.
|
|
|
|
|
Error is:
This version of MSSMSE can only be used to connect to sql server 2000 & 2005
N!dh!
|
|
|
|
|
|
I just started a new project and I need to work on the thing at home.
I have permission to take what I need but I'm not sure what I'm looking for?
I asked (and received) the Schema for the tables but whenever I try and run the SQL queries in the SQLServer query window I get all sorts of errors about missing LDF/MDF files:
Msg 5133, Level 16, State 1, Line 2
Directory lookup for the file "D:\_SQL_LOG\AXXX_CLQ_Log.LDF" failed with the operating system error 2(The system cannot find the file specified.).
Msg 1802, Level 16, State 1, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Msg 15010, Level 16, State 1, Procedure sp_dboption, Line 64
The database 'AXXX_CLQ' does not exist. Supply a valid database name. To see available databases, use sys.databases.
I'm wondering if it wouldn't be easier to just copy the actual files and move them into position? Unfortunately it looks as though the LOG files and data files are stored on different partitions and at home I only have one.
How can I be sure to get a full copy of everything so it's easy to setup at home and get working on this in the evening?
Cheers,
Alex
|
|
|
|
|
Hi,
there are a couple of possibilities:
1. change your fixed paths to something that exists on every relevant system, so if one doesn't have a D: then
don't use D: anywhere
2. make the paths configurable somehow, so you can run the app with different paths on different machines;
3. add a D: partition to the machine that lacks one (Vista does not need a tool to do this, i.e. Computer Management
can solve this;XP and earlier get changed most easily using Partition Magic or something similar)
4. on the machine(s) lacking D: use "Map network drive" to map D: to some existing folder on C: (see Explorer,
Tools menu).
In the short term I recommend 4; in the long term I would go for 2.
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
|
|
|
|
|
Luc Pattyn wrote: change your fixed paths to something that exists on every relevant system, so if one doesn't have a D: then
don't use D: anywhere
Thing is all I have is the schema SQL file not the files which the schema seems to refer. Can I create empty files and assume that would work?
|
|
|
|
|
hat do you actually mean by schema? Do you refer to the MDF-file? If yes, then how have you taken it. Is it detached, just copied etc.
|
|
|
|
|
Hi all. Is there a way to automatically grant execute permissions on CLR stored procedures upon deployment from a Visual Studio SqlServer project?
I have a solution with a project for all my Sql Server stored procs, and a second project for a web site that tests them. When I rebuild/run the web site, the stored procedure assembly is rebuilt and re-deployed also (which is good) but because the stored procedures are recreated, the GRANT permissions previously on them are lost (which is not so good). I'd like to have the GRANT EXECUTE permissions added as an automatic part of the deployment process, if that is possible.
Help?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
