Updating Date In Query
Jan 9, 2012
I am running a query that returns transactions that were done at a given point.I am using the"connections" part of Excel.I have created a workbook connection
Connection type : OLE DB Quert
Command Type: SQL
Command Text:
Select
t.SP_ID AS Salesperson
,sum(CASE t.Transaction_Date WHEN '01/05/2012' THEN (abs(t.Transaction_Units) * t.Transaction_Price
etc
It runs finds and returns my data into a table in Excel.However, I need to manually update the relevant date in the Command Text.Is there a way that the connection automatically updates the date. as indicated in a cell in Excel, or maybe t-1 given that the file updates automatically every time I open?
View 2 Replies
ADVERTISEMENT
Jun 18, 2007
Brand new to this, so please bear with me.I'm using the following code fragment to update a datetime field on a SQL Server 2005 database table:cmd.CommandText = "Update Projects Set EntryDate = " & Convert.ToDateTime(txtEntryDate.Text)cmd.ExecuteNonQuery()The result of the update operation is the the database field contains the value "1900-01-01 00:00:00:000". This probably means that I passed nulls to SQL; however, I see a valid date in the txtEntryDate field on my web form (i.e., "06/18/2007"). I also did a "Response.write" to display the txtEntryDate and it looks Okay.Can someone tell me what I'm doing wrong?Thanks!Using Visual Web Developer 2005 Express.
View 1 Replies
View Related
Jul 2, 2007
Hi,
I have around 8 date columns in a table I want to update the columns
for eg initialization date should be greater than incident date .but the thing is it should be just one day or upto 4 days greater .
Right now all the tables have random dates ..I tried dateadd i also tried datediff nonis helping please can u help
sambhav jain
View 14 Replies
View Related
Aug 7, 2005
All other fields are updating ok and I'm not getting an error.I am trying to update a date and time (smalldatetime) using a stored procedure.First, the info to be updated comes from a datagrid.
Dim sDate As DateTime
sDate = CType(e.Item.FindControl("tDate"), TextBox).TextThen, passed to the SQLDal class and then to the stored procedure.....
Public Function updateData(ByVal sDate As DateTime, ByVal sp As String) 'Some items snipped for easier read
Dim command As SqlCommand = New SqlCommand(sp, conn) 'Where sp is the stored procdure name
command.Parameters.Add("@date", sDate)'And so on.....And then the stored procedure....@num VARCHAR(256),@date SMALLDATETIME,@contact VARCHAR(256),@notes VARCHAR(8000),@media VARCHAR(256) ASBEGIN DECLARE @errCode INT
BEGIN TRAN
-- UPDATE THE RECORDS UPDATE dbo.tblData SET fldDate = @date, fldContact = @contact, fldNotes = @notes, fldMedia = @media WHERE fldNum = @num
<sniped>Like I said, all other fields are updated with no problems, but not the date.The date format being passed into the sp is {0:MMM dd, yyyy hh:mm tt} or Aug 05, 2005 04:39 PM Is it the format of the date? Or something else I'm not seeing...Thanks all,Zath
View 4 Replies
View Related
Mar 29, 2006
Hello,
My date format is set to smalldatetime like: yyyy/mm/dd
I need to convert all date formats on this collumn to dd/mm/yyyy
How could I do this?
View 2 Replies
View Related
Jan 31, 2008
Hi - Once again I've been looking at this forever and not able to see the problem. Have a grid table everything updates except the training date field. That get's wiped out each time - no matter if something is in it or not. Everything else updates correctly.
Here's the code:
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="ds1" DataKeyNames="eventID"><Columns><asp:CommandField ButtonType="Button" ShowEditButton="True" ShowDeleteButton="True" /><asp:BoundField DataField="eventID" HeaderText="ID" SortExpression="eventID" ReadOnly="True"><HeaderStyle Font-Bold="True" Font-Names="Verdana" Font-Size="Small" /><ItemStyle Font-Names="Verdana" Font-Size="Small" /></asp:BoundField><asp:TemplateField HeaderText="Training Date" SortExpression="trainingDate"><EditItemTemplate><asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("trainingDate", "{0:M/dd/yy}") %>'></asp:TextBox></EditItemTemplate><ItemTemplate><asp:Label ID="Label1" runat="server" Text='<%# Bind("trainingDate", "{0:M/dd/yy}") %>'></asp:Label></ItemTemplate><HeaderStyle Font-Bold="True" Font-Names="Verdana" Font-Size="Small" /><ItemStyle Font-Names="Verdana" Font-Size="Small" /></asp:TemplateField>
<asp:SqlDataSource ID="ds1" runat="server" ConnectionString="<%$ ConnectionStrings:TrainingClassTrackingConnectionString %>" ProviderName="<%$ ConnectionStrings:TrainingClassTrackingConnectionString.ProviderName %>"UpdateCommand="UPDATE [trainingLog] SET [trainingDate] = @trainingDate WHERE [eventID] = ?" >
<UpdateParameters><asp:Parameter Name="trainingDate" Type="DateTime" /></UpdateParameters>
View 2 Replies
View Related
May 13, 2008
If your tables contain created and modified/updated dates what is the best practice for these?
1. Should you use UTC dates?
2. Do you use a default for the creation date (I assume yes)?
3. Should you create a trigger to handle the last update date? Or do you update the column directly in your stored procedures that modify data?
Also, as an aside if you store the user who created/updated the record do you store a foreign key reference to the user table or do you store the username as a varchar? Of course I know you'd normally store the fk, but I wasn't sure if the "logging" nature of the column suggests storing a string value.
View 2 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
Aug 19, 2003
Can I update a select query using sql server 2000? What I mean is, I want to use a query instead of table or a view.
eg update (select * from tablename) set field='value'
something of this sort. I tried doing some r&d but it didn't work. I want it as single query. If some one knows it please let me know. It is very urgent.
Regards,
Samir.
View 1 Replies
View Related
Jul 20, 2005
First of all I don't normally use SQL, so please excuse my ignorance.1st QueryWe have a table with a column with data that looks like this:AMTL03256636What I need to do is change the AMTL portion of the column to another set ofcharachters i.e TESTCurrently i'm extracting the data, loading it into excel changing the dataand then bulk inserting it backinto the SQL7 table. Is there away to change the AMTL without the othersteps?Alan
View 3 Replies
View Related
Jul 20, 2005
First of all I don't normally use SQL, so please excuse my ignoranceThe following query Selects data based on another table.SELECT A.DSTRCT_CODE, A.WORK_ORDER, A.WO_TASK_NO, A.WO_TASK_DESC,A.TASK_STATUS, A.WORK_GROUP, A.CREW,A.WORK_CENTRE, A.ASSIGN_PERSON,A.JOB_DESC_CODE, A.SAFETY_INSTR, A.COMPLETE_INSTR, A.COMPLETED_BY,B.DSTRCT_CODE, B.WORK_ORDERFROM MSF623 A ,MSF620 BWHERE B.DSTRCT_CODE = 'TEST'AND A.DSTRCT_CODE = 'AMTL'AND A.WORK_ORDER = B.WORK_ORDERWhat I need to do is update A.DSTRCT_CODE column to read 'TEST'.Currently I take the data selected and dump it into excel, change theDSTRCT_CODE field and then upload load using a BULK INSERT statement.Is it possible to insert into the query an update statement? Is thereany special sytax that I should use.Alan
View 3 Replies
View Related
Nov 21, 2006
I'm using SQL Server 2005.
Is it possible to update two tables in a single update query by comparing rows in two tables?
Something like this:
UPDATE h
SET
CheckAmount =c.[amount1],
c.Updated = 'YES'
FROM tblCheckNumber as c
INNER JOIN tblHistory as h
ON c.Autonumber = h.AutoNumber
WHERE (h.CheckAmount Is Null Or h.CheckAmount=0)
AND h.CheckNumber Is Null
AND h.CheckDate Is Null
AND h.AccountNumber Is Null;
View 1 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
Mar 12, 2004
Hi All,
I'm trying to create a script that updates a field in a table, based on data in another table. It should be simple, but I'm doing something wrong. Here's the code:
USE DBMyDatabase
UPDATE TblToBeUpdated
SET IDField=TblOther.IDNew
WHERE IDField=TblOther.IDOld
SELECT Pk, IDField
FROM TblToBeUpdated
What am I doing wrong? The error code I get is:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'TblOther' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'TblOther' does not match with a table name or alias name used in the query.
Thanks.
Henry
View 3 Replies
View Related
Jul 23, 2005
Hi Group!I am struggling with a problem of giving a date range given the startdate.Here is my example, I would need to get all the accounts opened betweeneach month end and the first 5 days of the next month. For example, inthe table created below, I would need accounts opened between'5/31/2005' and '6/05/2005'. And my query is not working. Can anyonehelp me out? Thanks a lot!create table a(person_id int,account int,open_date smalldatetime)insert into a values(1,100001,'5/31/2005')insert into a values(1,200001,'5/31/2005')insert into a values(2,100002,'6/02/2005')insert into a values(3,100003,'6/02/2005')insert into a values(4,100004,'4/30/2004')insert into a values(4,200002,'4/30/2004')--my query--Select *[color=blue]>From a[/color]Where open_date between '5/31/2005' and ('5/31/2005'+5)
View 2 Replies
View Related
Nov 3, 2006
Can someone tell me sql query for filtering date field for current day,not last 24hours but from 00:00 to current time?
View 2 Replies
View Related
Jul 8, 2013
I have a rather complex query (to me at least) that I need to create but I am unsure of where to start. The query requires me to copy existing data into a new row (which will then create a new ID) as well as update all existing records with the newly created id. More specifically, I need to separate the data associated with LocationID 219 from it's parent, CompanyID 992.
Ideally I want to copy the data associated with LocationId 219 and then make a new CompanyId with the copied data (which will also create a new LocationID). Since this new record is no longer going to be associated with CompanyID 992 I will want to remove/delete/drop it from that record.
Finally, and perhaps most difficult of all, I need to update all tables that reference the old ID's together (992 / 219) to reflect the newly created Company ID and Location ID.
View 3 Replies
View Related
Dec 8, 2006
I want to set a column to 0 if it is set to a certain number. There are several columns to check though, so I am wondering if I can do it all in one query, or if I have to do it in single queries?
here is an example:
account | contact_id_1 | contact_id_2 | contact_id_3
433 | 67 | 23 | 67
so I want to set any contact_id_N = 0 where contact_id_N is = 67
so in the end, the table will look like this:
account | contact_id_1 | contact_id_2 | contact_id_3
433 | 0 | 23 | 0
is there a way to do it in one statement?
View 2 Replies
View Related
Apr 27, 2015
Is there any way to update multiple tables in a single query. I know we can write triggers. Apart from triggers, is there any other way available in SQL Server. I am using 2008R2.
View 8 Replies
View Related
Mar 20, 2008
Why is this SQL UPDATE query not updating when this code is used under button.click.
Dim ListingID As String = Request.QueryString("id").ToString
Dim sqlupdate As String = "UPDATE Listings SET PlaceName = '" & PlaceName.Text & "', Location = '" & Location.SelectedValue & "', PropertyType = '" & PropertyType.SelectedValue & "', Description = '" & Description.Text & "', Price = '" & Price.Text & "' WHERE ListingID ='" & ListingID & "'"
Dim con As New SqlConnection(ListingConnection)
Dim cmd As New SqlCommand(sqlupdate, con)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
View 12 Replies
View Related
Jan 24, 2008
I have to write a couple scripts that will update a couple columns in two separate tables and also insert a new row with the same data except for a few calculated or provided values ...... see specs below ...
1. tGradeHist Table Script One (Needs to be run first)
a. Read tGradeHist Table and Select rows with GradeEndDate = NULL and GradeStartDate = '1/1/2007 12:00:00 A.M.'
b. Calculate New Step Amount = StepAmount * Incr% (Round To Nearest Whole Dollar)
c. Create New Row for this table using information from row read above and insert new information where indicated :
GradeCode - Same
GradeLocationCode - Same
Step - Same
GradeStartDate - '7/1/2007 12:00:00 A.M.'
GradeEndDate - NULL
GradeCurrencyCode - Same
StepAmount - Result of b (above)
GradeFrequencyCode - Same
RangeMaximumAmount - Same
RangeMidAmount - Same
RangeMinimumAmount - Same
GradeCurrentFlag - 'True'
MarketMaximumAmount - Same
MarketMidAmount - Same
MarketMinimumAmount - Same
GradeGUID - Same
TSCOL - Same
d. Update Row read in a (above) with GradeEndDate = '6/30/2007 12:00:00 A.M.' and GradeCurrentFlag = 'False'
2. tPersonBasePayHist Table Script Two (Needs to be run second)
a. Read tPersonBasePayHist Table and Select rows with PersonBasePayEndDate = NULL
b. Calculate New PersonBasePayAmount = PersonBasePayAmount * Incr% (Round To Nearest Whole Dollar)
c. Create New Row for this table using information from row read above and insert new information where indicated :
PersonGUID - Same
PersonBasePayStartDate - '7/1/2007 12:00:00 A.M.'
PersonBasePayEndDate - NULL
PersonBasePayCurrencyCode - Same
PersonBasePayAmount - Result of b (above)
PersonBasePayFrequency - Same
PersonBasePayPayrollFrequencyCode - Same
BasePayReasonCode - 'SA'
ConductedBasePayReviewDate - Same
ScheduledBasePayReviewDate - Same
PayrollCode - Same
PersonBasePayCurrentFlag - 'True'
ApprovedByPersonGUID - Same
PersonBasePayGUID - Same
TSCol - Same
d. Update Row read in a (above) with PersonBasePayEndDate = '6/30/2007 12:00:00 A.M.' and PersonBasePayCurrentFlag = 'False'
View 7 Replies
View Related
Nov 1, 2013
I'm currently using the SQL to find records older than todays date in the SSD_SED field. I'm having to update the date manually each day. Is there a way I can automate this?
AND (SSD_SED < '2013-11-01')order by SSD_SED DESC
View 3 Replies
View Related
Jul 23, 2005
I am running a DTS Package.I have a temp table with 1494 records. I am inserting a 'Y' or'N'into a temp table #HasClaims.The TempTable name with the Provider Id's(PRPR_ID) is#TempFACETSNODupesThe @identityID is an identity field counting back from 1494 to 1I count back from the Max value of the identityid (1494) in the Whileloop until I get through all the records. The idea is to check for theexistance of a claims and authorization record and put a 'Y' or 'N'record in the temptable #HasClaims.This is running in the Execute SQL Task object of the DTS Package.The Package runs successfully but only inserts 200 rows into the newtemp table. There should be a row for each provider. Each time itruns, the number of rows it returns is different. Sometimes it is 205,then 185, then 210, before it completes the DTS package.Has anyone run into While looping problems within an Execute SQL taskin a DTS package(SQL 2000)like this--------------------------------------------------------------SELECT @identityID = MAX(IDENTITYID) FROM #TempFACETSNODupesWhile @identityID >= 1BEGIN@PRPRID is the placeholder for the PRPR_ID (Provider)SELECT @PRPRID = PRPR_ID FROM #TempFACETSNODupes WHERE IDENTITYID =@identityIDIF exists( SELECT CLCL_ID FROM dbo.CMC_CLCL_CLAIM CLCL WHERECLCL.PRPR_ID = @PRPRID)BEGININSERT INTO #HasClaims (PRPR_ID, HasClaims, IdentityID)VALUES( @PRPRID, 'Y', @identityID)ENDELSE INSERT INTO #HasClaims (PRPR_ID, HasClaims, IdentityID)VALUES( @PRPRID, 'N', @identityID)------------------------------------------------------------SELECT @identityID = @identityID - 1END
View 7 Replies
View Related
Jul 21, 2015
I have an existing old SSIS Solution that needs to be updated with MDX query. In the query I am using every thing as is but changing only the filter condition. When I try to Preview the data on data flow task, I am getting below error.
Outputs[OLE DB Source Output] references an external data type that cannot be mapped to a Data Flow task data type. The Data Flow task data type DT_WSTR will be used instead.
Package without MDX query update is running fine and no issues. I am using SQL Server 2014 and SSDT 12.0.50318.0 version for your info.
I tried updating SSAS connection string with "Format=Tabular" which was missing earlier and still didn't work.
View 2 Replies
View Related
Sep 21, 2006
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
Any suggestions?
View 7 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
Apr 7, 2008
Hi,
We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.
If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.
Thanks,
Sarah
The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.
This is running on a Websphere Application Server v6.1.
Code snippet - getting the record thru JDBC:
DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer");
Connection wsCon = wsDataSource.getConnection();
// wsCon.setAutoCommit(false); //have tried with and without this flag - same results
Statements stmt = wsCon.createStatement();
String sql = "SELECT * FROM Person where personID = 12345";
ResultSet rs = stmt.executeQuery(sql);
if(rs.next()){
System.out.println(rs.getString("lastName"));
}
if (rs != null){
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (wsCon != null) {
wsCon.close();
}
View 1 Replies
View Related
Jun 11, 2015
When updating a Power Query Source in Power Pivot, I'm getting the following error message:
Basically saying that OLE DB or ODBC-error occured when:
- Connections have been imported from a different workbook or
- the workbook has been created in a newer Version of Excel
None of which is the case here. What can cause this?
System: Virtual machine (VMware) on Windows 2008 R2, Office 2010, 64bit, using temporary profile.
View 5 Replies
View Related
Nov 16, 2006
I have the following 4 rows in a table
Company JobNumber BeginDate ModifyDate
1 2 12/12/2005 11/12/2006
1 2 12/12/2005 11/15/2006
2 3 11/12/2005 1/12/2006
2 3 11/12/2005 9/15/2006
The company and Job Number make up the key so yes this table has duplicate keys. My question is how would I return the two keys with the max modify date?
So the results would look like this:
1 2 12/12/2005 11/15/2006
2 3 11/12/2005 9/15/2006
Thanks,
View 2 Replies
View Related
Mar 9, 2007
Good Morning to all
I wrote a query to access some data from sql server ,my query is as follows
strsel = "select * from schedule where sector_id='" & str_sec_id & "' And dep_date = " & bb & ""
bb is the date transferred from the other module
i want to check the dep_date as short date format.because bb is in short date format.
in the above checking i am not getting the query result
please look on this code
Regards
Unni
View 2 Replies
View Related
Apr 10, 2007
I have a single table with the following columns:
rowid (pk)
whenloaded
tablename
records
comments
I want to write a query to get the max(whenloaded) date and then each individual related column corresponding to that date IE. tablename, records, comments
Query I have now - Select a.tablename, max(a.whenloaded) From dbo.table_load_tracking a group by a.tablename
This returns the list that I desire although I am not sure how to add the additional columns and maintain the origional listed max(whenloaded).
I would like to understand the concept behind building a query like this so a detailed explanation would be greatly appreciated.
Thank You,
Fullyii
View 1 Replies
View Related
Apr 7, 2005
I have a table with the following structure: FacilityID, DepartmentID, NumberBeds, DateCreated. I record gets added per FacilityID-DepartmentID. Not every facility and every department within the facility needs to have an entry and there can me multiple entries on the same date. I want to create a pivot table of sorts, which I think I can do, as well as the joins to get the Facility Name and Department Name from other tables.
What I really am having problems figuring out right now is how to pull the last entered date per FacilityID-DepartmentID. Here is a sample of what the data looks like in the database and what I want to pull:
Hospital1 Cardiology 1 4/6/05 4:24 PMHospital1 Critical Care 8 4/6/05 4:24 PMHospital1 Med-Surg 3 4/6/05 4:24 PMHospital1 Pediatrics 0 4/6/05 4:24 PMHospital1 Psychiatry 0 4/6/05 4:24 PMHospital1 Telemetry 0 4/6/05 4:24 PM Hospital1 Cardiology 8 4/7/05 9:04 AMHospital1 Critical Care 6 4/7/05 9:04 AMHospital1 Pediatrics 4 4/7/05 9:04 AMHospital1 Psychiatry 0 4/7/05 9:04 AM
I need to have as output (items in red above):Hospital Card CritCare Med-Surg Peds Psych Telem Last UpdatedHospital1 8 6 - 4 0 - 4/7/05 9:04 AM
I'm truly stumped on this one and any help, examples or links to documentation would be greatly appreciated!
View 1 Replies
View Related
Sep 19, 2001
Hi All
Could someone please give me some guidance , I think I have totally lost it.
I am trying to get an age from a dateofbirth field.
1.This is what I have done :
select datepart(year,getdate())- datepart(year,cast(dateofbirth as datetime))
from table
The values are all correct expect everything that has a year of 1949 or below.
This comes thru as 2049 , 2048 , 2047 etc instead of 1949 etc.
I am converting the dateofbirth field to a datetime as this is orginally a character field.
Why would this be happening?
2.If I use this sort of query :
select dateofbirth , datepart(year,getdate())- substring(dateofbirth,1,2)
from table
I get the right age result but in this format - 1948 , 1951, which could then just use the last two digits for the age.
Would there be a simpler way of doing this?
Thanks in advance
Tanya.
View 4 Replies
View Related
