Getting Last (newest) One Record (datetime Column Or Id)
Jul 23, 2005
Hello everybody,
-------------------------------------------------
CREATE TABLE [T1] (
[IDX] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[DateEvt] [datetime] NOT NULL,
[Value] [varchar] (10) NOT NULL ,
[DataX] [varchar] (10) NULL ,
CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED
(
[IDX]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:00',
'0000000001', 'AAAAAAAAAA')
insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:01',
'0000000002', 'AAAAAAAAAA')
insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:00:02',
'0000000003', 'AAAAAAAAAA')
insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:01:00',
'0000000001', 'BBBBBBBBBB')
insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:02:00',
'0000000001', 'CCCCCCCCCC')
insert into T1 (DateEvt,Value, DataX) values('2004.10.10 10:03:00',
'0000000001', 'DDDDDDDDDD')
GO
-------------------------------------------------
and the question is:
In which fastes and best for the preformance way, get the last IDX of
specified Value.
I could do this like this:
-------------------------------------------------
declare @nIDX numeric
declare @sValue varchar(10)
select top 1 @nIDX = IDX from T1
where Value = @sValue
order by DateEVT desc
-------------------------------------------------
But I know, this is not fast (even if I have index on DateEVT field),
and I'm quite sure, that there is better way to get this IDX.
Anyway, this table can be big (like 20 milions records).
I could take the max of IDX, but is it a sure way?
Any help? Thanks in advance
Matik
View 1 Replies
ADVERTISEMENT
Feb 7, 2008
Hey all,
I just discovered this cool forum. I am fairly new to T-SQL, so please bear with me.
Here is my problem. I am trying to return 1 record for each distinct MachineName, based on the most current CreateTime.
I have tried a bunch of different things, but the follwing query seems to get me close, except for all of the records, per MachineName, that aren't the newest CreateTime. Can anyone offer a suggestion I may be missing?
Here is my query:
SELECT
rcv.Name AS MachineName, r.CreateTime, rpv.Path + rpv.Name AS ResourcePool
FROM
dbo.Result_View AS r WITH (NOLOCK) INNER JOIN
dbo.ResourceConfiguration_View AS rcv WITH (NOLOCK) ON r.ResourceConfigurationId = rcv.Id INNER JOIN
dbo.Resource_View AS rv WITH (NOLOCK) ON rcv.ResourceId = rv.Id INNER JOIN
dbo.ResourcePool_View AS rpv WITH (NOLOCK) ON rv.ResourcePoolId = rpv.Id
WHERE
(rpv.Name NOT LIKE 'Archive')
AND
(r.CreateTime > '2/1/2008')
AND
(r.CreateTime = (SELECT TOP (1) r.CreateTime FROM dbo.Result_View))
ORDER BY MachineName
I have added the '2/1/2008' filter, so as not to hammer the DB too hard. It goes back for some time.
Thanks in advance,
Allen
View 3 Replies
View Related
Jun 10, 2005
Hi!I want to do a query against a SQL DB and by sorting a datetime field, I want to get the second newest record in the table, not the newest.Can I do that?/Johan Ch
View 1 Replies
View Related
Apr 5, 2007
I got some issues regarding selecting the most recent record from a datetime type column.
The probelm consists in following:
Table: News
Needed columns: Letter [varchar]
DateTimePublication [datetime]
Table News: Letter DateTimePublication
FR1_dd_mm_yy dd.mm.yy hh:mm:ss
FR1_dd_mm_yy dd.mm.yy hh+1h:mm:ss
FR1_dd_mm_yy dd.mm.yy hh-2h:mm:ss
EN1_dd_mm_yy dd.mm.yy hh+3h:mm:ss
EN1_dd_mm_yy dd.mm.yy hh+4h:mm:ss
EN1_dd_mm_yy dd.mm.yy hh-5h:mm:ss
DU1_dd_mm_yy dd.mm.yy hh:mm:ss
DU1_dd_mm_yy dd.mm.yy hh+1h:mm:ss
DU1_dd_mm_yy dd.mm.yy hh-2h:mm:ss
FR2_dd_mm_yy dd.mm.yy hh:mm:ss
FR2_dd_mm_yy dd.mm.yy hh+1h:mm:ss
FR3_dd_mm_yy dd.mm.yy hh-2h:mm:ss
EN2_dd_mm_yy dd.mm.yy hh+3h:mm:ss
EN3_dd_mm_yy dd.mm.yy hh+4h:mm:ss
EN3_dd_mm_yy dd.mm.yy hh-5h:mm:ss
DU1_dd_mm_yy dd.mm.yy hh:mm:ss
DU3_dd_mm_yy dd.mm.yy hh+1h:mm:ss
DU4_dd_mm_yy dd.mm.yy hh-2h:mm:ss
The ideea is that there are letters with the same name [Letter] but different DateTimePublication ... and I need only the letters with the most recent DateTimePublication for every letter type like FR_dd_mm_yy/EN_dd_mm_yy/DU_dd_mm_yy.
Thank you for your time and consideration!
SenneK
View 4 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
Jul 9, 2007
Hi,
I'm inserting a datetime values into sql server 2000 from c#
SQL server table details
Table nameate_test
columnname datatype
No int
date_t DateTime
C# coding
SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI");
connectionToDatabase.Open();
DataTable dt1 = new DataTable();
dt1.Columns.Add("no",typeof(System.Int16));
dt1.Columns.Add("date_t", typeof(System.DateTime));
DataRow dr = dt1.NewRow();
dr["no"] = 1;
dr["date_t"] = DateTime.Now;
dt1.Rows.Add(dr);
for(int i=0;i<dt1.Rows.Count;i++)
{
string str=dt1.Rows["no"].ToString();
DateTime dt=(DateTime)dt1.Rows["date_t"];
string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')";
SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase);
cmd.ExecuteNonQuery();
MessageBox.Show("saved");
}
When I run the above code, data is inserted into the table
The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column.
Is there any conversion needed for millisecond values?
thanks,
Mani
View 3 Replies
View Related
Sep 1, 2006
Hi
I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Thanks.
View 6 Replies
View Related
Mar 18, 2008
Hello. I have a database with a record that has two columns locked. descrip1 and descrip2. they are both nvarchar(max) columns. These are the only two columns of the record that remain locked. I am certain no user is accessing the record. I have even moved a backup of the database to my testing computer and the lock still exists. How do I remove the lock from these two columns on that particular record.
I can edit these two columns on other records. I have researched "Unlock" on MSDN but it doesn't seem to apply to t-sql. Any help would be greatly appreciated.
Thanks. Gary.
View 4 Replies
View Related
May 11, 2001
Is there any way to determine the date and time a particular record in a table was last modified?
View 2 Replies
View Related
Apr 16, 2007
I have a datetime field in a table want to use to record the date time last the row/field of the row is modified.
Can I do this in SQL Server 2005 without writing any code/trigger?
Thanks
J.
View 2 Replies
View Related
Feb 9, 2007
Hello all,Quick sql syntax question:I have this table:if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[REQUESTS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[REQUESTS]GOCREATE TABLE [dbo].[REQUESTS] ([ROW_ID] [uniqueidentifier] NULL ,[REQUEST_DATE] [datetime] NULL ,[STATUS] [tinyint] NULL) ON [PRIMARY]GOwith these values:insert into REQUESTS (REQUEST_DATE, STATUS)values (getdate(), 0)insert into REQUESTS (REQUEST_DATE, STATUS)values (getdate(), 1)insert into REQUESTS (REQUEST_DATE, STATUS)values (getdate(), 0)I need to select the single record with a STATUS = 0 with the earliestREQUEST_DATEI am using this query:SELECT TOP 1 ROW_ID FROM REQUEST_LOG WHERE STATUS = 0 ORDER BYREQUEST_DATEnot sure if this is the way to go...pointer appreciatedthanks
View 3 Replies
View Related
Oct 1, 2007
Hi,
Can anyone please let me know the query to get the latest record based on datetime cloumn?
For example, a table with three records:
Name | Application | Start Time
User A | Outlook | 10/01/2007 1:30 AM
User A | Internet Explorer | 10/02/2007 12:30 AM
User B | Internet Explorer | 9/20/2007 8:30 PM
The query gets the below:
User A | Outlook | 10/01/2007 1:30 AM
User B | Internet Explorer | 9/20/2007 8:30 PM
Thanks in advance.
View 1 Replies
View Related
Sep 24, 2007
Hi People,
hope someone can help me out here with a little problem.
Basically i've go a asp.net page which has a listbox on. This list box is populated from a SQL database table with the datetime of the a selected field. Thus in the list box you get a list of strings looking like this "24/09/07 12:58"
Also on the page is a submit button, and some other editing textboxes. The main issue here is the when the submit button is used i get the currently selected listbox timedate string and then pass this along with other items to update a record in the database based on the datetime in the listbox control.
Below is how i get the string from the listbox control
Dim except_time As DateTime
except_time = DropDownList1.SelectedValue
The expect_time is then passed to store procedure along with some other vars, which looks like this
-- =============================================-- Author: Lee Trueman-- Create date: 1st Sept 2007-- =============================================CREATE PROCEDURE [dbo].[spExcept_UpdateData] -- Add the parameters for the stored procedure here @validated bit, @update_time datetime, @except_time datetimeASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON
-- Insert statements for procedure here UPDATE exceptions SET validated = @validated, update_time = @update_time WHERE (except_time = @except_time)END
So validated and update_time should be updated when except_time is equal to @except_time
My problem is that the database never updates. If i debug the asp.net page the watch var shows the datetime in US format (I.e "09/24/07 12:58"), if this is true then this would explain why when it is passed to the stored proc nothing gets updated, as there would not be a date match.
can anyone see any silly mistakes i'm doing here ????
View 2 Replies
View Related
Jan 17, 2006
I am using SQL Express database and where can I set the default value to my Data field to auto insert the current date-time value? I tried to find the setting but can't seem to find it. Any help is much appriciated. Thanks in advance.
View 5 Replies
View Related
Jan 14, 2015
My goal is to update the "PriorInsert" field with the "DateInserted" from the previously inserted record where the WorkOrder, MachineNo, and Operator are all in the same group.
While trying to get to the correct previous record, I wrote the query below.
P.S. The attached .txt file includes a create and insert tbl_tmp sampling.
select top 1
a.ID,
a.WorkOrder,
a.MachineNo,
a.Operator,
a.PriorInsert,
[code]...
View 2 Replies
View Related
Apr 2, 2015
So I have a query that need to find the most recent datetime record each day for a customer. So I have a query that looks like this:
SELECT
dhi.[GUID],
dhi.[timestamp],
la.[bundle_id],
dhi.[value]
FROM
[dbo].[DecisionHistoryItem] as dhi WITH(NOLOCK)
[Code] ....
View 4 Replies
View Related
Feb 6, 2004
Hi everyone -
I am building from the Time Tracker Start Kit, trying to get a better feel for how MS thinks we should do things.
In my editing, I have built a new Stored Procedure, trying to pull the newest entries for a specified person.
I have the following that will return all entries for a specific person, sorted by date, newest first:
SELECT
EntryLogID, TT_EntryLog.Description, Duration, EntryDate, TT_EntryLog.ProjectID AS ProjectID,
TT_EntryLog.CategoryID AS CategoryID, TT_Categories.Abbreviation AS CategoryName, TT_Projects.Name AS ProjectName,
ManagerUserID, TT_Categories.Abbreviation AS CatShortName
FROM
TT_EntryLog
INNER JOIN
TT_Categories
ON
TT_EntryLog.CategoryID = TT_Categories.CategoryID
INNER JOIN
TT_Projects
ON
TT_EntryLog.ProjectID = TT_Projects.ProjectID
WHERE
UserID = @UserID
ORDER BY
EntryDate Desc
This will return something like:
EntryLogId Description Duration EntryDate ProjectID CategoryID CategoryName ProjectName ManagerUserID CatShortName
14Can type date in date... .00 2004-02-04 10:28:00116Pros ITS Project Management App 3 Pros
12Changed "Entry Date"... .00 2004-02-03 13:28:00116Pros ITS Project Management App 3 Pros
13Added default button ... .00 2004-02-03 00:00:00116Pros ITS Project Management App 3 Pros
11Removed hours per p... .00 2004-02-03 00:00:00116Pros ITS Project Management App 3 Pros
6Isn't this cool .00 2004-02-02 00:00:00229Pros Knowledge Base 3 Pros
9Added week-by-week... .00 2004-02-02 00:00:00116Pros ITS Project Management App 3 Pros
10Fixed Update comma... .00 2004-02-02 00:00:00116Pros ITS Project Management App 3 Pros
1Built initial framewor... 6.00 2004-01-30 00:00:00116Pros ITS Project Management App 3 Pros
5Adding up to 8 hours... 2.00 2004-01-30 00:00:00229Pros Knowledge Base 3 Pros
3Debugged - fixed a f... 1.00 2004-01-23 00:00:00229Pros Knowledge Base 3 Pros
What I would like to accomplish is to return only the newest entry for each ProjectID (so in the above example, there would only be 2 entries, EntryID 14 and 6)
Any ideas?
Thanks in advance-
View 4 Replies
View Related
Feb 22, 2006
How can I select from a table the newest entry.I'm inserting in a table user info and then want to get the users id number
View 9 Replies
View Related
Jun 30, 2006
I have a query set up that returns the data that I would like, but Iwould only like the latest data for each vehicle number. The query Ihave set up isSELECT TOP 100 PERCENT dbo.vwEvents.EventName,dbo.luSessionAll.SessionName, dbo.luOuting.OutingNumber,dbo.luVehicle.VehicleName, dbo.luOuting.OutingID,dbo.tblOutings.OutingStartTime,dbo.tblSessions.Ses sionDate,dbo.tblSessions.SessionStartTimeFROM dbo.vwSessions INNER JOIN dbo.vwEvents ONdbo.vwSessions.Event = dbo.vwEvents.EventIDINNER JOINdbo.luSessionAll ON dbo.vwEvents.EventID =dbo.luSessionAll.Event INNER JOINdbo.luOuting ON dbo.luSessionAll.SessionID =dbo.luOuting.SessionID INNER JOINdbo.luVehicle ON dbo.luSessionAll.Vehicle =dbo.luVehicle.VehicleID INNER JOINdbo.tblOutings ON dbo.luOuting.OutingID =dbo.tblOutings.OutingID INNER JOINdbo.tblSessions ON dbo.tblOutings.[Session] =dbo.tblSessions.SessionIDGROUP BY dbo.vwEvents.EventName, dbo.luSessionAll.SessionName,dbo.luOuting.OutingNumber, dbo.luVehicle.VehicleName,dbo.luOuting.OutingID, dbo.tblOutings.OutingStartTime,dbo.tblSessions.SessionStartTime, dbo.tblSessions.SessionDateORDER BY dbo.luVehicle.VehicleName, dbo.tblSessions.SessionDate,dbo.tblSessions.SessionStartTime, dbo.tblOutings.OutingStartTimethis returns all the outings. I would like the outing that has, inorder of importance, the latest session date, latest session time andlatest outing start time. Outing start time can sometimes be <<Null>>but the other two always have values. How would I go about doing this?thanks in advance for any help
View 2 Replies
View Related
Sep 18, 2006
Hi,Can anyone help please?select notefield, modifiedonFROM Table1WHERE id = '100426' and(statusfield like '%criteria1%' ORstatusfield like '%criteria2%')Produces a list of records based upon the criteria. I would like to beable to only show the newest dated record, from the modifiedon field.I've tried max(modified) on, but as I am using an aggregate function inthe query I have to use GROUP BY, which notefield does not like as thisis a ntext field.(I get:Server: Msg 306, Level 16, State 2, Line 1The text, ntext, and image data types cannot be compared or sorted,except when using IS NULL or LIKE operator.)Any ideas please?
View 1 Replies
View Related
Jul 10, 2015
I have just started to use Integration Services Catalog with SQL Server Agent. After I deploy a new version of the project the any Server Agent job that uses any of dstx still uses the old one. How do I fix this?
View 1 Replies
View Related
Nov 20, 2014
I dont know how to get the newest input for each user from one single table.
Should be a very simple task but i cant work it out.
The table looks like this:
ID (A_I), userID, ip, date(timestamp)
Here is a SQL Fiddle Link with some data also: [URL] ....
I have tried a lot querys like this one:
SELECT userID, ip FROM userips GROUP BY userID ORDER BY ID DESC
But this one does not give me the latest ip which was entered by a user.
View 2 Replies
View Related
Oct 9, 2006
i have the following table
Project
Name
Date
1
Dennis
01-01-2004
1
Peter
03-03-2005
2
Henry
01-02-2003
2
Lisa
04-03-2003
2
Peter
05-05-2005
2
Simon
05-06-2005
2
Lisa
12-12-2005
3
Henry
02-02-2004
4
Peter
03-04-2003
5
Joan
12-10-2005
For each project i would like to have the latest responsible person, so that i get a list like this
Project
Name
Date
1
Peter
03-03-2005
2
Lisa
12-12-2005
3
Henry
02-02-2004
4
Peter
03-04-2003
5
Joan
12-10-2005
Can someone please tell me how to do that in one query ?
View 4 Replies
View Related
Oct 5, 2007
I have the following Table:DataIDuserIDTimeStampI need to create a query that selects the "newest" records for all distinct users. I'm hoping to get this done in one query. I know I could write multiple queries to find all distinct users, and then the newest records based on that... and so on... but I'm not sure that's the most efficient. I have the following so far, but is it the most efficient (I have indexing on most of my columns)? SELECT DATA.DataID, DATA.UserID, UN.vchName, UN.vchImage, U.vchFirstName + ' ' + U.vchLastName AS 'FullName' FROM [tblData] DATA INNER JOIN [tblUnits] UN ON DATA.UserID = Un.UserID INNER JOIN [tblusers] U ON U.UserID = UN.UserID WHERE GPS.intGPSDataID IN (SELECT MAX([tbLData].DataID) FROM [tbLData] GROUP BY [tbLData].UserID)
View 4 Replies
View Related
Feb 11, 2004
Ok here's a big one.
First I'm a big NEWBIE, so it'd be great if you can provide a little explanation as to how this should be done...
Here's what I want to do, but have no idea how to approach it.
I have a table with Quotes (table Quote) in them (for a Sale's Team). Each has a quote number (qtQN) and this number is sequencial but sometimes revised where a letter is added at the end. Like so:
qtQN__________Date
---------------------------
111q0001--------02/01/04
111q0002--------02/02/04
111q0002A------02/03/04
111q0002B -----02/04/04
222q0005--------01/15/04
etc... ------------- etc...
The first 3 digit are company codes and pretty unimportant to this problem. As you can see, 111q0002 has three versions. A, B, and no letter. The most up to date is B. So in this list I want to list only the most up to date quotes. So the resulting list would be:
111q0001
111q0002B
222q0005
Get it? Good, cuz I have no idea how to query that... any help at all is appreciated!
I'm using MS SQL Server 2k and scripting with ASP 3.0
View 6 Replies
View Related
Mar 24, 2006
Hi,I'm working on a simple performance-program, where I need to extractinformation from the 2 newest periods for every performance-indicator- And from there calculate a trend between these results.The problem is, that I can't find a simple way to extract the 2 latestresults.The Table (Table1) looks like this:kpiIDperiodIDActualAcceleration23Acceleration54Speed1100Speed4200Speed7220Speed9180Weight122Weight332Weight721Weight1033If I want to extract the newest I use something like this (made it inMS Access, so the syntax might differ slightly from SQLServer):SELECT table1.kpiID, table1.periodID, table1.ActualFROM table1 WHERE table1.periodID = (SELECT max(t.periodID) fromtable1 as t WHERE t.kpiID=table1.kpiID);BUT - how how do I get the second-newest period as well?Preferably I would like the final result to be a View with thefollowing fields:kpiID, periodID_newest, Actual_newest, periodID_sec_newest,Actual_sec_newestAlternatively a View with 2 posts for each performace-indicator.Thanks in advanceRyan
View 10 Replies
View Related
Jul 6, 2014
I am trying to tweak some code which is used to display the newest comments left on photos created by my members.
The existing code is this:
SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESC
So the latest comment left was for photo #210879 from user "Cla" (redacted user names). The 2nd newest comment would be for photo #211072 from a member named "mo". pdate is a date field
However for the script I have coded I don't want all of the photo comments to show up. This is because I use access levels based on the type of location (higher levels mean more restricted galleries). I check the access levels as I go through the recordsets.
I use this method to get the top 15 comments:
SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESC
Now I have to use two other tables to determine the access level. Since PHOTOCOMMENTS is just a list of photo #'s and the people who left comments for those photos, I need to:
a) determine what location the photo is from and
b) determine the access level of that location
I use: select creator,access from locations where id=(select dir from photos where id="&pnumber&")"
This is a two step process as you can see. The first part is:
select dir from photos where id=(pnumber)
ID is the same value as pnumber seen in PHOTOCOMMENTS. That is to say PHOTOS.ID = PHOTOCOMMENTS.PNUMBER
If I haven't confused you yet, the executed code for the first example would be:
select dir from photos where id=210879
which would get me a value for DIR. DIR is the location number which would be:
select creator,access from locations where id=(dir value)
Just to simplify it a bit....
There are three tables (shown below)
PHOTOCOMMENTS
PHOTOS
LOCATIONS
I need to: SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESC (first table shown)
but then also
select creator,access from locations (The last table shown)
where id=(select dir from photos where id="&pnumber&")"
So the first table PHOTOCOMMENTS has to also join PHOTOS table where PHOTOS.DIR = PHOTOCOMMENTS.PNUMBER in order to get the value of "DIR" and then DIR is joined to the LOCATIONS tables where PHOTOS.DIR = LOCATIONS.ID
Here is the actual code, which I am trying to make into a single SQL command
strSQL = "SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESC"
set ors = oconn.Execute(strSQL)
tl = 0
do until ors.eof or tl > 15
' until we have 15 results because not every recordset will be of the proper security level
[Code] ....
Bonus points if you can also get it to select from LOCATIONS only WHERE userlevel >= 2
View 4 Replies
View Related
Jan 22, 2008
I have a column which Data Type is DateTime the value is looks like 2008-01-18 16:40:39.560if I want to group by this column only compare with yyyy/mm/dd , don't care the TimeHow to write a Sql Query to fit this request?the values in Columns like below2008-01-18 16:40:39.5602008-01-18 16:40:39.5602008-01-18 16:40:39.5602008-01-18 16:40:39.5602008-01-18 17:10:02.8572008-01-18 17:10:37.3732008-01-18 17:10:37.3732008-01-18 17:11:57.1872008-01-18 17:12:22.8102008-01-18 17:13:01.4502008-01-18 17:13:37.1532008-01-18 17:13:52.4502008-01-18 17:14:10.6402008-01-18 17:14:24.6532008-01-18 17:14:41.1532008-01-18 17:15:09.3572008-01-21 14:55:18.560 thank you for your posting .
View 3 Replies
View Related
Jul 16, 2005
Hi All,I faced this strange problem with sql server. I have datetime column and storing value from asp.net. If the user doesnt enter any date, then we dont want any value to be stored in the database. But when we checked SQL Server, it default takes this value "01/01/1900". When users clicks on edit button, this value is fetched from database and stored in front end. I dont want this value to be stored in database if i didnt provide value. But in 1 particular page, there are 5 different date columns. I am not sure about how to go ahead. I tried the following way, but again ended up with same problem.insert into tablename.......values (...., txtFromDate.text, txtToDate.text,....). Since value is not there, '' is sent to database and above said value is stored. i want NULL to be stored in teh database when user didnt specify any value.i didnt encounter this problem in Oracle.appreciate your reply.
View 9 Replies
View Related
Dec 27, 2005
hi, i was wondering how to set default value in the datetime column of the database so that it will enter current date and time if one is not provided when a row is populated. is there a store procedure to do this? or built-in function?
mp
View 3 Replies
View Related
Jun 18, 2001
Hi Guys,
I have to take only time from DateTime column filed value.
Is there any function in SQL Server which will give us time only.
Thanks,
Rau
View 2 Replies
View Related
Jul 21, 2000
I've got have a populated table and I want to convert a datetime column so it lists the date only (without the time component)
I tried to run this as a script, but returns an error:
update <table>
where <column>=convert(datetime,convert(char10),<column>,101))
When I run only this part, it does strip the date of the time component but it becomes a string, and I need this field stored as a datetime field:
convert(char(10),hire_date,101)
I'd appreciate any suggestions :)
View 1 Replies
View Related
Apr 20, 2005
I'm trying to update a datetime column from another datetime column. However, I just want the date transferred to the new column without the time. Any ideas? Thanks for your help.
View 6 Replies
View Related