Transact SQL :: Date Arithmetic With Date And Datetime
Sep 21, 2015
if I do this:
print @@version
print 'arithmetic with datetime'
go
begin try
declare @datetime datetime = getdate()
[Code] ....
... I get this:
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64)
Mar 26 2015 21:18:04
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
arithmetic with datetime
@datetime = Sep 22 2015 1:39PM
arithmetic with date
Msg 206, Level 16, State 2, Line 3
Operand type clash: date is incompatible with int
Why the inconsistency? Datetime is lenient in this regard - I can even do set @datetime += 0.5 (although the meaning is less intuitive).
View 6 Replies
ADVERTISEMENT
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
Dec 2, 2015
I have a table of errors with a DateTime field for when the error occurred. I want to query the table for a given date range omitting the time portion. What is the most efficient way to perform this query?
View 5 Replies
View Related
Sep 18, 2015
I need to set only the date part of tblEventStaffRequired.StartTime to tblEvents.EventDate while maintaining the time part in tblEventStaffRequired.StartTime.
UPDATE tblEventStaffRequired
SET StartTime = <expression here>
FROM tblEvents INNER JOIN
tblEventStaffRequired ON tblEvents.ID = tblEventStaffRequired.EventID
View 4 Replies
View Related
Nov 9, 2015
I just need the date, hour, and minute...not the micro seconds. Do I have to DATEPART and concatenate each or is there any way to simply truncate the milliseconds from it? Or is there a date format to put extract and report on it as...
MM/DD/CCYY HH:MM:SS AM
I see there is a format 131 that puts it in..
DD/MM/YYYY HH:MM:SS:MMMAM
View 7 Replies
View Related
Aug 3, 2015
How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
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
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
Oct 28, 2015
i have written a sql function which returns only number of working days (excludes holidays and Weekends) between given StartDate and EndDate.
USE [XXX]
GO
/****** Object: UserDefinedFunction [dbo].[CalculateNumberOFWorkDays] Script Date: 10/28/2015 10:20:25 AM ******/
SET ANSI_NULLS ON
GO
[code]...
I need a function or stored procedure which will return the date which is 15 working days (should exclude holidays and Weekends) prior to the given future Date? the future date should be passed as a parameter to this function or stored procedure to return the date. Example scenario: If i give date as 12/01/2015, my function or stored procedure should return the date which is 15 working days (should exclude holidays and Weekends) prior to the given date i.e 12/01/2015...In my application i have a table tblMasHolidayList where all the 2015 year holidays dates and info are stored.
View 18 Replies
View Related
Aug 31, 2015
So my data column [EODPosting].[MatchDate] is defined as a DATE column. I am trying to SELECT from my table where [EODPosting].[MatchDate] is today's date.
SELECT*
FROM[dbo].[EODPosting]
WHERE[EODPosting].[MatchDate]=GETDATE()
Is this not working because GETDATE() is like a timestamp format? How can I get this to work to return those rows where [EODPosting].[MatchDate] is equal to today's date?
View 2 Replies
View Related
Apr 19, 2015
My requirement is to get the earliest start date after a gap in a date column.My date field will be like this.
Table Name-XXX
StartDate(Column Name)
2014/10/01
2014/11/01
2014/12/01
[code]...
In this scenario i need the latest start date after the gap ie. 2015/09/01 .If there is no gap in the date column i need 2014/10/01
View 10 Replies
View Related
Apr 23, 2015
My requirement is to get the latest start date after a gap in a month for each id and if there is no gap for that particular id minimum date for that id should be taken….Given below the scenario
ID StartDate
1 2014-01-01
1 2014-02-01
1 2014-05-01-------After Gap Restarted
1 2014-06-01
1 2014-09-01---------After last gap restarted
1 2014-10-01
1 2014-11-01
2 2014-01-01
2 2014-02-01
2 2014-03-01
2 2014-04-01
2 2014-05-01
2 2014-06-01
2 2014-07-01
For Id 1 the start date after the latest gap is 2014-10-01 and for id=2 there is no gap so i need the minimum date 2014-01-01
My Expected Output
id Startdate
1 2014-10-01
2 2014-01-01
View 4 Replies
View Related
Sep 29, 2015
how to write a query to get current date or end of month date if we pass year and month as input
Eg: if today date is 2015-09-29
if we pass year =2015 and month=09 then we have to get 2015-09-29
if we pass year =2015 and month=08 then we have to get 2015-08-31(for previous months we have to get EOMonth date & for current month we have to get current date).
View 3 Replies
View Related
Aug 13, 2015
How to get the date where planned delivery date key is less than getdate()
I want to make a view where I want put the condition :
select CONVERT(datetime, CAST(planned_delivery_date_key AS CHAR(8)), 101) < CONVERT(datetime,getdate(),101)
from qlk_fact_sales_back_order
View 2 Replies
View Related
Nov 23, 2015
I have a the following date variables that's being set around the current date. how to adjust it to work around a date variable @Date instead of the current date? Lets the @Date = 2015-06-30 then the it would adjust the variable below accordingly. I'm assuming the getdate()) needs to be replaced with @Date but I can't seem to get it to work.
Declare @EndOfLMPriorYear Date = DateAdd(yyyy,-1,EOMONTH(Dateadd(Month,Datediff(Month,0,getdate())-1,0)))
Declare @EndOfPriorMonth Date = EOMONTH(Dateadd(Month,Datediff(Month,0,getdate())-2,0))
DECLARE @EndOfLastMonth Date = EOMONTH(Dateadd(Month,Datediff(Month,0,getdate())-1,0))
View 3 Replies
View Related
Sep 28, 2015
Basically, the sample raw data is a result from my last cte process which consolidate all the records. I want to find the closest date in PO ended using Receipt_date column but my problem, for this ESN R9000000000019761824, i wanted to get the latest or the last transaction date.
please see below DDL and sample data.
--this is the result from my last cte process
Create table #sample
(ESN nvarchar(35),ESN2 nvarchar(35), Receipt_Date datetime,PO_ENDED datetime)
insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED)values('990002036924452','990002036924452','2015-01-07 17:39:44.660','2014-01-09 04:13:29.000')
[Code] .....
Expected result:
ESN-------------------ESN2-------------Receipt_Date-------------POENDED---------------
--------------------------------------------------------------------------------------
990002036924452-------990002036924452--2015-01-07 17:39:44.660--2015-04-23 20:26:55.000
990002038378525-------990002038378525--2015-01-07 05:29:56.923--2015-05-29 14:50:52.000
R9000000000019761824--354403060637509--2015-01-03 19:23:54.980--2015-03-20 16:43:07.000
[Code] ....
View 8 Replies
View Related
Sep 11, 2015
I require outputting the date difference between two date's if it is greater than 7(DateDiff(day, DateAdd(day, t.[Started], Nxt.started), (t.[started])) > 7).I get incorrect syntax on my operator.What is the correct code?
View 6 Replies
View Related
Jul 23, 2015
I am working into a t-sql where i need to expand the date within a date parameter. For example:
Start Date = '1/1/2014'
End Date = '12/31/2014'
I want to expand this as:
01/2014
02/2014
03/2014
04/2014
05/2014
06/2014
up to
12/2014
View 4 Replies
View Related
Oct 30, 2006
Hi,
I have a date and time variable like dateVariable = '30/10/2006 12:45:36 AM'.(DD/MM/YYYY HH:MM:SS)
From this variable how to fetch only the date part in the format of MM/DD/YYYY
How to write sql query
I mean the result should be like 10/30/2006.
thnx
shaji
View 6 Replies
View Related
Jun 15, 2015
How can I get a date that would be last/previous Monday from given date?
For example for given 22/01/2015 it would give 19/01/2015.
View 16 Replies
View Related
Mar 11, 2014
I am inserting date and time data into a SQL Server 2012 Express table from an application. The application is providing the date and time as a string data type. Is there a TSQL way to convert the date and time string to an SQL datetime date type? I want to do the conversion, because SQL displays an error due to the
My date and time string from the application looks like : 3/11/2014 12:57:57 PM
View 1 Replies
View Related
Oct 26, 2000
Hi,
Is there any function can give me the date portion only out of Datetime fieldname.
Thanks
View 2 Replies
View Related
Mar 4, 2008
Is there a single function that does that instead of using 3 datepart functions and puting that all together?
View 1 Replies
View Related
Sep 19, 2006
Hi,I have the following query:SELECT dbo._Fault.id, dbo._Fault.date_created, dbo._Fault.reference,dbo._Fault.class, dbo._Fault.owner, dbo._Fault.Contact_Name, dbo._Fault.Product,dbo._Fault.Problem_Description,dbo._Fault.Action_Taken, dbo._Fault.Problem_Solution,dbo._Fault.Duration_start_date, dbo._Fault.Duration_duration,dbo._Fault.Duration_date_summary,dbo._Fault.Fault_Status, dbo._Fault.Fault_Source, dbo._Fault.Part_Number,dbo._Fault.Serial_No,dbo._Product.id AS PRODUCTID, dbo._Product.descriptionFROM dbo._Fault LEFT OUTER JOINdbo._Product ON dbo._Fault. Product = dbo._Product.idI need an additional field appended to the end of this (called'DATECOMPLETED') that will take the 'Duration_start_date' and add the'Duration_duration' to it - effectively giving me a new datetime field thatis newer than the start date.I have tried unseccessfully to amend the code but I am afraid my knowledgeof SQL is limited. I would greatly appreciate if someone could show me howto do this.Thanks,Darren
View 2 Replies
View Related
Oct 30, 2007
I am trying to count the amount of distinct dates (not datetime) in a table row. The call below returns the amount of distinct datetimes. How do I strip off the time when doing the SQL call?
SELECT COUNT(DISTINCT DT) FROM Event
View 2 Replies
View Related
Mar 11, 2004
Hi
What is the best practice to get the date from a smalldatetime field without the time.
The table contains 5 minute readings for energy consumption in the column period.
Now i need to get all the readings form some dates.
SELECT dbo.TBL_Data.*
FROM dbo.TBL_Data
WHERE (Period IN (CONVERT(DATETIME, '2003-12-31', 102), CONVERT(DATETIME, '2004-01-01', 102)))
this result contains only the readings for the timestamp 00:00
so how to select the whole day ?
kind regards
piet
View 1 Replies
View Related
Sep 2, 2004
Hi,
I have used smalldatetime datatype to store my date and time values. i want to store just the date or time but the problem is it stores both the date and time. For eg, if i add the the date 03/11/2004, it also the stores the current time automatically. so the new value will be something like 03/11/2004 10:00:00 AM where i want just 03/11/2004. further problem is even though i managed to store just the date like 03/11/2004 in the database, whole date and time shows up when i display it in my pages.
any help will be appreciated.
thanx,
View 4 Replies
View Related
Jun 15, 2006
Hi all
How to get just the date portion from a datetime field from sql server?
thanks a lot
View 5 Replies
View Related
Dec 6, 2005
I have a field that contains only a date, and a field that only contains times. If I try to add the two together, I get some meaningless date like year 2111.
The raw data looks like this
EVT_DT='2005-12-05 00:00:00'
EVT_TM='2005-12-06 13:59:00' //today's date
I wrote a function that gives me the minutes past midnight for the EVT_TM
and use a dateadd(n,myMinutesFuntion(EVT_TM),EVT_DT), but it kills the performance in the nexted cursor.
Thanks,
Carl
View 2 Replies
View Related
Jul 23, 2005
Is there any other way which is more simpler(shorter) to get the datepart (I don't want the time part ) of Datetime than this ?Convert(nvarchar, DATEPART(dd,[Date]) ) + '/' + Convert(nvarchar,DATEPART(mm,[Date])) + '/' + Convert(nvarchar, DATEPART(yy,[Date])) As[Date]
View 1 Replies
View Related
Dec 29, 2006
I have a report with a StartDate and EndDate parameter. By default StartDate is the first day of the month (12/1/2006 12:00:00 AM) and EndDate is the last day of the month (01/01/2007 12:00:00 AM). At the bottom of the report, I want it to print StartDate & " through " & EndDate, but I don't want the time on there. My inital thought was to use convert as seen below
=CONVERT(varchar,Parameters!StartDate.Value,101) & " through " & CONVERT(varchar,Parameters!EndDate.Value,101)
When I preview the report I get the following error:
[BC30684] 'CONVERT' is type and cannot be used as an expression.
I really have no idea why I can't use CONVERT in a textbox expression. Does anyone haev any recommendations for getting the following in my footer?
Report Range: 12/01/2006 through 01/01/2007
View 4 Replies
View Related
Sep 3, 2007
OK, I know this is really simple but I can't get my statement to pull just the date from a datetime field!
Here's my query:
select *
from tblPR
where date between convert(datetime, dateadd(day,-day(getdate())+1,getdate()),103)
and convert(datetime, dateadd(day,-day(getdate()),dateadd(month,1,getdate())),103)
I get no errors but I get the timestamp too and I only want the date.
Where am I going wrong?!
Thanks in advance,
Brett
View 3 Replies
View Related
