I have table with column Date Of Birth its datatype is smalldatetime. Now I was looking for SQL Statement like I will give from date and to date as parameter it should select date of birth occurring between that date and month.
View 5 RepliesHello Everyone,
I have a table containing a column "date of Birth" in char datatype, I need to calculate Age for the person from his or her date of birth. how would I be able to do that?
Any help will be appreciated.
Thanks
Hello
Can someone help please?
I'm using SQL Server 7 and trying to update a table containing over 250,000 records. I have a character(8) (CHARDOB) field representing dates of birth in the form YYYYMMDD and would like to update my datetime field (DOB) using a simple update command like:-
UPDATE MyTable SET DOB = RIGHT(CHARDOB, 2) + '/' + SUBSTRING(CHARDOB,
5, 2) + '/' + LEFT(CHARDOB, 4)
but get the 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 statement has been terminated.
The strange thing is that this works on a small dataset but crashes and burns on larger ones. I have tried different CAST and CONVERT options to transform the data into the correct format. I have tried a stored procedure, DTS transformations and execute SQL task in my DTS package without success.
Any help will be appreciated.
Len
Hi,all,
How to check the default value set up for datetime field? The reason I am asking is my database
field "birthday" is datetime(8), the default value I set to '' already. Everytime my asp program
have empty birthday or invalid birthdate for this field, the system automaticaly set it to
1900-01-01 00:00:00.000
Do you know what happened?
Thanks!
Betty
Hi, folks.
I have a table with a column Date_of_birth.
I want to evaluate average date_of_birth by grouping on depts.
AVG function doesn't seem to work with datetime col. Plz help.
I have a table name employee and datetime column named dateofbirth, how can i write a select statement to show their, date of birth and age in years?
This is how i did it, i couldn't finish it, Any feedback would be very thankful.
select name, datebirth, year(dateofbirth)
from employee
hello
i want to make one SQL Query in that i have problem so please help me
i have three column ID,Name,DOB
in that i want top 5 name whose Birthday is incomming days....
reply as soon as..
Here's the problem. I want to insert age in years to a table of children, using a MS SQL trigger to calculate age from Date of Birth (DOB). DOB format is mm/dd/yyyy. The DOB input comes from an ASP Insert statement. I've tried to use DateDiff and a user-defined function to calculate age without any success. Also the trigger needs to account for children of less than 1 year old, could be a 0. Age is integer in the children table. Any help is greatly appreciated. The sooner the better. Even a kick start is better than nothing.
View 11 Replies View RelatedI am trying to convert the birth dated from 19591229 to 12/29/1959 and using below code but its not giving me in that format its printing line 1959-12-29. how do I do this?
bdate=convert(date, p.date_of_birth,101)
Hello, I'm new to SQL Server, working for a non-profit computerizing alot of its data.I imported a table of people's names, birth dates, etc. into SS2005from Access, and the birth_date was imported as an Access date/timefield, giving it the datetime datatype in SQL.The column values look like:10/14/1964 12:00:00 AMWhere and how do I learn to specify that all fields like this should bein ISO format of yyyy-mm-dd??Do I have to create a new column and put all the dates into it??Should I just convert the data in queries/views??Use a constraint to format the data??I can redo the Access table if necessary, it is only 300-some rows.I tried BOL but it was not helpful...The end users will likely enter mm/dd/yy or mm/dd/yyyy and it will haveto be stored properly in the database table as column/fieldbirth_date...Thank you, Tom
View 11 Replies View RelatedI need creating date of birth using ID number the ouput that im looking is a follows
e.g. RSA ID: 800101 (80 is year, 01 is month and 01 is day) that will be 1980 01 01
e.g. RSA ID: 000101 (00 is year, 01 is month and 01 is day) that will be 2000 01 01
The desired format I need is to take the above and create date of birth with the below format as required by the application used.
01 Jan 1980
01 Jan 2000
I have 3 fields (DOB_YEAR, DOB_MO, DOB_DAY)
They are populated like this (1985, 12, 21)
I would like to take the 3 values of populate one field (BIRTH_DATE) with these combined values to look like this (19851221) BIRTH_DATE is an 8 character field in datetime format.
The code
UPDATE DOB
SET BIRTH_DATE = (dob_year+dob_mo+dob_day)
Returns 6/11/1905 for 1959,11,17
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?Â
Hi,
I have soma ado.net code that inserts 7 parameters in a database ( a date, 6 integers).
I also use a self incrementing ID but the date is set as primary key because for each series of 6 numbers of a certain date there may only be 1 entry. Moreover only 1 entry of 6 integers is possible for 2 days of the week, (tue and fr).
I manage to insert a row of data in the database, where the date is set as smalldatetime and displays as follows: 1/05/2007 0:00:00 in the table.
I want to retrieve the series of numbers for a certain date that has been entered (without taking in account the hours and seconds).
A where clause seems to be needed but I don’t know the syntax or don’t find the right function
I use the following code to insert the row :
command.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime, 40, "LDate"));
command.Parameters[6].Value = DateTime.Today.ToString();
command.ExecuteNonQuery();
and the following code to get the row back (to put in arraylist):
“SELECT C1, C2, C3, C4, C5, C6 FROM Series WHERE (LDate = Today())�
WHERE LDate = '" + DateTime.Today.ToString() + "'"
Which is the correct syntax? Is there a better way to insert and select based on the date?
I don’t get any error messages and the code executes fine but I only get an empty datatable in my dataset (the table isn’t looped for rows I noticed while debugging).
Today’s date is in the database but isn’t found by my tsql code I think.
Any help would be greatly appreciated!
Grtz
Pascal
Hi --
What is the proper way to select results with a date in the where statement when the datatype of the column is datetime?
select * from table where date_field = '3-23-2006' does not get any results.
Thank-you for your help.
Hello,I have a couple of tables. The client tables and the contactedtables.I am not sure how to start on this, what I need is a way to query allmy clientsthen show any client that the last visit and or called day is greaterthan 30 days.Now it gets confusing, Suppose the client was visited more than 30 daysagobut was called only 10 days ago, I really would like to have thisappear on the samequery.So the report would look similar to this below.Visit Date Called DateClientA 2006-11-02 2006-12-16ClientB 2006-12-17 2006-10-30ClientC 2006-10-15 2006-10-16ClientDFields (Simplified)Clients: Name, Address, Phone.Contacted: Name, Date, Visit, Call.I need to query all l names, but I only need the last visit and lastphone call. Then determine if either date is greater than 30 days ifso, display the last date of each type of contact. And if there isnothing for the client in the contacted table this needs to show also,ClientD.Any tips, ideas would be greatly appreciated....ThanksIce
View 9 Replies View RelatedI'm trying to return the Max and Min Date value for each Item in a History table. Thanks for any help. Example:
Code Block
ID Item Date
1 A 12/1
2 A 11/1
3 A 10/1
4 B 9/1
5 B 8/1
6 B 7/1
The result I would be expecting is:
Code Block
Item Max Min
------------
A 12/1 10/1
B 9/1 7/1
I'm having a mental block on a select statement.I have a table with the following columns:KitId int
LotNo varchar(10)
DateReceived smalldatetimeIt is possible to have many rows with the same LotNo but a differing DateReceived I need to write a select statement that returns the KitId for a given LotNo with the earliest DateReceivedso if I had rows:KitId =1, LotNo = 123, DateReceived = 11th May 2008KitId =2, LotNo = 123, DateReceived = 28th May 2008KitId =3, LotNo = 125, DateReceived = 28th May 2008KitId =4, LotNo = 127, DateReceived = 28th May 2008KitId =5, LotNo = 123, DateReceived = 12th June 2008I would want to retrieve KitId=1 if I provided LotNo 123 as a parameter Whilst it should always be the case that the LotNo with the earliest date will have the lowest KitId I cannot guarantee that will be the case so going for the lowest KitId isn't an optionCan one of you SQL gurus provide me with the statement I need?ThanksNeil
So I have this query where i need to get the average date of about five different dates... Is there any way to do this or am I screwed. I looked at using the avg function but SQL server 2005 did not like that.
Thanks in advance for any help.
Hello all. I ma using the following query to pull back data. The MergeHistory table has a column named DateMerged. I am looking to pull back the one record with the most recent DateMerged. I have managed to get the query as far as below but not sure how to select the most recent one. Can anyone help with this? I was told it may be along the line of SELECT TOP 1 or something?
INSERT INTO @List (IndexID, IndexName, MergeSystem, Status, DateCreated, CreatedBy, DataTag, MergedDate)
SELECT DISTINCT
RT.IndexId,
isnull(dbo.ufn_GetBestIdentifier(RT.IndexId), dbo.ufn_GetBestVirtualIdentifier(RT.IndexId)),
dbo.ufn_GetEntitySystemName(RT.IndexId),
RT.Status,
CONVERT(varchar, RT.DateCreated, 106) as DateCreated,
RT.CreatedBy,
RT.DataTag,
MH.MergedDate
FROM @resulttable AS RT, MergeHistory AS MH
WHERE RT.IndexId = MH.EntityID
Hi,
I've been wrestling with this problem for a while, but my newbie SQL skills are no match for it, so I'm hoping somebody here can point me in the right direction.
I have the following table, called AccountPayments:
PaymentID CaseID_FK AmountPaid InvoiceDate PaymentDate
12135 250 01/16/2006 01/31/2006
13136 150 01/16/2006 01/31/2006
14 135 200 01/24/2006 02/05/2006
15 136 300 01/24/2006 02/05/2006
16139 120 01/25/2006 02/07/2006
17 136 100 01/25/2006 02/10/2006
18 137 115 01/25/2006 02/13/2006
19 136 110 02/12/2006 02/25/2006
20139 310 02/12/2006 03/06/2006
21 138 220 02/15/2006 03/09/2006
I would like to select all the entries where the payment date is, at the latest, the 7th day of the month following the one in which the invoice was issued.
In other words: If the invoice date is in January, I would like to select all the entries where the payment date is February 7th at the latest. If the invoice date is in February, I would like to select all the entries where the payment date is March 7th at the latest.
So, for the above table, I would like to get the following result:
PaymentID CaseID_FK AmountPaid InvoiceDate PaymentDate
12135 250 01/16/2006 01/31/2006
13136 150 01/16/2006 01/31/2006
14 135 200 01/24/2006 02/05/2006
15 136 300 01/24/2006 02/05/2006
16139 120 01/25/2006 02/07/2006
19 136 110 02/12/2006 02/25/2006
20139 310 02/12/2006 03/06/2006
Does anybody know if it's possible to do this? I'm working with SQL Server 2000 and have been playing around with dateadd, but I can't seem to figure it out.
Thank you for your help.
I need to select the most recent price for an item from this table where certain criteria are met:
flexing_stock_transactions
--------------------------
item date price status week firm_or_commission
---- -------- ----- ----- ---- ------------------
CH 10/05/07 200 2 35 F
AL 10/05/07 195 1 35 C
CH 10/05/07 209 1 35 F
CH 11/05/07 210 1 35 C
I am currently using:
SELECT
price, date
FROM flexing_stock_transactions
WHERE (
[date] = (SELECT MAX([date]) FROM flexing_stock_transactions)
AND item = 'CH'
AND status = '1'
AND week = '35'
AND firm_or_commission = 'F'
)
zero rows are returned wheras I was expecting:
209, 10/05/07
I'm using MS Server 2005
Good Morning,I have a view that contains rate information, contractIDs, and effectivedates.I need to select the rate info based on contractID and date.I can provide a date and contractID, and I need to select the rate info forthat contract where the effective date is <= dateprovided.I need the 1 record that is closest to that date.I am thinking something with max() perhaps. Any ideas? The <= effectivedate will return several rows, I just need the one closest to the date Iprovide.Thanks for any advice,CK
View 2 Replies View RelatedI'm trying to construct code that will return the last non-NULL value in a column containing daily records.
For E.G. I want to know what the LAST value of Description field when it is not NULL, AND the Date is within the range t=1 to t=5 => i.e. "Dog" in the below example:
Date Description
1 NULL
2 NULL
3 Cat
4 Cat
5 Dog
6 NULL
7 NULL
8 Mouse
9 NULL
10 NULL
How this can be achieved?
I have to select rows based on if the transaction date = todays date.The column is defined as numeric 8 with 0 decimal.how to code for todays date with such a column?
View 8 Replies View RelatedI have table like this and I need to remove the duplicates selecting the Min Date
IDDate Alloc ProdWater Tub Cas
3752011-10-10 00:00:00.00000014.70
3752011-10-11 00:00:00.00000014.70
3232014-04-24 00:00:00.000000352555.7
3232014-04-26 00:00:00.000000352555.7
3562014-04-01 00:00:00.000000318.8471.1
3562014-04-02 00:00:00.000000318.8471.1
3562014-04-03 00:00:00.000000318.8471.1
3562014-04-04 00:00:00.000000318.8471.1
3232014-05-20 00:00:00.000000649.1976.9
3232014-05-21 00:00:00.000000649.1976.9
3232014-05-22 00:00:00.000000649.1976.9
I would like my result set to be like this:
3752011-10-10 00:00:00.00000014.70
3232014-04-24 00:00:00.000000352555.7
3562014-04-01 00:00:00.000000318.8471.1
3232014-05-20 00:00:00.000000649.1976.9
The following is the sql being executed in my Crystal report. There seems to be an issue with the same date request. Sometimes it shows data, other times not. We have data every day, we are a mass market company. Is there anything i can do for the sql to do select when from and to dates are the same?
SELECT "OEHIS1"."ODORD#", "OEHIS1"."ODORDT", "OEHIS1"."ODNTU$", "OEHIS1"."ODSHP#", "ICPRT1"."IARC11", "OEHIS1"."ODORDD", "ICPRT1"."IARCC4", "OEHIS1"."ODQTY#", "OEHIS1"."ODRQSD", "MFHHMH"."MHAWGT", "OEHIS1"."ODPRT#", "OEHIS1"."ODPRLC"
FROM ("S10M10"."ASTCCDTA"."EODDETAILS" "OEHIS1" INNER JOIN "S10M10"."ASTDTA"."ICPRT1" "ICPRT1" ON "OEHIS1"."ODPRT#"="ICPRT1"."IAPRT#") INNER JOIN "S10M10"."DLIB"."MFHHMH" "MFHHMH" ON "OEHIS1"."ODORD#"="MFHHMH"."MHORDP"
[Code] ....
Here's my current SQL:
SELECT
RN_TEST_ID AS 'Test ID',
MAX(RN_EXECUTION_DATE) AS 'Last Execution Date',
MAX(RN_EXECUTION_TIME) AS 'Execution Time',
RN_DURATION AS 'Run Duration'
FROM RUN
[code]....
Here's a sample of data returned:
Test IDLast Execution DateExecution TimeRun Duration
86722/9/2015 0:0012:08:16180
86822/9/2015 0:0011:29:06181
86842/9/2015 0:0008:29:17119
105252/3/2015 0:0019:03:4089
105252/3/2015 0:0019:10:13305
106682/3/2015 0:0018:55:43103
106682/6/2015 0:0018:10:50123
114572/3/2015 0:0011:40:0726
What I need are two things:
1. The query should only return one record for each test id
2. The record returned should be the most recent. By most recent I mean the RN_EXECUTION_DATE and RN_EXECUTION_TIME of the returned row should be the most recent in time.
For example, in the sample data there are multiple rows with the same test id (for example 10668 and 10525. The 10525 is even more problematic since its execution date is the same for both rows returned - the execution times differ. Again, I want one record per test id and that record should be the most recent in time.
Using MS SQL 2005, how can I SELECT only the records whos date field is equal to the most recent date held in that field?
For example:
date item colour
---- ---- ------
12/15/2007 shirt red
12/13/2007 shirt black
12/15/2007 shirt blue
result(s) expected:
12/15/2007 shirt red
12/15/2007 shirt blue
I have the following function(s) that each joins an active record withit's most recent record in an audit trail table, to show differences.I've perused the execution plans for 2 versions of this function in QAand it seems to me that the two versions execute identically - that is,the addressing the sub-query of the WHERE clause with either IN or =results in the same execution plan.Does this seem plausible or did I misinterpret something in QA?---ALTER FUNCTION Function9()RETURNS TABLEAS RETURN(SELECT ID, Numb, Name, Q.Diff_ID, Q.Diff_Numb, Q.Diff_Name,FROM tblQuestion INNER JOIN(SELECT ID AS Diff_ID, Numb AS Diff_Numb, Name as Diff_NameFROM tblQuestion_AuditWHERE AuditDate IN (SELECT MAX(AuditDate) FROM tblQuestion_Audit)) AS QON dbo.tblItem.ID = Q.Diff_ID)ALTER FUNCTION Function10()RETURNS TABLEAS RETURN(SELECT ID, Numb, Name, Q.Diff_ID, Q.Diff_Numb, Q.Diff_Name,FROM tblQuestion INNER JOIN(SELECT ID AS Diff_ID, Numb AS Diff_Numb, Name as Diff_NameFROM tblQuestion_AuditWHERE AuditDate = (SELECT MAX(AuditDate) FROM tblQuestion_Audit)) AS QON dbo.tblItem.ID = Q.Diff_ID)
View 3 Replies View RelatedI have a table that has a DateTime column which uses a DataTimedatatype. How do I retrieve a range of records based on the month andyear using ms sql?Eugene Anthony*** Sent via Developersdex http://www.developersdex.com ***
View 4 Replies View RelatedI have 6 columns, all with dates within them, i.e.Proposed Start Date 1Proposed Start Date 2Proposed Start Date 3Proposed Finish Date 1Proposed Finish Date 2Proposed Finish Date 3What I need to do is narrow this down into two fields:Start DateFinish DateSo I need to find the newest value from the columns. i.e.If PS1 filled PS2 and PS3 empty, then Start Date = PS1If PS3 empty and PS2 filled, then Start Date = PS2If PS3 filled then PS3and similarly for Proposed Finish Dates.Anyone knew how I can do this.(Maybe the following will help for the programmers out there:If PS3 <> nullThen StartDate = PS3Else if PS2 <> nullThen StartDate = PS2ElseStartDate = PS1End IFJagdip
View 5 Replies View RelatedOK. I have this query, works on another box fine.
SELECT *
FROM bookkeep RIGHT OUTER JOIN
acraccts ON LEFT(bookkeep.accnum, 9) = acraccts.p_accnum
WHERE (bookkeep.busdate = '03/09/10') AND (bookkeep.tradetype = 'S')
on my sql box, if i run it, i get no data.
i figured out that if i change the where clause to (bookkeep.busdate='2003/09/10') it works
OR
if i simply put SET DATEFORMAT YMD on the first line before the SELECT * that it also works.
my problem is the basic query is hard coded and i really can't change it.
is there a global sql server setting that will make my sql 2000 sp3 box recognize '30/09/10' as 2003/09/10?