Recurring Date
Dec 17, 2004
Here's my problem:
I have a date that is entered into the field, 12/17/2004. My client wants the site set up so that when after the date is entered, it automatically updates itself every week. So the date represents an event, and he wants to update the event date automatically instead of having a user login and update the date manually. So 12.17/2004 shoudl turn into 12/24/2004, and then 12/31/2004 and so forth.
One brilliant guy told me that if you entered a start date of and an end date then some how that might me be a little easier.
Any thoughts good people?
View 6 Replies
ADVERTISEMENT
Jan 5, 2007
Guys can you help please...
At the moment I have an unknwon number of ID's and need their related info in another table (1,2,3,4,5 for example) and I'm looping and merging to get them as one table:
For i = 1 to however many...
Dataset = Select where ID = array(i)
FinalDataset.Merge(Dataset)
Next
How can I write a stored proceedure or something where the loop is happening at the database end, to avoid going back and forth?
I was thinking dynamically generating the sql statement:
Select * from table where ID = 1 or ID =2 or ID =3......
But this could become huge if you had a couple of hundred to do...there must be a more efficient way to do it...
View 2 Replies
View Related
Feb 28, 2007
I have a situation where a common table within multiple databases across multiple servers is randomly getting corrupt. The database schema is identical for each database. There are approx. 200 copies of the database on each server. I am seeing one or two errors per week. There doesn't seem to be any similarities between the databases that are effected... It is always the same table that gets corrupted. The mulitple servers are using a SAN but since it is different databases / common table I have a hard time believing it is hardware related.
The error that the application throws is:
Microsoft OLE DB Provider for ODBC Drivers (-2147467259) [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find the index entry for RID '16b700000000000000030002' in index page (1:5366), index ID 0, database 'DISOPT'.
When running a DBCC on the table the following is returned:
Database DISOPT consistency errors in table TF_FILE_METADATA_VALUES fixed no data loss reported.
DBCC results for 'TF_FILE_METADATA_VALUES'.
The error has been repaired.
Clustered index successfully restored for object 'dbo.TF_FILE_METADATA_VALUES' in database 'MUTGEN'.
There are 277 rows in 2 pages for object 'TF_FILE_METADATA_VALUES'.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'TF_FILE_METADATA_VALUES' (object ID 1332199796).
CHECKDB fixed 0 allocation errors and 1 consistency errors in table 'TF_FILE_METADATA_VALUES' (object ID 1332199796).
This corrects the problem and the app functions as normal. The errors are random across databases but ALWAYS the same table.
Any thoughts?
View 7 Replies
View Related
Sep 13, 2006
Is it possible to display when a scheduled job is next due to run ineither a view or a table?If so, how?thanksBryan
View 3 Replies
View Related
Dec 8, 2004
Hi!
My problem/"goal to achieve" is the following..
Users can insert orders in a db-table. The date on which they've been created is stored.
I want the server to insert a new record in another table (accounts) for every order which is older than 2 weeks.
How should I do that?
With a custom action which checks all orders (a customer must press some button every day).
Or a static "update-class" with a kind of timer which does the action every 24 hours?
Of course an automatic solution will be the best! Another question is: how can i ensure that an instance of this "update-class" will never die?
Thanks so far...
View 2 Replies
View Related
Nov 10, 2004
Ugh!
I need to create some SQL that will generate all the dates for a recurring meeting on multiple days per week.
For example:
a meeting on Monday , Wednesday and Friday at 10:00 am - 10:30 am from December 1st 2004 to Feb 28th 2005.
I'm not sure what to do here.
Any Ideas?
Regards
Russ
View 1 Replies
View Related
Jan 4, 2008
Hi,
I have been asked to add a feature to some software I have written which needs to send reminder emails.
The reminders can have a recurrence, for example it might recur every 3 days or every 2 weeks. The reminders also have a start and end date. I have created a sql table with these fields:
Reminder ID
RecurrenceType (e.g. Daily, Weekly, Monthly)
RecurrenceAmount (e.g. 1, 2 etc.)
StartDate
EndDate
ReminderText
AddedBy
DateTimeStamp
I now need to write a stored procedure that will run every morning and send the reminders. I have no problem with sending emails from sql, the trouble I am having is I cannot figure out how to get the recurrences for the current day.
Hope this makes sense!
Sorry there is no code sample, I have been thinking about this one for a couple of days but I still don't know where to begin!
If anyone could offer any help it would be greatly appreciated!
Thanks
Jimbo
View 7 Replies
View Related
Jun 21, 2007
I downloaded VisualBasic 2005 Express Edition recently, and the installation went flawlessly, but when I tried to download the optional component SQL server express, the installation failed. When two subsequent installations failed, I decided I'd get along without it, until such time as I discovered I *needed* it. Well, I'm here, and the installation is still failing. I've tried both installing it though the VBstudio setup, and downloading it separately, but both fail at roughly the same point. The error from the standalone installation is more descriptive:
TITLE: Microsoft SQL Server 2005 Setup
------------------------------
SQL Server Setup Failed to compile the Managed Object Format (MOF) file c:Program FilesMicrosoft SQL Server90Sharedsqlmgmproviderxpsp2up.mof. To proceed, see "Troubleshooting an Installation of SQL Server 2005" or "How to: View SQL Server 2005 Setup Log Files" in SQL Server 2005 Setup Help documentation.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=setup.rll&EvtID=29513&EvtType=sqlca%5csqlmofca.cpp%40Do_sqlMofcomp%40Do_sqlMofcomp%40x80041002
The link leads to an apology - they have no information on this subject. Of the help files, the troubleshooting page contains nothing (noticeably) relevant, and the log files have nothing obvious either, at least as far as I could tell... but then, I have no idea, which is why I'm here
View 1 Replies
View Related
Sep 15, 2006
We have some SSIS packages that are running on a recurring SQL Job. They work and play fine. They ran for about three iterations and then decided to stop....they don't report errors, they just don't do anything. They sit as if they were disabled (but they aren't disabled, I checked the MSDB database).
You can manually execute the packages and they work fine.
I have dumped and reloaded these packages and that hasn't fixed the issue.
If I setup a simple recuring job that executes a sql script every few minutes that works fine and you can see that it executes every few minutes. But these SSIS packages just sit....and do nothing. But they were working for the first few iterations.
I have also redeployed the SSIS packages and that hasn't fixed it either.
Anybody seen anything like it?
View 1 Replies
View Related
Aug 11, 2007
Hi guys, wonder if you could help.
Ok, basically, my SSIS flow gets data from different excel worksheets and puts in the db.
For some reason its being very incosistent. I fixed all the problems I was having but one component keeps coming up with the [ component "Excel Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA" ] error.
So, I click on the component, it asks if i want to fix the metadata stuff, I say yes, it fixes it, I run the app, everything's cool.
The I run the app again and again it comes up with this f** error?
Is there anyway SSIS can't just ignore this metadata stuff??
thanks!
View 5 Replies
View Related
Jun 22, 2007
Hi All,
I have created a SSIS package which calls child packages internally. In other words there is hierarchy of packages. I am using For Loop Container with certain check conditions to execute whole set of packages repeatedly. I have to execute this set for almost 5000 times. But my problem is this set fails after every 50 and sometimes 55 cycles. Can Anybody let me know how to get solution for such a problem?
Regards,
Prash
View 1 Replies
View Related
Jan 14, 2005
Hello,
This is my first project at the company I recently joined. It is the most challenging project I ever had.
It is a kind of web-scheduler.
it let you save appointments which could be one time or periodic.
For example, it could be like every monday, every other monday, first tuesday every monday, and so on.
once they are saved, it should be able to display only those appointments the user have on a specific day.
I'd like to know how database has to be designed to meet such a challenging requirement?
I'd like to hear anything from you if you have had similar project before.
Thank you,
Charlie
View 2 Replies
View Related
Feb 29, 2008
I am trying to load Project Real. I get warnings when opening the RecurringETL package. However I created the two environment variables it's looking for and have confirmed these variables are functional.
I am unsure how to troubleshoot this and am afraid to do anything given these warnings. Where is the configurations collection??????:
Warning 1 Warning loading TestHarness.dtsx: The configuration environment variable was not found. The environment variable was: "REAL_Configuration". This occurs when a package specifies an environment variable for a configuration setting but it cannot be found. Check the configurations collection in the package and verify that the specified environment variable is available and valid. C:Microsoft Project REALETLTestHarness.dtsx 1 1
Warning 2 Warning loading TestHarness.dtsx: The configuration environment variable was not found. The environment variable was: "REAL_Root_Dir". This occurs when a package specifies an environment variable for a configuration setting but it cannot be found. Check the configurations collection in the package and verify that the specified environment variable is available and valid. C:Microsoft Project REALETLTestHarness.dtsx 1 1
Warning 3 Warning loading TestHarness.dtsx: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed. C:Microsoft Project REALETLTestHarness.dtsx 1 1
Thanks
View 8 Replies
View Related
Jan 20, 2014
I have a problem where I want to write a function to remove recurring characters from a string and replace them with a single same character.
For instance I have the string '12333345566689' and the result should be '12345689'. In Oracle I could do this with "regexp_replace('12333345566689', '(.)1+', '1')", but in T-SQL the only solution I could think of is something like this:
DECLARE @code NVARCHAR(255)
SET @code = '12333345566689';
SET @code = REPLACE(REPLACE(REPLACE(@Code, '1', '~1'), '1~', ''), '~1', '1');
and repeat this for 2 - 9. But I'm sure there is a more elegant version for this in SQL Server 2012.
View 9 Replies
View Related
May 25, 2007
Hi,
I am having a recurring issue that involves a stored proc using OPENROWSET to query an excel file. I used the surface area config to enable this on the server, and made sure that is still set. After an undetermined amount of time, the OPENROWSET query starts failing with this message:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
I corrected this previously by restarting the SQL server, but not before I checked permissions, the excel file itself, etc, and that was the last thing to try.
I am using SQL 2005 with SP1 installed - my primary approach to tackling this issue is to install the SP2, but I did not find this bug referenced in the fixes, and was wondering if anyone else had further insight.
Thanks,
Darrell Young
View 3 Replies
View Related
Sep 21, 2006
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
View 3 Replies
View Related
Jun 13, 2002
In SQL Server 2000:
How do I convert a Julian date to a Gregorian date?
How do I convert a Gregorian date to Julian?
Examples please.
Many thanks in advance.
Gary Andrews
View 2 Replies
View Related
Jul 29, 2015
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
View 9 Replies
View Related
Mar 20, 2015
I am trying to find a beginning date from multiple date ranges, for example:
RowNumberidBegin dtEnd Dt
107933192014-09-022015-06-30
207933192013-09-032014-09-01
307933192012-09-042013-09-02
407933192011-09-062012-09-03
For this id: 0793319, my beginning date is 2011-09-06
108203492014-09-022015-06-30
208203492013-09-032014-09-01
308203492012-09-042013-09-02
408203492011-12-122012-07-03--not a continuous date range
For this id: 0793319, my beginning date is 2012-09-04
108203492014-09-022015-06-30
For this id: 0820349, my beginning date is 2014-09-02
To find continuous date, you look at the beginning date in row 1 and end date in row 2, then if no break in dates, row 2 beginning date to row 3 end date, if no break continue until last date There could multiple dates up to 12 which I have to check for "no break" in dates, if break, display beginning date of last continuous date.
View 9 Replies
View Related
May 30, 2015
I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.
I have table containing 5 columns.
caseid
referenceid
startdate
enddate
caseopendate
[code]...
View 4 Replies
View Related
Mar 18, 2014
I have the following
Column Name : [Converted Date]
Data Type : varchar(50)
When I try and do month around the [Converted Date] I get the following error message
“Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.”
My Query is
SELECT
month([Created Date])
FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]
View 7 Replies
View Related
Jul 20, 2005
Hi,I have a problem with updating a datetime column,When I try to change the Column from VB I get "Incorrect syntax near'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]'942' is the unique key column valueHowever if I update any other column the syntax is fineThe same blanket update query makes the changes no matter what isupdatedThe problem only happens when I set a unique key on the date field inquestionKey is a composite of an ID, and 2 date fieldsIf I allow duplicates in the index it all works perfectlyI am trying to trap 'Duplicate value in index' (which is working onother non-date columns in other tables)This is driving me nutsAny help would be appreciated
View 5 Replies
View Related
Apr 30, 2015
So I have to build dynamic T-SQL because of a date parameter that will be provided. The Date Parameter will be provided in SSRS in normal MM/DD/CCYY format. So how do I then convert that date to my Oracle format
NUMERIC(8,0) CCYYMMDD?
I tried this...
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF<='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF>='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
but that put it in the format of...
AND
MEMBER_SPAN.YMDEFF<=''2015-04-01''
AND
MEMBER_SPAN.YMDEFF>=''2015-04-01''
Which is close...I think I just need to lose the "-"
View 5 Replies
View Related
Nov 16, 2015
SELECT * ,[Due]
FROM [Events]
Where Due >= getdate() +90
This returns the error: Conversion failed when converting date and/or time from character string
Why would this be? How to cast or convert this so that it will work?
View 24 Replies
View Related
Sep 28, 2007
Using DTS package in 2000 version, I am dumping TXT file contents into SQL Table,
I have one column having date in format YYYYMMDD(20070929) and corresponding column in SQL is datetime, but it fails on data type mismatch.
I have no choice of making date column in SQL to string or Varchar etc,
is there any way to make that date column in SQL to convert the value upon transformation from format (YYYYMMDD) to M/DD/YYYY (9/29/2007).
many many thanks,
View 2 Replies
View Related
Mar 12, 2008
Hi,
Basically the above is a very common requirement, please comment on my solution which I've arrived at by searching through the web; -
In summary I have used 3 SSIS components these are "Flat File Source", "Derived Column" and "SQL Server Destination".
1) File Connections Manager Editor
1.1) Within File Connections Manager Editor; -
Name the data type e.g. "INTERCHANGE_NET_APP_DATE_SRC"
and assign a type to the data type e.g. string[DT_STR]
1.2) Click on the Preview button to ensure the expected text is assigned to the expected data type.
2) Derived Column Transformation Editor
2.1) Assign Derived Column Name, e.g.
INTERCHANGE_NET_APP_DATE
2.2) Select <add as new column> within Derived Column.
2.3) Enter the conversion Expression, e.g. ; -
2.3.1)
(SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,8,2) + "/" + SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,5,2) + "/" + SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,1,4))
2.3.2)
Since the above conversion is such a common task I suggest that Service Pack 3 of SQL Server 2005 delivers the following functionality; -
STRINGTODATE ('YYYYMMDD',INTERCHANGE_NET_APP_DATE_SRC)
2.4) Select "database timestamp [DT_DBTIMESTAMP] " as Data Type.
2.5) Within the Mappings tab of the SQL Destination Editor have; -
Input Column as INTERCHANGE_NET_APP_DATE and
Destination Column as INTERCHANGE_NET_APP_DATE.
Please comment on the above, I will then pass on my suggestion to Microsoft.
Thanks in advance,
Kieran.
View 1 Replies
View Related
Nov 13, 2007
Hi there.
I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).
I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.
The DB table has the date field stored as a type: CHAR (as opposed to 'DATE')
Can anyone out there help?
Please advise. Thanks.
Best.
K7
View 1 Replies
View Related
Nov 15, 2007
Hi, I really need this help urgently.
I need to send an email when the dueDate(field name in database) is equal to today's date... I have come out with this code with the help of impathan(jimmy i did not use ur code cos i not very sure sry)... below is the code with no error... but it jus wun send email...
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load, Me.Load
con1.Open()
Dim cmd As New SqlCommand
cmd.CommandText = "select * from custTransaction where convert(datetime,dueDate,101) = convert(datetime,GetDate(),101)"
'Set the connect the command object should use
cmd.Connection = con1Dim da As New SqlDataAdapter(cmd)Dim ds As New DataSet
da.Fill(ds)
con1.Close()
If Not ds.Tables(0) Is Nothing ThenIf ds.Tables(0).Rows.Count > 0 Then
Dim objEmail As MailMessage = New MailMessage
objEmail.From = New MailAddress("my@email.com.sg")objEmail.To.Add(New MailAddress("my@email.com.sg"))
objEmail.Subject = "Due Date Reaching"objEmail.Body = Session("dueName")
objEmail.Priority = MailPriority.Normal
Dim SmtpMail As New SmtpClient("servername")
SmtpMail.Send(objEmail)
End If
End If
End Sub
Note: I am veri sure that database has the data field dueDate with the value 11/16/2007 smalltimedate(mm/dd/yyyy)
Realli veri urgent Thanks so much for ur'll help
View 8 Replies
View Related
Jan 11, 2006
hello
how can i select all dates upto todays date and include the first next future date in a given data base
say todays date was the 01/06/2006 (MM,DD,YYYY)
below is a mock data base
id date (MM,DD,YYYY)
1 01/02/2006
2 01/04/2006
3 01/06/2006
4 01/09/2006
5 01/20/2006
i want to select all dates equal or less that 01/06/2006 and include the first next future date .. and in this case it would be 01/09/2006
so the results would return
1 01/02/2006
2 01/04/2006
3 01/06/2006
4 01/09/2006
View 2 Replies
View Related
Aug 24, 2015
PHP Code:
INSERT INTO [GPO].dbo.tblMetric (KPI_ID, METRIC_ID, GOAL, REPORTING_MONTH, ACTUALS)
SELECT
1 AS KPI_OWNER_ID
, 23 AS METRIC_ID
, .75 AS GOAL
, CAST(Z.REPORTING_MONTH as DATE) AS REPORTING_MONTH
, SUM(CAST(FTP_COUNT AS DECIMAL))/SUM(CAST(FULL_COUNT AS DECIMAL)) AS ACTUALS
[Code] ....
The insert column I am trying to get into is a date type. The original state of the field is YYYYMM varchar. How to get this into the table.
View 3 Replies
View Related
Jan 30, 2007
I asked this question below, but the answer was that the conversion will take place automatically, but I can't get that to happen. I have a flat file with an 8 position field that I identify as string (and I also tried date) that is yyyymmdd and it needs to go into the database field that is datetime format. IS there something I am doing wrong with the definition of it, or do I need to add some kind of conversion, and if so, what and how would that be done. I'm a dts Sql2000 expert, but the SSIS thing is driving me crazy. I have a ton of dts' to convert and the migration tool doesn't work because there are a lot of active X scripts in them. thanks for your help. Boston Rose
View 1 Replies
View Related
Sep 5, 2007
Dear Expert!
A server with SQL 2005 sp2, Reporting Services and Sharepoint services (ver 3.0) (in integrated mode) gives an odd error. When viewing a Reporting Services report with a Date Time Picker, the date chosen is wrong. The preferred setting is Danish with the date format dd-mm-yyyy. The date picker shows the months in Danish but when selecting a date, and clicking on the Apply-button, the date reformats to US (mm-dd-yyyy).
Example:
When choosing 5th of September 2007 and clicking apply, it shows in the picker, 9th of May 2007.
When choosing 26th of September 2007 and clicking apply, it shows, again in US format, the RIGHT date but adds a timestamp 12:00 AM? in the end, making further enquiries to fail.
The report itself receives the right date and shows correctly. The only case it fails is, when the time stamp appears.
The server is a 32-bit one with 4 GB RAM. A testserver with identical collation on the Reportserver database cannot recreate the error. The site containing the reports has been set to Danish in the regional settings. To Reinstall is not an option.
The test report has no database connection whatsoever.
When setting the site to US, the timestamp wont appear at all.
The server has been restarted and the installation procedure was of the simple kind. No special tweaks at all.
Any advice would be greatly appreciated.
Kind Regards
Johan Rastenberger
View 1 Replies
View Related
Nov 26, 2015
CREATE TABLE #Dateissue
(ID int,
Code nvarchar (20),
Datein datetime,
Declined datetime )
[Code] .....
I have a table here. I want find a way of getting the latest date, when the code is the same. If the Declined date is null. Then I still want the latest date. E.g. ID 3.
If the declined date is filled in. Then I want to get the row, when the Datein column value is greater then the declined date only.
I tried grouping it by max date, but i got an error message when trying this out. Against the code
WHERE MAX(Datein) > Declined
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference. What do I need to do to get both my outputs working?
View 9 Replies
View Related