I have an integer based on
1 Sunday
2 Monday
4 tuesday
8 weds
16 thurs
32 Fri
64 saturday
if multiple days were selected it would use a logical or such as weds and tues = (4+8=12) I am trying reverse get the days back. What is the best to do that. Thanks
Ludwig
I have a VB6 application from which I want to create and schedule a jobs. The individual jobs should run once and be deleted when succesfully finished. Almost everything works fine. But enabling the jobschedule won't work. In the sp below I set the @enabled parameter of sp_add_jobschedule to 1 (is also default) which should indicate that the schedule is enabled. How to fix this, or what I am doing wrong? I tried to place an execute sp_update_jobschedule at the end of the sp, but no effect at all.
CREATE PROCEDURE mis_CreateCustomJob (
@strUserName varchar(50),
@strDateTimeStamp varchar(14),
@strJobType varchar(10),
@intDate int,
@intTime int
)
AS
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @JobName VARCHAR(67)
DECLARE @ReturnCode INT
SELECT @JobName = 'jb_' + @strUserName + @strDateTimeStamp
SELECT @ReturnCode = 0
BEGIN
-- Add the job
DECLARE @strDescription VARCHAR(100)
SELECT @strDescription = 'Job aangemaakt met MUC door ' + @strUserName
EXECUTE @ReturnCode = msdb.dbo.sp_add_job
@job_id = @JobID OUTPUT ,
@job_name = @JobName,
@owner_login_name = N'Admin',
@description = @strDescription,
@category_name = N'[Uncategorized (Local)]',
@enabled = 1,
@notify_level_email = 0,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level= 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
DECLARE @strCommand varchar(100)
SELECT @strCommand = 'SET ANSI_NULLS ON' + char(13) + 'SET ANSI_WARNINGS ON' + char(13) + 'go ' + char(13) + char(13) + 'exec mis_JobTest'
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID,
@step_id = 1,
@step_name = N'Upload Contracttabellen',
@command = @strCommand,
@database_name = N'mis',
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 1,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID,
@name = N'HandmatigeUpload',
@enabled = 1,
@freq_type = 1,
@active_start_date = @intDate,
@active_start_time = @intTime
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @JobID,
@server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Hi:
in sp_add_jobschedule
@freq_type = 8 for weekly @freq_interval = 1 for Sunday.
I need to script to create a job which will run every Sunday 6 pm, to backup one prod database to a new backup device. EX. database name is DBTest, and the 7/20/2003 (Sunday) running result should be backup_DBTest_2003_07_20.bak.
and next Sunday's should be backup_DBTest_2003_07_27.bak.
(if this way, the backup device will be created at the begining of the job running...)
this way the backup would not be overwritten. Is there a way to dynamically generate 54 weeks job script from this coming Sunday?
Or to make the backup result as backup_DBTest_2003_week27.bak, backup_DBTest_2003_week28.bak....
thanks
David
I have an SQL code below which removes weekends and non working days when calculating days difference between two dates:
ce.enquiry_time represents when the enquiry was logged
(DATEDIFF(dd, ce.enquiry_time, getdate()) + 1)
-(DATEDIFF(wk, ce.enquiry_time, getdate()) * 2)
-(CASE WHEN DATENAME(dw, ce.enquiry_time) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END)
-(SELECT COUNT(*) FROM nonworking_day WHERE nonworking_day.nonworking_date >= ce.enquiry_time AND nonworking_day.nonworking_date < dateadd(dd,datediff(dd,0,getdate()),1))
It works but I don't understand how it works it out. I am having issues understanding each coloured piece of code and how it works together.
Hello,I am writing a query to select records added to a table today, in the last 3 days, in the last 7 days, and so on.Here is what I have (which seems that its not working exactly). -- total listed today
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 0-- total listed yesterday
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 1-- total listed in the last 3 days
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 3I'd like to be able to select the count for records added within the last X number of days. Can someone please help me out? Thanks so much in advance.
How would I bring back a column that shows all the days from the begining of the year to the current date. Also make sure this moves onto the next year.
View 9 Replies View RelatedDid a bit of searching, but couldn't find it. I'm looking to get data from the past 7 days:
select * from inquiry
where created_date is within the last 7 days
Thanks,
Hey heyI've written my site in asp.net 2 and have set up roles and various logins etc which work beautifully locally. Now, when i upload to my host I get the: An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be
caused by the fact that under the default settings SQL Server does not
allow remote connections. (provider: SQL Network Interfaces, error: 26
- Error Locating Server/Instance Specified)The thing is, I've checked my connection string a million times and it's perfect, I've also checked all the database settings on my host and they're all fine. However, when i use SQL Server Management Studio Express to copy my database from the local to the remote server (from a tutorial on these forums), I get the "Operation completed successfully" message yet when I expand my tables, there is no data in them. I've tried copying and pasting the data in manually but i still get the same issue. It's driving me absolutely insane! Anyone any ideas?Thanks
Hi all,
I have a table in which I have two fields in my DB.
FromDate and ToDate.
Both are stored as Varchar(MAX).
I would like to have an SP which gives me the Days in Between them.
Regards,
Naveen.
Is there any way to find out in sql server that whether it is Monday to friday or saturday sunday.I have to write a stored procedure in which the logic shoukld be done only if there is a weekend.if saturday or sunday select * from status_tempPlease let me know if there is any function that is available in sql
View 4 Replies View RelatedIs there anyway to get SQL to convert a date to days of the week?
Example, I have a query where the date is "01/01/2006" but I would like it to display "Sunday". Thank you.
How can I select records from a schedule table that have end dates older than 90 days?
Can I do getdate() -90?
Code:
select *
from dbo.schedule
where enddate <> endate-(90 days?)
Hi,
I just want to know, is there anything more better solution than this one to find out the no of days in a month ?
I have done this but I am not satisfied,anybody has a smarter solution?
Plz comment..
My Solution :
/* check leap year*/
if year(getdate())%4<>0
set @noofdays=(select case month(getdate())-1
when 1 then 31
when 2 then 28
when 3 then 31
when 4 then 30
when 5 then 31
when 6 then 30
when 7 then 31
when 8 then 31
when 9 then 30
when 10 then 31
when 11 then 30
when 12 then 31
end )
else
set @noofdays=(select case month(getdate())-1
when 1 then 31
when 2 then 29
when 3 then 31
when 4 then 30
when 5 then 31
when 6 then 30
when 7 then 31
when 8 then 31
when 9 then 30
when 10 then 31
when 11 then 30
when 12 then 31
end)
Joydeep
I have a little query that returns me all the days in a month, but the days are not in the order I need. They come out like so..
January 10
January 11
January 12
January 13
January 14
etc
January 19
January 2
January 20
January 21
January 22
here is my sample code
select [month] + ' ' + [day] as [Date] from mytable
where [month] = 'january'
and [year] = '2004'
Thanks, Jeff
Hi, I used the following query to update my columns according to the change of time and days of the week. But my query is not working as it should. Today is Tuesday and the time now is 9am so according to my query Mon_Night column should be updated but Sat_Night column is being updated when i execute the query. Please advice..
declare @Weekday bit, @hour int
select @Weekday = datepart(dw,getdate()),@hour= datepart(hh,getdate())
Update Weekly set
Sat_Night=CASE WHEN (@Weekday= 1 and (@hour> 7 AND @hour<=19))
THEN ALD.EngTime ELSE NULL END,
Sun_Day=CASE WHEN (@Weekday= 1 and (@hour > 18 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END,
Sun_Night=CASE WHEN (@Weekday= 2 and (@hour> 7 AND @hour<=19))
THEN ALD.EngTime ELSE NULL END,
Mon_Day=CASE WHEN (@Weekday= 2 and (@hour > 18 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END,
Mon_Night=CASE WHEN (@Weekday= 3 and (@hour> 7 AND @hour<=19))
THEN ALD.EngTime ELSE NULL END,
Tue_Day=CASE WHEN (@Weekday= 3 and (@hour > 18 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END,
Tue_Night=CASE WHEN (@Weekday= 4 and (@hour> 7 AND @hour<=19))
THEN ALD.EngTime ELSE NULL END,
Wed_Day=CASE WHEN (@Weekday= 4 and (@hour > 18 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END,
Wed_Night=CASE WHEN (@Weekday= 5 and (@hour> 7 AND @hour<=19))
THEN ALD.EngTime ELSE NULL END,
Thu_Day=CASE WHEN (@Weekday= 5 and (@hour > 18 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END,
Thu_Night=CASE WHEN (@Weekday= 6 and (@hour> 7 AND @hour<=19))
THEN ALD.EngTime ELSE NULL END,
Fri_Day=CASE WHEN (@Weekday= 6 and (@hour > 18 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END,
Fri_Night=CASE WHEN (@Weekday= 7 and (@hour> 7 AND @hour<=19))
THEN ALD.EngTime ELSE NULL END,
Sat_Day=CASE WHEN (@Weekday= 7 and (@hour > 18 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END
from ALD join Weekly on Weekly.TesterID = ALD.TesterID
How would I do a date range from the first day of this year to the getdate(), and so it will change once the new year hits as well?
View 6 Replies View RelatedI've have these following table
tbllocation
Main_ID | Date_Taken | Time |Hit
-----------------------------------------
206 | 5/9/2008 | 100 | 2
206 | 5/9/2008 | 200 | 3
206 | 5/6/2008 | 300 | 6
201 | 5/1/2008 | 400 | 5
201 | 5/4/2008 | 500 | 9
201 | 5/7/2008 | 600 | 2
204 | 5/2/2008 | 700 | 2
204 | 5/3/2008 | 800 | 4
204 | 5/6/2008 | 900 | 2
203 | 5/7/2008 | 100 | 2
203 | 5/8/2008 | 200 | 3
203 | 5/9/2008 | 300 | 6
202 | 5/4/2008 | 400 | 5
202 | 5/3/2008 | 500 | 9
202 | 5/8/2008 | 200 | 3
205 | 5/2/2008 | 300 | 6
205 | 5/1/2008 | 400 | 5
205 | 5/9/2008 | 500 | 9
tblSetValue
Main_ID | Hit2
---------------
206| 3
201| 5
204| 3
203| 1
202| 8
205| 7
*Main_ID is a primary key
Condition
1. Let's say, the current date is 5/9/2008
2. Result only display the last 7 days data. From above data. it's mean only pickup from 5/3/2008 to 5/9/2008
3. Every Main_ID only pickup the MAX Hit
4. Diff (column on the fly) = Hit - Hit2
The expected result shown as follow
tblResult
Main_ID | Date_Taken | Time | Hit | Hit2 | Diff
-----------------------------------------------
206| 5/6/2008 | 300 | 6 | 3 | 3
201| 5/4/2008 | 500 | 9 | 5 | 4
204| 5/3/2008 | 800 | 4 | 3 | 1
203| 5/9/2008 | 300 | 6 | 1 | 5
....
....
....
Anyone can help me to built the query?
dear gurus,
I want to get the working days between two days..
in a single query.
i will give the start_date '06-02-2008',end_date '06-13-2008' the result should be as below.
06-02-2008Monday
06-03-2008Tuesday
06-04-2008Wednesday
06-05-2008Thursday
06-06-2008Friday
06-09-2008Monday
06-10-2008Tuesday
06-11-2008Wednesday
06-12-2008Thursday
06-13-2008Friday
Thanks in advance.
cool...,
Dear gurus..,
I need to get all the days in a year in a single query.
Thanks in advance.,
cool...,
Hello,
I have a table with a field Expiration of smalldatetime. I want to only display the data that has 60 days or more between Expiration and todays date. Any help would be highly appreciated.
I am looking to do the following:
CurrentDate - admit date /24 (round down to whole number)
I Have a date range and i want to calculate the number of days - the weekends(saturdays and sundays) so that only businessdays are obtained.
A simple table's example:
Name, surname, datebeg, dateend
How can can it be done with sql?
BB
So Im trying to find the number of days in a certain month I know how I want to do but cant figure out how to code it
i want to take the first date of the getadate() and then ad a month then subtract 1 day and ill have the number of days in a month how would i code that?
Hi Everyone,I use the following to get records from the last two days in MySql:where date_entered <= curdate() and date_entered >=DATE_SUB(curdate(),INTERVAL 2 day)I'm looking to do the same in MS-Sql server but I'm just not getting it.I've got this so far which does not work:where hit_date <= GETDATE() and hit_date >= DATE_SUB(GETDATE(),INTERVAL 2day)then I tried this:WHERE hit_date >= DATEDIFF(GETDATE(), (GETDATE()-2)>Essentially, I need all records from the last two days.Any help or guidance in this matter would be greatly appreciated.-JohnyB
View 5 Replies View RelatedHi,
I want to get the no of saturdays that are coming between given span of days,
ex:- if i give dates as 04/05/08 and 04/25/08 , i want to get the no of saturdays between these dates
can anyone help
I am pretty much brand new to reporting services so I apologize for any simple questions I may ask.
I have created a call report that shows data broken down by day and grouped by week. Everything works great. However, if there is no data on a certain day is does not show me that day on the report. Is there a way I can make it display the day even if there is not data for that day and just give me 0 values? Or do I need to modify my query?
Thanks in advance.
Hi,
Does anyone has the DAYS360 excel formula in a function in sqlserver ?? I did this one
FUNCTION dbo.fnDays360_EXCEL
(
@startDate DateTime,
@endDate DateTime
)
RETURNS int
AS
BEGIN
RETURN (
(CASE
WHEN Day(@endDate)=31 THEN 30
ELSE Day(@endDate)
END) -
(CASE
WHEN Day(@startDate)=31 THEN 30
ELSE Day(@startDate)
END)
+ ((DatePart(m, @endDate) + (DatePart(yyyy, @endDate) * 12))
-(DatePart(m, @startDate) + (DatePart(yyyy, @startDate) * 12))) * 30)
END
But there is a bug, if the end date is bigger then february, february must have 30 days and not 28 or 29...
Does anyone has the solution ?
Thanks
Hello,I am writing a SQL query to count records in a table from the last "X" number of days.There is a dateTime column that I want to base this off of:Lets say.. I want to count the records that were added in the last 3 days. What is the query I should use to get this value?Here is what I have and it seems to not be working correctly:SELECT @TotalListedLast3Days = COUNT (*) FROM myTable WHERE DATEDIFF(Day, DateAdded, getdate() ) <= 3Can someone help so I am able to get the count of records added in the last "X" days?Thank you very much.
View 2 Replies View Relatedi have id, start_date, end_date
View 2 Replies View RelatedBottom is my table structure. please some one can give me the sql code to add 3 days to the existing date.
SemesterID intSemesterLongID varcharSemesterIdentifier charSemesterIdentifierName varcharSemesterName varcharSemesterStart datetimeSemesterEnd datetimeSemesterNameAlt varcharEnrollmentDeadline datetimeRegistrationStart datetimeRegistrationEnd datetimeLengthInWeeks varcharBulletinStartDate datetimeBulletinEndDate datetimeClassroomStartDate datetimeClassroomEndDate datetimeFacClassStartDate datetimeFacClassEndDate datetimePlanningStart datetimePlanningEnd datetimeGoToTeach datetimeTOButAppears datetimeTOButDisappears datetimeSignature datetimeTOButDue datetimeBookListDue datetimeProfAccess datetimeExamDue datetimeSCGDue datetimeGradesDue datetimeExtGradesDue datetimeTOExt datetimeSessionPlanning datetimeTODue datetimeSCGStart datetimeTOExpire datetimeSRPTOCDue datetimeSRPCopyDue datetimeSRPCCCFormDue datetimeTOID intSemesterIdentifierID intCode varcharTitleIVDescription varcharCreatedBy varcharModifiedBy varcharDateCreated datetimeDateModified datetime
Hi,
I have a textbox with date selected from calendar... Now i put in another textbox to enters number of day(30 or 120 or other), this number may vary...
so my qns is how to add the n numbers of days entered to the date selected? i stored date as smalldatetime
Help is appreciated
i have a customer and his output date must be 6 weeks or 42 days after his input date in my sql queryhow do i do this i've don't it in access and tried a similar code to no avail '[inputDate]' + '42' alias ext1 can anyone help me out thanks chris
View 4 Replies View RelatedIf I have and invoice date column and I want to now what invoices are 15 days past due or 30 days past due, how do I do this in a where clause?
WHERE tblInvoices.InvoiceDate ???