I am starting to play with a datetime field in sql server 2005
i wanted to write the date and time to my database in seperate fields but if if format my input to dd-MMM-yy i end up with 02-february- 2008 00:00
and if i try for the time i get 01/01/1900 12.00
i can put them both in the same field, thats not a problem for be but how do i then do an sql query to get backj say all records where the date is between the 01-jan-2008 and 02-feb-2008
Maybe if some one can point me at a tutorial it may assist me
I need to find Monday's DATE (and Tuesday's, and Wednesday's, etc.) of Week number 44 of the year 2005 for my stored procedure. Does anyone know how I can do that? Jon
I am having trouble finding a way to work with dates. Below is a very simplified sproc I am trying to get to work. It will later be expanded, but first things first:
CREATE PROCEDURE spCellarOp4 @ReportDate datetime = getdate AS SELECT FERMID, fldfermtank, fldfilltime, fldfermprod, "Sample Test" = CASE WHEN CONVERT(int, DATEDIFF(dd, fldfilltime, @ReportDate)) = 1 THEN ' it is 1 ' END FROM vwUnfilteredFermenters
The error message is: Server: Msg 241, Level 16, State 1, Procedure spCellarOp4, Line 0 Syntax error converting datetime from character string.
I've got a table with some datetime fields in it.One field (call it field 1) is of the form mm/dd/yyyy and the other two(fields 2 and 3) are in the form of hh:mm:ss:xx where xx is hundrethsof a second.I'm getting the difference between field 2 and 3 using (datediff(ms,access_time, release_time )/1000/60.). This seems to work fine.However, in some other cases I'd like to add field 1 to field 2 andthen manipulate the result. This is where it gets weird.If I do it like this: Convert(varchar 20),record_date+access_time,100),it adds field 2 ok but subtracts two days. So for example 3/1/05 +10:30:00 AM = 2/27/05 10:30:00 AM. So it effectively subtracts twoadditional days for no apparent reason. If is userecord_date+2+access, then this returns the correct answer.If I try to use Convert(varchar(20),dateadd(ss,record_date,access_time),100) sql server complains Argument data type datetime isinvalid for argument 2 of the dateadd function.Basically I'd just like to know how to add and subtract fields 1(mm/dd/yyyy format) and 2 (hh:mm:ss:xx format).As a bonus question, is it possible to get an average time for severaldifferent times? For example the average time between 10:30 and 11:00would be 10:45.regards,-David
I'm trying to add records to a table that includes datetime value using INSERT query. No matter what date value I'm giving the query, the value saved is 1/1/1900 00:00 . Also, I'm interested in saving only the short date, without time. I thought changing the type from datetime to smalldatetime would do the job, but it didn't help.
I'm trying to write a function to return all notes with date. Sample data for 1 record=187189 as follows: iincidentid,iWorkNoteId,iSeqnum, dtEntryDate, workNoteAll 1871893440 1 2006-04-24 note1 1871893545 1 2006-06-22 note2 1871893547 1 2006-06-22 note3 1871893653 1 2006-08-10 note4 1871893653 2 2006-08-10 note5
funtion will return = 2006_08-10 note4 note5 for iincidentid=187189 ----------------------------------------------------- CREATE FUNCTION dbo.getIncidentNotesRev(@iIncidentID int) RETURNS varchar(8000) AS BEGIN declare @incidentId int declare @worknoteid int declare @worknotesaveid int declare @seqnum int declare @dtEntryDate smalldatetime declare @worknoteall varchar(8000) declare@allnotes varchar(8000) declare @currentWEDate smalldatetime declare @beginWEDate smalldatetime
select @allnotes='' select @currentWEDate=currentweekEndDate from csCurrentweekEndDate --get the current week end date select @beginWEDate = DATEADD(d, - 28, @currentWEDate)--get the last 4 weeks
declare CursorIncident CURSOR LOCAL FOR SELECT iIncidentId, iWorkNoteID, iSeqNum, dtEntryDate,worknoteall FROM dbo.rpt_weekly_prospect_status_vw where iIncidentId=@iIncidentID order by iWorkNoteId
OPEN CursorIncident FETCH NEXT FROM CursorIncident INTO @incidentId,@worknoteid,@seqnum,@dtEntryDate,@work noteall
--store 1st record of cursor select @worknotesaveid =@worknoteid WHILE (@@FETCH_STATUS=0) BEGIN if @dtEntryDate >=@beginWEDate AND @dtEntryDate <= @currentWEDate Begin if @worknotesaveid <> @worknoteid Begin Select @allnotes = @allnotes + @dtEntryDate + @worknoteall End else BEgin select @allnotes = @allnotes + @worknoteall End
select @worknotesaveid = @worknoteid --save next worknoteId End else Begin select @allnotes='' End FETCH NEXT FROM CursorIncident INTO @incidentId,@worknoteid,@seqnum,@dtEntryDate,@work noteall END --WHILE (@@FETCH_STATUS=0)
CLOSE CursorIncident DEALLOCATE CursorIncident
return @allnotes END
---------- Function not working right. I appreciate any help. Thanks in advance.
Here is the query:WHERE DATEPART(month, " + tableName + ".timestamp)>='" + startTextBox.Text + "' AND DATEPART(month, " + tableName + ".timestamp)<='" + endTextBox.Text + "'This is in a program using C# which is why it's in quotes and all that good stuff. The query itself works properly when startTextBox.Text = 8 and endTextBox.Text = 9. Itreturns results for both months 8 and 9. But when I want a result from a single month, say just 9... I put 9 in both text boxes and it ends up returning no results.Logic would tell me that say that both logics should come back TRUE but for some reason it's failing. Any ideas/suggestions? Thanks in advance!
Hello, Can anyone out there tell me if there's a simple way to calculate the number of week days between two dates in TSQL? Need it to calc. average turnaround times, excluding weekends. Can do it v. easily in VB, but gets a little more tricky in TSQL as there's no way to return the number of Sundays and Saturdays between the two dates. Any help much appreciated !
Jon Reade Sql Server DBA NEC Technologies (UK) Ltd.
Hello, Can anyone out there tell me if there's a simple way to calculate the number of week days between two dates in TSQL? Need it to calc. average turnaround times, excluding weekends. Can do it v. easily in VB, but gets a little more tricky in TSQL as there's no way to return the number of Sundays and Saturdays between the two dates. Any help much appreciated !
Jon Reade Sql Server DBA NEC Technologies (UK) Ltd.
This function will return working hours between given 2 dates. This function assumes that the break is between 9:45 AM and 10 AM and that Lunch is between 12:30 PM and 1 PM. This function also assumes that the working hours are between 7:30 AM and 4 PM. There is a section for public holidays there. We have a table for that you might not so that piece needs to be fixed.
This function will return working hours between given 2 dates. This function assumes that the break is between 9:45 AM and 10 AM and that Lunch is between 12:30 PM and 1 PM. This function also assumes that the working hours are between 7:30 AM and 4 PM. */ AS BEGIN
--check if one of the dates is null if @StartDate is null or @EndDate is null RETURN 0
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U')) DROP TABLE [dbo].[temp_tbl] GO CREATE TABLE [dbo].[temp_tbl]( [tbl_id] [bigint] NULL, [cs_id] [int] NOT NULL, [USERID] [int] NOT NULL,
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?
I had a view in which I did something like this isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005. When I change the query to fld as 'alias' then it works ok in sql 2005 . why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
I have a table which records employees'time-off records. There are 6 columns in this TimeOff table. They are RequestID, EmpName, StartDate, EndDate, Type, NumofDays. I have another table which has all the dates from 01/01/1950 to 01/01/2056.
I am trying write a query which lists all the dates between the timeoff startdate and enddate, including the the start and end dates, but my query so far only lists the start and end date in a timeoff record:
SELECT D.[Date], Datename(dw,D.[Date]) AS Weekday FROM Dates D LEFT JOIN TimeOff T ON D.[Date] = T.OffStartDate OR D.[Date] = T.OffEndDate WHERE (OffType = 'Sick Day' AND EmpName = 'Cat White') AND (D.[Date] BETWEEN T.StartDate AND T.EndDate)
Has anyone ever written a function to retrieve all individual dates between two given dates? Using DATEDIFF I can get the number of days between two dates. However I need to iterate through the days to identify weekend and holiday dates. Has anyone ever written a function to do this?
So, if select datediff(d,'07/01/2007','07/15/2007') as NumOfDays returns 14, I'd need to iterate through the 14 days and get the weekends and holidays. Would I have to use a cursor to iterate through the days?
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn) 'Dim cmd As New SqlCommand("pContact_Add", cn)
Catch ex As Exception Label1.Text = ex.Message End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
I'm trying to generate this query, that displays Budget Current Year , Actual Current Year and Prior Year Revenue. When It comes to the Budget and Actual everything works fine, however when I try to add the query for the Prior Year I get an error, and I realized that the leap date is causing the error
Here is what I'm trying to generate
InnCodeID Quarterly Monthly Days Period Year BARmRev AARmRev PYRmRev
ADDIS Q1 Jan 1 1 2008 NULL NULL
ADDIS Q1 Jan 1 1 2008 3462.14 5107.65
ADDIS Q1 Jan 1 1 2008 NULL NULL
ADDIS Q1 Jan 1 1 2008 NULL NULL
Here is the error that I'm getting:
Code Snippet
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
(4834 row(s) affected)
Here is my Transact-SQL Syntax (summarized because I Couldn't post it):
SELECT
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01'
AND Year(dbo.Trans.TR_Date) = Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1'
THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT Trans1.TR_Amount
FROM dbo.Trans Trans1
WHERE Trans1.TR_Dept = '10' AND TR_Main = '5120' AND TR_Sub = '01' AND trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date)
AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Year(dbo.CurrentDate.CurrDate) AS varchar(4))) AS datetime)
AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND trans1.datatype = dbo.Trans.DataType) ELSE NULL END) * - 1 AS BARmRev,
--AA Script Here AS AARmRev,
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01' AND Year(dbo.Trans.TR_Date)
= Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1' THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT SUM(Trans1.TR_Amount)
FROM dbo.Trans Trans1
WHERE RIGHT(RTRIM(Trans1.TR_Dept), 2) = '10' AND Trans1.TR_Main = '5120' AND Trans1.TR_Sub NOT BETWEEN '04' AND '05' AND
trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2))
+ '/' + CAST(Year(dbo.CurrentDate.CurrDate)-1 AS varchar(4))) AS datetime) AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND
Hello All, I am trying to Add certain number of days to a particular date. and my requirement is that it need to exclude all saturdays and sundays and then give me the resultant date in Sqlserver. Please can anyone help me in achieving it. thanks Shiva Kumar
Hi can anyone help me , or am I on the wrong track.
Is there any easy way to create a stored procedure that inserts into a table the relevant months dates into a table based on the month and year as parameters. I.e say the parameters passed are 01/2001 hence based on this all of the month of January 2001 dates are inserted into a table in this format : 'Jan 01 2001 12:00:00'
We are deploying a system in the UK in SQL Server 6.5. They want the date to default to dd/mm/yy. Is there a way to set this on a permanent basis? Is there a way to set the server so any time a date is display, including getdate(), the result is dd/mm/yy?
I have a table which contains apart from other fields, 2 dates. i.e. startdate and enddate.
The startdate is always less than / equal to enddate.
I want to write a stored proc where I will give a date as a parameter and it should give me all the records there my date is between the startdate and the enddate.
Also I want to create a temp table in the procedure and the table gets populated with all the dates that comes between startdate and enddate, both dates inclusive.
I know this might be a tall order, but I am new to writing stored procedure, so I will be obliged if somebody can help me out
I have an assignment using SQL this is the first time i have used it but i can understand the basics.
It is for an instrument service company and i need to find what instruments have not been serviced for a year. i already know i have to:
SELECT instrument number, date of last service FROM Instrument table
but after this i am unsure what code i can use in order to find all instruments that have not been servided in over 365 days what code can is best to use?
I have a columns with storeID's and dates i need to be able to find the difference of date for a certain store for its Min(Date) to the End of that Month
example
Date StoreID Difference of Date to end of the Month 2008-01-02 00:00:00.000 163 2008-01-03 00:00:00.000 165 2008-01-01 00:00:00.000 167 2008-01-02 00:00:00.000 180
I am trying to calculate our job value and product costs over a given time period. But if I run my query, the numbers just dont look right at all. Please can you have a look at this query and see if anything stands out?
-- FINISHED PRODUCTS PRODUCTION REPORTS DATA -- gets current WIP Job information. -- requires item details from this. USE Staging -- declare start and end variables DECLARE @Start DATETIME DECLARE @End DATETIME SET DATEFORMAT DMY SET @Start = '01/01/2008' SET @End = '01/02/2008' SELECT SUM(jbs.Value) AS ProducionValue, SUM(prd.Cost) AS Cost, CONVERT(CHAR(10), @Start, 103) AS [From], CONVERT(CHAR(10), @End, 103) AS [End] FROM ( SELECT Job.[Job No], Job.[Cust Name], Job.[Req'd Date], Job.ProdStartActual, Job.Dept, [Job].[Value], Job.Loan FROM Job WHERE Job.[ProdStartActual] BETWEEN @start AND @End AND Job.Cancel = 0 AND Job.Sales = 1 AND Job.Invoiced = 1 OR Job.[ProdStartActual] BETWEEN @start AND @End AND Job.Cancel = 0 AND Job.Sales = 1 AND Job.Invoiced = 2 ) AS jbs INNER JOIN ( SELECT Item.[Job No], Item.[Code] FROM Item ) AS itm ON jbs.[Job No] = itm.[Job No] INNER JOIN ( SELECT Product.[Product Code], Product.[Cost] FROM Product ) AS prd ON itm.Code = prd.[Product Code]
