hi
i wanted to crete an extra colunm when i retrive a set of rows...
the date colunm must show the current date eg 15/06/2006
currently im writing it as follows
select cost, '15/06/2006' as CDate
from prices
as you can see this is static date i need the date to be current whenever the query is done.
Hi,
I'm making some sort of application where people can add their resume.
They also need something to add places where they have worked or currently are working.
I have a form where they can add this and i add this experience using the following stored procedure:
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_Insert_Experience]
@ExperienceId uniqueidentifier,
@ExperienceEmployee nvarchar(100),
@ExperienceFrom datetime,
@ExperienceUntil datetime,
@ExperienceQualifications nvarchar(250),
@ExperienceTechnologies nvarchar(250),
@ExperienceTasks nvarchar(250)
as
insert into Experiences
values(@ExperienceId,@ExperienceEmployee,@ExperienceFrom,@ExperienceUntil,@ExperienceQualifications,
@ExperienceTechnologies,@ExperienceTasks);
It must be possible to add the place where they currently are working. Then the ExperienceUntil has to be something like still going on. Then I decided that the user then had to set the current date.
But what I want is the following, I want that the ExperienceUntil keeps updating automatically, like everytime i get that record, it has to have current date.
Is this possible ?
But if the ExperienceUntil isn't the current date , it just had to take the supplied parameter date.
I've this query
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)
Let's say, current date is 8 AUG 2005 and current time is 2045
So, i will get
ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
206 | 8/1/2005| 1950 | 88
...
...
207 | 8/7/2005| 1845 | 77
208 | 8/7/2005| 2255 | 77
209 | 8/7/2005| 2140 | 77
Can someone can show me to filter data between
t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND TIME>=CurrentTime
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101) AND TIME<=CurrentTime
If current date is 8 AUG 2005 and current time is 2045, so the result shown as follow
ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
...
...
207 | 8/7/2005| 1845 | 77
I only have this query,
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)
lack of idea to put the TIME condition.
Plz help me..
I need help with creating a query that compares the current date with a stored date field. If the difference between the two dates is greater or equal to 5 days for example, I need to be able to return these records. I am not sure if this can be done through a query alone but any help and suggestions would greatly be appreciated. Thanks in advance.
View 4 Replies View Relatedhow 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).
I have a table named "shift" and I need to setup my query to return only data where the field "startime" = today. The problem I am running into is the starttime field it laid out like "2005-12-29 14:00:00" with different time values. I need to ruturn everything that has todays date regardless of the time value. I tried using GetDate() but that is returning data for other days as well or just data before or after the current time. Does anyone have any suggestions? This is driving me crazy! Thanks, Garrett
View 7 Replies View RelatedI have a reference table that currently has no web front-end. It's a small table(<10 rows) that's not going to change very often (maybe once every few months).
We manually update rows on the table via the GUI table interface in Enterprise Mgr., not in T-SQL.
What I'd like to do is have SQL Server automatically update the "Last_Modified" column with the current timestamp. I can do it on an Insert using the GetDate() function, but if I update a row, this doesn't work.
Is there a function I can use that can auto-populate for both insert and updates?
Hi,
I tried to create parameters in my report. I need one date picker. So i select data type as datetime. in default values i selected as non-queried and in date time functions i selected =Today. But am unable to set current date. Please help me to solve it.
Regards
Nataraj.C
I am writing a ASP.NET C# web application. I will need to store the date in one field in one of my tables. It appears that I need to use the datetime data type for the date in SQL Server 2005.
So I have a question
1.) How do I get today's date in C# and how should this be passed to SQL server?
I am trying to SUM a column of ActivityDebit with current Calendar_Month to a Column of Trial_Balance_Debit from Last Calendar_Month. I am providing Temp Table code as well as fake data.
=====
IF OBJECT_ID('TempDB..#MyTrialBalance','U') IS NOT NULL
DROP TABLE #MyTrialBalance
CREATE TABLE #MyTrialBalance (
[Trial_Balance_ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
[FISCALYEAR] [smallint] NULL,
[Code] ....
Here is my Query I am trying but not working. I cant figure out how to doo the dateadd for correct column.
SELECT A.Trial_Balance_ID,A.ACTIVITYDEBIT --SUM(A.ACTIVITYDEBIT + B.Last_Trail_Balance_Debit) AS New_TB
FROM
(SELECT [Trial_Balance_ID], [Calendar_Month],[ACTIVITYDEBIT]
FROM Mytrialbalance
WHERE actindx='48397' AND ACTIVITYDEBIT='820439.78000'
)A INNER JOIN
(SELECT [Trial_Balance_ID],DATEADD(MM, -1,Calendar_Month)AS Last_Month
FROM Mytrialbalance) B ON B.Trial_Balance_ID=A.Trial_Balance_ID
the requirements are to return person ID with the most recent leaving date from a scheduled dept, who has previously arrived at an unscheduled dept within 7 days, but just now my query shows all the previous leaving dates within 7 days of that unscheduled dept arrival, I only want the last leaving date before the arrival at the unscheduled dept:
So for instance looking at a copy of one person's date below I have:
PersonID Last Dept Arrival Date To Last Dept Leaving Date From Last Dept Next Arrival Date to Unscheduled Dept
======== ================= ========================= =========================== =====================================
0106573268Dept 5 2013-03-01 2013-03-03 2013-03-05
0106573268Dept 6 2013-02-27 2013-02-27 2013-03-05
0106573268dept 2 2013-02-26 2013-02-26 2013-03-05
In the data above I only want to return the first row, which is the most recent leaving date before arrival at an unscheduled dept.
My query is much the same as before except my inline view is looking at the data for last scheduled leaves from depts in my inline view and also the outer query returning all arrivals to the unscheduled dept:
SELECT b.personID
,b.dept AS "Last leaving dept"
,b.arrival_Date as "arrival Date To Last dept"
,b.leaving_Date AS "leaving Date From Last dept",
a.[arrival Date] as "Next arrival Date to AREA_GH"
FROM Unscheduled_Arrival a INNER JOIN (SELECT *
FROM scheduled_Leaves
where [leaving date] is not null) b
ON a.Person_ID = b.Person_ID
[code]....
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))
Afternoon all,
I have a colunm sName(nVarChar(255) which has first name and second name in it and is divended with a ",". Can somebody show me to put these in there own colunm sFirstName and sSecondName.
Very Kind regards.
Hello
Can some body help me in performing this?
I want to move the data from Colunm Value to Colunm CPublicID whenever CCField = 'PublicID' as shown below in green color( Without using Cursor or While loops)
CPubID ------------CCTable ------------CCField------------ DWTable------------ DWField ------------Value
101 XYZ Type Dimension Flag Related
101 XYZ ClaimAssocType Dimension Key Related
101 XYZ ID Dimension Flag 1890
101 XYZ ID Dimension Key 1890
101 XYZ PublicID Dimension Flag 103
101 XYZ PublicID Dimension Key 103
101 XYZ PublicID Dimension Flag 230
101 XYZ PublicID Dimension Key 230
101 XYZ PublicID Dimension Flag 5691
101 XYZ PublicID Dimension Key 5691
CPubID ------------CCTable ------------CCField------------ DWTable------------ DWField ------------Value
101 XYZ Type Dimension Flag Related
101 XYZ ClaimAssocType Dimension Key Related
101 XYZ ID Dimension Flag 1890
101 XYZ ID Dimension Key 1890
103 XYZ PublicID Dimension Flag 103
103 XYZ PublicID Dimension Key 103
230 XYZ PublicID Dimension Flag 230
230 XYZ PublicID Dimension Key 230
5691 XYZ PublicID Dimension Flag 5691
5691 XYZ PublicID Dimension Key 5691
Also , I need to know the best practice to perform the below process. Which of them is advisable?
I have a table, say as above, which has lots of data, say 100,000 records. I need to look at each row and perform certain updates/ trasnformation to a colunm (Value). I aslo, need to hold the old colunm. I came up with two method
Process A) Clone the table. So I have table2 which is a copy of table1. Read the Table1, update / trasnfored the Table2
Table1 Colunm(Value) will have Old values and table 2 colunm(Value) will have transformed values
Process B) Clone the Colunm. So I have a colunm Value and its clone Value2. Read the table and value for Colunm Value and Update the Value2.
I am using process A. because I beleive , process B, reading and updating same table make sthe entire process slow.
Please advice on th ebest industrial practice of doing this.
Thanks in advance for all the help
Hello All
I want to creat a colunm from two colunms in a specific format
COL1 COL2 COL3
123456 01-10-2007 12070001 ( 12: From First two digit of Col1, 07 from the year of colunm2 and 0001: if combination 1207 is first else increment)
125647 01-11-2005 12050001
126756 12-25-2007 12070002 (* here since combination of 1207 is repeating it gets increment to 0002)
Can somebody help me please
Afternoon all.
I have a table which has a column with has been inserting incorrect data, here is an example. Column name is description and table name is tblClientUsage:
[companyname]: 288bsl | | 13/03/2007
[companyname]: 288asl | | 08/03/2007
[companyname]: RES04sl | | 19/02/2004
[companyname]: RES01sl | | 19/02/2004
[companyname]: AAsl | | 31/12/2005
[companyname]: 652Asl | | 05/02/2007
[companyname]: GAZ1sl | | 20/03/2007
[companyname]: AAsl | | 31/12/1999
[companyname]: 363asl | | 20/01/2007
[companyname]: 288b1sl | | 02/11/2006
[companyname]: 288bsl | | 02/11/2006
Now what has been happening is that is has not been putting the company name in the column, just the text companyname. Now i have a table which has the company name and company id, i can easily join the two tables together but how can i replace just the companyname text in the description column with leaving the text after.
Any help would be great
When I submit a sql :
select * from view from column=''
the table is a view point to oracle table with ole db provider for oracle
In mssql2000, the result return rows which containing a space ' '
However, in mssql2005, the result return no rows .
Any advise on it?
Is there a system word for the current date in a stored procedure or do I have to pass it in as a parameter ?
ie
BK_DateRequired <= Today
Hello,
I'm new to SQL server and the SQL language itself and I'm having a bit of problem.
Is it possible to obtain the current date from the system?
Something like 'sysdate' or anything like that.
I want to compare the current date with a date stored in the database but I can't seem to find the wright SQL server keyword, or is there none?
Many thanks
Does anyone know how I can get the current date inserted to a SQL query to only get results of today?
My SQL is:
SELECT Business.BusinessCODE, Business.Name, Business.BusinessNumber, Business.BusinessStatusCODE, Business.Done, Business.StartDATE, Business.EndDATE, Business.TotalPrice, Business.Contribution, Business.BusinessProcessCODE, Customer.Name1, CaesarUser.FirstName, CaesarUser.Surname, BusinessHistory.UpdateDATE
FROM Movex_SMS_50_Server.dbo.Business Business, Movex_SMS_50_Server.dbo.BusinessHistory BusinessHistory, Movex_SMS_50_Server.dbo.CaesarUser CaesarUser, Movex_SMS_50_Server.dbo.Customer Customer, Movex_SMS_50_Server.dbo.relCaesarUserBusiness relCaesarUserBusiness
WHERE Business.CustomerID = Customer.CustomerID AND relCaesarUserBusiness.BusinessID = Business.BusinessID AND BusinessHistory.BusinessID = Business.BusinessID AND relCaesarUserBusiness.CaesarUserID = CaesarUser.CaesarUserID AND
((BusinessHistory.Description='Créé' OR
BusinessHistory.Description='Created' OR
BusinessHistory.Description='Creado' OR
BusinessHistory.Description='Erstellt' OR
BusinessHistory.Description='Creato')AND
((BusinessHistory.updatedate = TODAYS DATE)) AND
(Customer.EmployeeCountCODE=0xEC6D3BDB8B6FD511AC240006294308D9 OR
Customer.EmployeeCountCODE=0xFC673BDB8B6FD511AC240006294308D9))
ORDER BY CaesarUser.Surname, Customer.Name1
Hi guys,how do i insert the current date in SQL?
View 2 Replies View RelatedHi,
Can I write a code in SQL that return the current date? If so, how?
Thanks!
Will
Hi. I have a stored procedure that looks for events on the current date. I tried using GETDATE() but i realized that is looking for the current date AND time. how can i use this to find only the current date? thanks a bunch for your help!
View 3 Replies View Relatedhi,
If today is the 22/4/08
how can I get the day last month,
eg. 22/3/08
?thank you
From my table:
convert(varchar,Rec.expdate,101) as exp:
01/19/2038
08/12/2013
08/12/2013
I only want record is >= the current date but looks like my syntax is not working right because I got nothing return instead of 01/19/2038.
and convert(varchar,Rec.expdate,101) >=convert(varchar,getdate(),101)
Hi,I have a simple table called events, which lists the start and end dates of events. I'm using a calendar control that queries the db for events, but at the moment it does a check for every day by passing in the day. (Very inefficient) What I'd like to do is pass in the current month, and get a set of rows that have an event which is in that month. It sounds easy, but I'm a little confused about what to do if the event starts current month -1 and ends currentmonth +1 I obviously need to return results like these also.
View 4 Replies View RelatedHi, I've just started using SQL Server Express with VWDE 2005 and I have a database with one table called EVENTS which has a datetime column called DATE. I want to select records where the DATE value is in the next two months.First I tried selecting records where the DATE value is >= today. I used the query builder to produce... SELECT [Date], [Title] FROM [Events] WHERE [Date] >= GETDATE()but got the error "Undefined function 'GETDATE' in expression".Am I missing something obvious?
View 5 Replies View RelatedHi!
I'm new to T-SQL and trying out my queries using the SQL Query Analyzer.
What I intend to do is to retrieve records based on current date. The field is Trans_R.Tr_Date, of type nvarchar(255) and the data inside the field is formatted as yyyymmdd.
My Q's are:
1. Where should I put the WHERE statement at?
2. Is there any other way to work around this problem without resorting to converting the Trans_R.Tr_Date field to Datetime datatype beforehand?
3. I tried using Getdate function as part of the WHERE statement but nothing comes out. Would this WHERE statement work (or make sense)?
WHERE Trans_R.Tr_date = getdate()
Please advise and thanks in advance.
-------------
START OF CODE
-------------
SELECT
Cards.card_number,Trans_R.Tr_Date,Trans_R.staff_nu mber,
MIN(Trans_R.Tr_In) AS Actual_Time_In,
MAX(Trans_R.Tr_Out) AS Actual_Time_Out,
'Status'= CASE
WHEN MIN(Trans_R.Tr_In) IS NULL THEN 'ABSENT'
WHEN MIN(Trans_R.Tr_In) < 171500 THEN 'ATTEND'
ELSE 'ABSENT' END,
'Late Time' = CASE
WHEN (MIN(Tr_In) - 080000> 0) THEN (MIN(Tr_In) - 080000)
ELSE '' END,
FROM Trans_R INNER JOIN Cards
ON Trans_R.Staff_Number = Cards.Staff_Number
GROUP BY Cards.card_number,trans_r.staff_Number,Trans_R.Tr_ Date
-------------
END OF CODE
-------------
All,
I have a stored procedure that uses two parameters (@StartDate, @EndDate) and I want to pass the current date (no time) and when I try:
EXEC sp_AMR_AllComplSummary convert(varchar(12),getdate(),101), convert(varchar(12),getdate(),101)
I get Incorrect syntax near the keyword 'convert'.
AND Trying:
EXEC sp_AMR_AllComplSummary [convert(varchar(12),getdate(),101)], [convert(varchar(12),getdate(),101)}]
I get Error converting data type nvarchar to datetime.
This should be simple and I am lost.
Thanks in advanced.
Hello
I'm trying to figure out how to write this query...for example,
A stud attended University of Toronto in 2001 and then attended Seneca College in 2006 ..... I want to select the Education Institute that the stud attended last.
Thank you
Hey all, how do you take the current date and subtract a year from it in a SP?
What I want to do is...
Take the current date when the SP is ran, subtract a year, then if my date field is within that range (higher than the date with the subtracted year) it will continue in the query.
Edit: the answer is..
dateadd(yyyy, -1, getdate()) as Date1
Can anyone explain the result of the sql statement
Select DATEADD(wk, DATEDIFF(wk, 6,getdate()), 6)
Can we use this to get the start date of the current week always???
I have a query that will go into an ssis package (eventually). The package will run every night at 3am. I need to capture the last 24 hours of by using something like:
SELECT worktype, changedate, woclass
where siteid = 'GTM' and woclass = 'WORKORDER' and istask = 0
[highlight=#ffff11]and changedate between '2015-01-13 03:00:00' and '2015-01-14 03:00:00'[/highlight]
I know I am not doing the between correctly to get the changedate between the last 24 hours. Is there a way to correct this so that I am only getting the change date that is between 3am today and 3am yesterday on any given day I happen to run this?