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..
Hello 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
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 Replies View RelatedHello
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
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
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).
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 RelatedCan 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 RelatedI'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
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
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,
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
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
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!
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.
The user enters in a date which is a varchar type and I'm trying to query the
database for all the birthdays of that date. The problem with SQL server is that when you do a search by the date, it makes a match with the timestamp of 12:00AM. So, if you search by a certain date, it also does a search by the time.
If John's birthday in the database is 'Jan 1 1999 08:00AM' and I use this
Select * from people where birthday = @UserInputDate,
the query returns NOTHING because the defualt time is 12AM and the date on that birthday is 08:00AM.
The only way I can get it to return ALL the birthdays of 1/1/1999 is with this statement:
select * from people where birthdate like 'Jan 1 1999 %:%'
But how do I convert the '1/1/1999' to the Jan 1 1999 format and append '%:%' at the end?
I've tried variations of the following:
select * from people where birthdate like @UserInputDate + ' %:%' but for some
reason that only appends one percentage sign '%:' and not '%:%'
Help!
Angel
Trying to perform a query where it will return all cases between the current day and the first day of the same month, regardless of whether it is this month or 6 months down the road.
WHERE Response_Flag = '1' AND Begun_By_Id = '" & Session("Emp_Id")& "' AND Begin_Date Between GetDate AND ???????
Any help would be greatly appreciated
Chris
I have to run a sql query to find out whether an employee has a birthday during the month of September. The date is in the format dd-mmm-yyyy (no time) and no matter what I do I can't get it to give me an answer.
So far I've tried LIKE, BETWEEN, >, <. Appreciate any help.
Thanks.
how would i query the table with dateid for a particular date
DateID
2005-11-24 14:40:00
select * from tbl1 where dateid like '2005-11-24%' ?
how about date and time like 9-11am of something
Hi!
I have a DB with this fields:
MID - primary key
EVENT - varchar(2)
EVENT_DATE datetime (format mm/dd/yyyy)
I need to design a query that will pickup records from DB with EVENT = '02' and EVENT_DATE equal or earlier than 18 days from the date the query was executed.
In this example, the query should display these 2 records if ran today:
MID EVENT EVENT_DATE
01 02 12/25/2005
02 02 12/20/2005
But not this:
MID EVENT EVENT_DATE
03 03 12/25/2005
04 02 01/01/2006
Any help will be greatly appreciated.
Hi all:
I am trying to get data that wasn't used in the last six month. Created_on (date field)
select Category_tree_value, Created_on from workitem
where Created_on (field was used from the last six months)
Created_on format (2005-10-01 00:00:00.000)
Thanking everyone in advance.
Lystra
Hi
I have two regarding questions regarding queries involving dates.
I have a number of tables in the database i am using, one of which holds date periods of a financial year so it looks like something below:
DatePeriod:
ID, Begin, End
Jan08,01/01/2008,25/01/2008
Feb08,01/02/2008,29/02/2008
Mar08,03/03/2008,28/03/2008
...
...
The tables actual data begins from Jan07.
Another table shows transactions made, something which looks like:
Transactions:
ID, DateMade, CustomerID, ProductID
22,24/01/2008,111,222
23,27/01/2008,117,222
24,27/02/2008,011,233
...
...
The first query I am trying to do is to perform a count of all transactions made during a financial month period as specified by the user. So i guess I would use a variable such as @month which links to the DatePeriod table. This is what I'm not sure on how to do.
The second query is performing a count of all the transactions made within the last 12 months of the month specified by the user.
Can anyone help with this?
Thanks
I had data that went in wrong
the date in the db went in as
2008-01-06 08:54:18.000
when it should have been 2008-06-01 08:54:18.000
is there an sql statement that i can run to just change all these records to the reverse - meaning to change the date from 01-06 to 06-01
and 02-06 to 06-02 leaving the time as is.
can someone help me with this?
I am trying to write a query pulling the last te_event from a table by the latest te_date for a set of records. My query is below
SELECT tickets.t_number, tickets.t_reference,ticket_events.te_event, ticket_events.te_date, FROM ticket_events INNER JOIN
tickets ON ticket_events.te_system_ref = tickets.t_number INNER JOIN
warrants_due ON tickets.t_number = warrants_due.wd_system_ref
[code]...
There are 25,000 tickets in the subquery. I can't work out where to put the MAX (ticket_events.te_date) statement.
Hello, this is my first time with SQL.
I have a Mysql database of 3 tables
1)CUSTOMER (cust#, name, address, tel#, etc)
2)PRODUCT (prod#, desc, price, etc)
3)BOOKINGS (cust#, prod#, order_date)
I also have an admin page with options to display:
1)all bookings
2)bookings for next 7 days
3)bookings for next 14 days
How do I query the database to show the cust#, prod# and order_date from the BOOKINGS table, and the name, address, price from the CUSTOMER table for the three date options above?
I think I need to join two tables within a SELECT statement but am stuck on how to use todays date in the query.
Many thanks
Clam
Hi all :),
I am trying to do a query so that i can identify an inactive customer. Now if the customer shopped in Jan 2005 and did not shop in 2006 he/she would be deemed as inactive.I have no idea where to start does any one have any ideas???