Select And Update Query For Date
Nov 20, 2007
Hi Friends,
I need a select query to identify how many records violate the basic dateformat, later on i update the junkdata with null.
Initially I have a database, in which one field(for Date) is given as Varchar, now as we know varchar accepts all types of data, when migrating the same data to another server, i am using Date as datatype for the new field and want to remove all other format's of data (Junk data) entered to that field and want to ratain only the general format i.e, MM/DD/YYYY or MM/DD/YYYY, this query should also support MM<=12, DD<=31
Regards,
Prasad K
View 2 Replies
ADVERTISEMENT
Aug 22, 2006
have a table with students details in it, i want to select all the students who joined a class on a particular day and then i need another query to select all students who joined classes over the course of date range eg 03/12/2003 to 12/12/2003.
i have tried with the following query, i need help putting my queries together
select * from tblstudents where classID='1' and studentstartdate between ('03/12/2004') and ('03/12/2004')
when i run this query i get this message
Server: 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.
the studentstartdate field is set as datetime 8 and the date looks like this in the table 03/12/2004 03:12:15
please help
mustfa
View 6 Replies
View Related
Nov 6, 2013
i want to select a data from a employee table and update that select data to attendlog1 table with match Eid
this is a employee data
EID----------bid
26478---------2
this is a attendlog data
EID-------------EBID
26478------------NULL
i want this type of result
EID-------------EBID
26478------------2
BID and EBID fields are change but values are same.
View 2 Replies
View Related
Oct 26, 2007
i am selecting the top 25 records from a table for processing. i would like to set the processstatus field to 3 as soon as i select the records.
is there a way to do this in one query?
View 17 Replies
View Related
Nov 20, 2007
Hi Friends,
I need a select query to identify how many records violate the basic dateformat, later on i update the junkdata with null.
Initially I have a database, in which one field(for Date) is given as Varchar, now as we know varchar accepts all types of data, when migrating the same data to another server, i am using Date as datatype for the new field and want to remove all other format's of data (Junk data) entered to that field and want to ratain only the general format i.e, MM/DD/YYYY or MM/DD/YYYY, this query should also support MM<=12, DD<=31
Regards,
Prasad K
View 2 Replies
View Related
Jul 20, 2005
I have a table with only one row to get unique ID-numbers. To get the nextID-number I have to increase the number in the row: first, get the numberfrom the database, and then do the update.Some ASP-code:Set rs = db.execute("SELECT id FROM tbl_id")id = rs("id")db.execute("UPDATE tbl_id SET id ="& id + 1 &"")This is not safe: two different sessions cat get the same ID.Is it possible to increase the value of id in tbl_id and get the value atthe same time? Or is there any other way?/matte
View 6 Replies
View Related
May 5, 2008
HI Guys,
I have two table t1 and t2
t1 has a,b,c columns(no primary key defined)
t2 has a,b columns (a is primary key)
I want to update the table t2 with follwin query
update t2 set b=(select avg(b) from t1 group by a) where a=(select a from t1 group by a)
Is it possible to update table of result of other query?
or give me other solution for that how can i do that thing...?
View 1 Replies
View Related
Apr 6, 2015
I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below
Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
The table B stores the item process time. The structure is as below
Item ProcessStartDate ProcessEndDate
V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000
A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000
B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000
C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000
I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.
Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.
The result expected is
Table A
Name StartDate EndDate
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
Table B
Item ProcessStartDate ProcessEndDate
A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000
B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000
C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000
Scenario 2 expected result
Table A
Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Table B
Item ProcessStartDate ProcessEndDate
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000
View 8 Replies
View Related
Jul 20, 2005
hi myself avii am developing one appliacaion in which i am using vb 6 as front end,adodb as database library and sql sever 7 as backend.i want to update one table for which i required data from other table. andiretrive data from second table by giving some condition. when i get data,then to update first table i need to use do while loop. instead of that iwant to use select statement directly in update query.plz give me some help.following is the my queries and its out putStrSql = ""StrSql = "Select * From SalesVchMaterialDesc where TransactionID=" &txtTransactionID.text & ""rsMName.Open StrSql, Conn, adOpenKeysetDo While Not rsMName.EOFStrSql = ""StrSql = "Update StockTable Set Outward=Outward - " &rsMName("Netweight") & ",OutwardQty=OutwardQty - " & rsMName("Qty") & "Where MaterialId=" & rsMName("Material_Name") & " and VoucherDate='" &Format(rsMName("VoucherDate"), "mm/dd/yyyy") & "'RsAdd.Open StrSql, Conn, adOpenStaticrsMName.MoveNextLooprsMName.Closeout put***main querySelect * From SalesVchMaterialDesc where TransactionID=848do while not loopUpdate StockTable Set Outward=Outward - 8.06,OutwardQty=OutwardQty - 1Where MaterialId=221 and VoucherDate='04/01/2004' and SMID=0loop
View 1 Replies
View Related
Feb 21, 2007
Is it possible for me to do something like
update table1 SET var1=something,var2=something2
from table1
(SELECT * from table2) as newtable
where newtable.field1=acondition
View 5 Replies
View Related
Mar 21, 2005
I'm converting an ASP system from using msaccess to SQL Server as the db engine, and I'm stumped on the following query
update
timecard
set
TcdDate = #3/18/05#
TcdDate is defined as a date/time type
It will not run with the date bracketed by # signs, and when I take them out, 1/1/1900 is stored in the dbs. Is there a different symbol to bracket the date with or should I be using a function to convert the date?
View 4 Replies
View Related
Aug 29, 2007
...for instance i want to select * from lists where publishdate > todayDate()
do i need to include todays date as a selectparameter?
can you provide example please?
View 6 Replies
View Related
Oct 4, 2004
Is it possible to format the date field create_date (mm/dd/yyyy or mm/dd/yy)
I use the following query in stored proc. will be called in the asp.net page for population the datagrid.
select id, name, create_date from actionstable;
Please help, Thank you.
View 1 Replies
View Related
Oct 15, 2015
I have the follwing function in my SQL data base which is hosted in Azure.
All date and time field in my db are store as UTC and I have a function define as below :
ALTER FUNCTION [dbo].[func_GetCurrentLocalTimeFromUTC]
(
)
RETURNS datetime
AS
BEGIN
DECLARE @OffSet as int = 1
DECLARE @CurrentDate as datetime = getUTCdate()
[Code] ...
Now what I have trying to do is to perform a select statement on a table where I have a dateTime value field and add the proper offset value to the field in order it is display based on where user will run the query. For example if I run this simple query :
SELECT INVENTORYDATE from Inventory
Will return the UTC dateTime value.
How can I use the select statement in order to get the field format with proper offset based on user pc timezone/. Please note that the query will be called from a rrs.
View 5 Replies
View Related
Nov 18, 2015
How I can get the desired result using query. I don't want any
'@' variable in the result.
DECLARE @ST AS varchar(10)='AA'
DECLARE @desc AS int=8
DECLARE @STID AS int=4
DECLARE @PP AS int=63
DECLARE @SS AS int=22
/* Desired Result */
Update #RT Set ST='AA', desc=8, STID=4 Where PP=63 and SS=22
View 9 Replies
View Related
Feb 14, 2008
hi !!!!!!!!!!!!!!!!!!!!!!!
I have 3 tables
let say Employee,Employee_temp,emp_approval
i want to update employee table by selecting columns from employee_temp
i do that using oracle but i want it using sql server 2000
Sample syntax Below: for oracle
update employee e set (col1,col2,col3,col4)=
(select t1.col1,t1.col2,t1.col3,t1.col4 from employee_temp t where t.col1=:new.col1) where e.col1=:new.col1
this syntax for oracle....
plsease provide sql 2000 syntax and sql 2005 syntax please.........
thank you.
View 6 Replies
View Related
May 6, 2015
I have a stored procedure in which I'll select some rows based on a condition and I need to update the status of those rows within the same stored procedure.For e.g.
Create Procedure [dbo].[myProcedure] As BEGIN BEGIN TRAN T1 SET NOCOUNT ON SELECT TOP 5 * INTO #TempTable FROM myTable WHERE ENABLED = 1 AND FetchDate<=GetDate(); UPDATE myTable SET [Status] = 'Locked' From myTable Inner Join on #TempTable myTable.id = #TempTable.id; SELECT * FROM #TempTable; DROP Table #TempTable; COMMIT TRAN T1 END
The Stored Procedure works fine when I debug in SQL. I'm accessing the StoredProcedure through C# like this.
private ProcessData[] ReadFromDb(string StoredProcedure, SqlConnection Connection) { List<ProcessData> Data = new List<ProcessData>(); SqlCommand Command = new SqlCommand(StoredProcedure, Connection); Command.CommandType = System.Data.CommandType.StoredProcedure; try { Command.CommandTimeout = CONNECTION_TIMEOUT;
[code]....
The problem is I'm getting the required rows in C# but the update query in stored procedure is not working.
View 3 Replies
View Related
Oct 30, 2007
I have the following table called BADSANTA:
varchar(30) datetime
NAME WHENBAD
OJ Simpson 2007-1-12 xx:xx:xx:xxx
OJ Simpson 2007-4-2 xx:xx:xx:xxx
Monica Lewinsky 2006-7-4 xx:xx:xx:xxx
Monica Lewinsky 2006-10-31 xx:xx:xx:xxx
Bill Clinton 2006-7-4 xx:xx:xx:xxx
Bill Clinton 2006-10-31 xx:xx:xx:xxx
Bart Simpson 2006-11-2 xx:xx:xx:xxx
Bart Simpson 2006-2-25 xx:xx:xx:xxx
Bart Simpson 2005-07-27 xx:xx:xx:xxx
I want the records of the persons latest WHENBAD date. It should return one recordset for each person.
Thanks in advanced.
View 4 Replies
View Related
Oct 28, 2015
I have created a table(T1) from select query result, that Select query is parameterised. Now I need to update the select query table(T1) based on the result every time.
Below is my Query:
ALTER PROCEDURE [dbo].[RPT_Cost_copy]
SELECT MEII.*, SIMM.U_SP2DC, UPPER(SIMM.U_C3C2) AS GRP3,sb.cost, PREV.Z1, PREV.Z3, SB.Z2, SB.Z4,SIMM.U_C3DC1 AS FAM
INTO T1
FROM
(SELECT a.meu, a.mep2, SUM(a.mest) as excst
FROM mei as A WHERE a.myar=@yr and a.mprd=@mth AND LTRIM(A.MCU) <> '' AND LTRIM(A.MRP2) <> ''
[code]....
View 2 Replies
View Related
Aug 27, 2014
I have an Orders table which has the following fields:
OrderID (PK, int, auto increment, not null)
CustomerID (FK, int, null)
PaymentDate (datetime, null)
UserID (uniqueidentifier)
(and other irrelevant fields)
Basically, for a specific PaymentDate range (29th July 2014 - 26th August 2014, inclusive) I want to select all orders where they only appear once in the orders table based on the CustomerID, so I only want to know about them if they have a paid order (decided by PaymentDate not being null) in that date range, but also taking into account if they have ever had a paid order outside of that date range. I'll also be joining on to the aspnet_Users table to get the username assigned to that order.
View 5 Replies
View Related
Sep 6, 2015
I have a table like the following (with much more data, but the concept is the same) with Dates and Actions for People and a column called Action with beginning Dates and end dates.
(I attached a picture because I could not figure out how to Format it)
begin Date end Date Name
begin 2014-10-15 end 2014-10-31 phil
begin 2014-09-18 end 2014-09-30 phil
begin 2014-08-21 end 2014-08-23 John
I need the query to be like this. The idea is to have the query grab the next 'END' not all Ends, which my attempts have done i.e. I get not just the closest end to the begin date, but ALL Ends with the same Person.
I Need it to look like this:
begin Date end Date Name
begin 2014-10-15 end 2014-10-31 phil
begin 2014-09-18 end 2014-09-30 phil
begin 2014-08-21 end 2014-08-23 John
There can be different People so the query Needs to return the beginning and end rows for the Person in sequential order.I can't figure out how to select only the 'next' end. My query always gets 'end' values that have a 'begin'. I
View 8 Replies
View Related
Sep 2, 2015
In my SELECT query I have: MIN(a.orderdue) AS 'Oldest order date'
This works in that it brings through the oldest order date, however it brings through a date format like: 2015-06-11 11:30.000
So I amended the SELECT query to:
MIN (CONVERT(varchar(17),a.orderdue,103)) AS 'Oldest order date'
This brings the date through as 11/06/2015, which is preferable.
But I have noticed that doing this has affected the output: the MIN function no longer returns the first (oldest) date, but a completely different value.
Obviously my changing the formatting for the date has affected the MIN output. Is there any way I can amend the formatting of the date without this happening?
View 3 Replies
View Related
Aug 18, 2014
I have date field in table and data contain 2014-08-09 11:13:03.340
when I use smalldatefield I am getting 2014-08-09 11:13:00 I should have got 2014-08-09 11:13:03
Why the 03 is trimming in smalldatefield.
how do I use select query to get 2014-08-09 11:13:03 from original Date records
View 1 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
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
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
Feb 18, 2006
I do realize that his could be posted in a few spots but I think the answer is in the SQL.
I have a ASP.NET page, with a SqlDataSource, Text Box and Calendar Controls. I have the textbox and calendar controls eval'ed to the same sql data source DateTime Field. The text box is formatted eval to small time and the calendars eval has no formatting.
ex:
<asp:TextBox ID="START_TIME" Text='<%# Eval("EVENT_START","{0:t}") %>' runat=server Width=200></asp:TextBox>
I want to merge the two controls; one has the date the other has the time when I update the pages data to the SqlDataSource field EVENT_START. I've tried a couple of methods, but I would like some other opinions. As Sql server only supports date and time together I am storing the two together.
I could merge the two together in the code behind on the update button's event handler or merge the two during the update query using parameters.
Not that I could get an illegal date for the calendar control, but I could get garbage from the textfield time. So I still would have to do validation on the text field before the SQL server could do the update.
There's a few ways to go about this, so I was wondering if anyone else has figured out an elegant way to handle it.
wbochar
View 1 Replies
View Related
Mar 10, 2008
I 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?
View 4 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 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
Oct 21, 2013
Aim – Convert the following field ”[INSTALLATION_DATE]” date format from “20090709” Into this “2009-07-09” ,
Also create a new column called “BegMonth” which selects first day of the given month of the converted date column
The table is ;
SELECT
[FDMSAccountNo],
[INSTALLATION_DATE]
FROM [FDMS].[dbo].[stg_LMPAB501]
Results
FDMSAccountNoINSTALLATION_DATE
87800000088420030521
Required Results
FDMSAccountNoINSTALLATION_DATEBegMonth
8780000008842003-05-212003-05-01
View 3 Replies
View Related
Feb 15, 2008
At the moment i have a query that is dependent on a date which is 42 days before whatever the date may be today.
The statement in my query I have to use is:
MailDate <= '2008-01-05'
I am wondering if i could make that statement automatically take off 42 days from todays date?
View 8 Replies
View Related
Feb 8, 2008
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?
View 9 Replies
View Related
