|
Use a table variable not an array.
Declare @Tbl Table(Date datetime)
Insert @tTbl
Select getdate()
So instead of buying a book and studying you are going to bring every problem to the interweb forums for answer. I predict your questions shortly be ignored and no answers will be forth coming!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
alter procedure sp_pubhol as begin select Holiday_Name from Holiday where Holiday_On = CONVERT(date,GETDATE()) end when i execute sp_pubhol , i am facing the following issue... Invalid object name 'Holiday' pls suggest me what should i do
|
|
|
|
|
The error is that it can't find a table called "Holiday". You can specify the database name followed by the table.
I think the syntax is YourDB.Holiday or YourDB..Holiday.
Give it a shot.
|
|
|
|
|
You really need to get a book on databases and SQL, learn the basics before attempting to build a solution. Forum messages and cut and paste is not the way to learn!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i created a table Holiday....even then it is showing error...my doubt is Holiday_On =
CONVERT(date,GETDATE()) can i do like that....
|
|
|
|
|
|
can anyone help me in writing stored procedure to Activate reminder for event to send email notification 3 days before event along with event description pls .i am using sqlserver2008R2.the list of events are in DB.
|
|
|
|
|
Use the datetime data type to AddDays to to the getdate() function to determine the date to check against your stored event date.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
What have you tried so far? Where are you stuck and need help?
Here is what is expected of enquirers:
1. TRY first what you want to do! You may find that it's not that hard.
2. Formulate what was done by you that looks like an issue/not working.
Try them and tell if you face issues.
For now, have a look at this to start of:
Send Email from Database[^]
Automatically send email every night 12.00 am[^]
|
|
|
|
|
Hi,in this you can create an job using SSMS.No need of writing stored procedure for this.
|
|
|
|
|
Hi,
I need a help please
I am using MySQL. I have a patients database. In patients database I have a table called visit with created_date field as timestamp datatype.
I want to do a query which will give the break down of total number of patients in every hour like this:
hour -------- Patients
00 -------- 7
01 -------- 8
02 -------- 11
03 -------- 4
...
...
...
20 -------- 5
21 -------- 10
22 -------- 3
23 -------- 0
Technology News @ www.JassimRahma.com
|
|
|
|
|
Create a query that extracts the hours (use datepart(h,datefield) component of the created date as an integer. A simple group by will then give you the result you need.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The amount of 0 items will make a problem with Mycroft Holmes' answer - that line won't show up.
You need the numbers from 0-23 (somehow generated, or make a table "Hours" with those values, and then JOIN the query suggested by Mycroft Holmes to it, and replace NULL values with 0.
|
|
|
|
|
Not sure if this will directly work in MySQL, but here is what I would do in SQL Server
SELECT
DATEPART(hh, Created_Date) [Hours]
, COUNT(*) [Patients]
FROM Visit
GROUP BY
DATEPART(hh, Created_Date)
EDIT
There is a format function in MySQL that will return the hour
https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format[^]
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
Hi,
I have visits table for my patients database. I have created_date field which is timestamp with CURRENT_TIMESTAMP and closed_date with datetime datatypes. I have no problem with the created_date but when I close the file using this command:
UPDATE visits SET total_amount = param_total_amount, balance_amount = param_balance_amount, visit_is_open = FALSE, closed_date = NOW(), closed_user = param_created_user WHERE visit_id = param_visit_id;
The closed_date will have the same value of the created_date. All my closed_date now equal created_date
Technology News @ www.JassimRahma.com
|
|
|
|
|
try GETDATE()
Cheees,
Edo
|
|
|
|
|
sorry Edo, forgot to mention it's MySQL spo there is no GETDATE()
Technology News @ www.JassimRahma.com
|
|
|
|
|
Then I agree it's a strange phenomenon 
All other fields get updates correctly or the whole row does not get updated?
Cheees,
Edo
|
|
|
|
|
Not sure how created_date is equal to closed_date.
Make sure, that when you create a patient, at that time populate the created_date field (not closed_date - keep it blank)
At the time of closing the patient, just update the closed_date for that patient, something like:
UPDATE visits SET closed_date = GETDATE() WHERE visit_id = param_visit_id;
Both are separate queries and both target separate fields.
Refer: http://msdn.microsoft.com/en-us/library/ms188383.aspx[^]
|
|
|
|
|
closed_date is Blank... here is the table structure:
CREATE TABLE `visits` (
`visit_id` int(11) NOT NULL AUTO_INCREMENT,
`file_no` int(11) DEFAULT NULL,
`visit_is_open` bit(1) DEFAULT b'1',
`clinic_id` int(11) DEFAULT NULL,
`doctor_id` int(11) DEFAULT NULL,
`account_category` int(11) DEFAULT NULL,
`account_number` bigint(11) DEFAULT NULL,
`price_list_id` int(11) DEFAULT NULL,
`vital_signs_taken` bit(1) DEFAULT b'0',
`total_amount` double(11,3) DEFAULT '0.000',
`balance_amount` double(11,3) DEFAULT '0.000',
`created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created_user` int(11) DEFAULT NULL,
`closed_date` datetime DEFAULT NULL,
`closed_user` int(11) DEFAULT NULL,
PRIMARY KEY (`visit_id`),
KEY `idx_visits_visit_id` (`visit_id`),
KEY `idx_visits_file_no` (`file_no`),
KEY `idx_visits_visit_is_open` (`visit_is_open`),
KEY `idx_visits_clinic_id` (`clinic_id`),
KEY `idx_visits_doctor_id` (`doctor_id`),
KEY `idx_visits_account_category` (`account_category`),
KEY `idx_visits_account_number` (`account_number`),
KEY `idx_visits_price_list_id` (`price_list_id`),
KEY `idx_visits_vital_signs_taken` (`vital_signs_taken`),
KEY `idx_visits_total_amount` (`total_amount`),
KEY `idx_visits_balance_amount` (`balance_amount`),
KEY `idx_visits_created_date` (`created_date`),
KEY `idx_visits_created_user` (`created_user`),
KEY `idx_visits_closed_date` (`closed_date`),
KEY `idx_visits_closed_user` (`closed_user`)
) ENGINE=MyISAM AUTO_INCREMENT=631 DEFAULT CHARSET=utf8;
Technology News @ www.JassimRahma.com
|
|
|
|
|
Jassim Rahma wrote: `created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
This is the issue. You have set the field 'create_date' to be updated to current timestamp on any update - Wrong and not needed in your case.
Just keep it:
`created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON INSERT CURRENT_TIMESTAMP,
`created_date` timestamp DEFAULT NULL,
Pick that you need and apply.
As per other comment of MySQL, you can use NOW()
|
|
|
|
|
Hi,
Use DEFAULT Constraint....
ALTER TABLE visits MODIFY closed_date DATETIME NOT NULL DEFAULT NOW();
Check the following link
MySQL ALTER Command[^]
modified 4-Mar-13 4:25am.
|
|
|
|
|
I've got a scripted install for the database on a local tablet. The OS is windows 7, and it's sql 2008. I end up with this error:
Error: 18456, Severity: 14, State: 11.
Login failed for user 'FOO\BAR'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine="">]
I've only got one user account on the machine, and it is an administrator. The best solution I can find so far is "run ssms as administrator", which is not really a valid option. The DB is used by an application only, and we don't even have ssms. I just need the app to be able to use the database. I don't understand why it can't. This same installer has worked on the other machines before this one.
-edit-
I have followed up more on this being a UAC issue. I actually was going to shut off the notifications with the EnableLUA setting in the registry... but that has already been done. I think that rules out the account not having permissions issue.
-end edit-
If it moves, compile it
modified 2-Mar-13 7:02am.
|
|
|
|
|
this turned out to be an issue with the computer name and the user name being the same. Aparently as of Vista, this is an issue. I had ruled this out when I renamed the computer, uninstalled sql through add/remove programs, and ran the installer again.
Grabbing at straws I just happened to notice that the folders and files don't get removed when you add/remove sql through the control panel. I removed it again, and manually deleted the Microsoft SQL SErver folder, rebooted again, and installed. This worked.
If it moves, compile it
|
|
|
|
|
1. what is active database and why it is used?
2. What are the advantages and disadvantages of active database?
3. name some application using active database?
4. Compare with other databases?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
