i 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
Jas
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
With the notation '03/12/2004' the SQL Server never knows what is the day value and what is the month value.
When you use '3 dec 2004' or '12 mar 2004' it works better.
I believe SQL Server uses the american notation. The format MM/DD/YYYY has to work also. But I always use the month format of above.
jan
feb
mar
apr
may
jun
jul
aug
sep
oct
nov
dec
Don't use parenteses. And the format that SQL Server will accept is YYYY-MM-DD.
For return all records of a day you must set the finnish date to date+1, because SQL will take that as midnight (00:00):
select * from tblstudents where classID='1' and studentstartdate between '2004-12-03' and '2004-12-04'
just use the YYYYMMDD format for your dtaes...
select * from tblstudents
where classID='1'
and studentstartdate between '20041203' and '20041212'
note did you want to include students whojoin of the 12 of december?
if you did then the enddate of the range needs to be the 13th Dec since time is also held...
i have run these querys
KarinLoos
select * from tblrequests where datesent between convert(datetime, '12/03/2004') and convert(datetime, '12/03/2004')
set dateformat dmy
select * from tblrequests where datesent between convert(datetime, '12/12/2004') and convert(datetime,'12/12/20
VMontalvao
select * from tblstudents where classID='1' and studentstartdate between '2004-12-03' and '2004-12-04'
i dont get any error messages but i also dont get any results back............
i need to get all the students who joined a class for the whole course of the day from midnight to midnight is this possible?
(as per lowfats point-- datetime defaults to midnight if you dont add times, so change the start date to 1 day less effectively being midnight on day -1 till midnight on actual day..
set dateformat dmy
select * from tblrequests where datesent between convert(datetime, '11/12/2004') and convert(datetime,'12/12/20
Hi all,
Can anyone help me to find a solution for this question
Parse for a numeric field using Active X in a DTS package?
I'm trying to parse for a numeric string from a column in a table. What I'm looking for is a numeric string of a fixed length of 8. The column is a comments field and can contain the numeric string in any position. I'm not familiar with ActiveX, so any suggestions will be useful. Thanks in advance,
Regards,
Jaishankar.
OK. I'm closer, but still confused. I added a sort criteria so I could see the latest record, and it's pulling records from yesterday (5/31/05). I assumed with the -60 days part of the statement, I'd only get records at least 60 days old (the purpose of this view), so why is it pulling them through current? Here's how it looks now.
SELECT TOP 100 PERCENT *, [date] AS Expr1
FROM dbo.bill
WHERE (mat_code IN
(SELECT DISTINCT bill.mat_code
FROM dbo.bill
WHERE bill.date < DateADD(d, - 60, getDate())))
ORDER BY [date] DESC
SELECT TOP 100 PERCENT *, [date] AS Expr1
FROM dbo.bill
WHERE (mat_code IN
(SELECT DISTINCT bill.mat_code
FROM dbo.bill
WHERE bill.date < DateADD(d, - 60, getDate())))
ORDER BY [date] DESC
this doesn't work because mat_code isn't distinct in the bill database.
So, it's limiting the data to all of the mat_codes that have a bill that has a date greater than 60 days old.
What you need is:
SELECT TOP 100 PERCENT *, [date] AS Expr1
FROM dbo.bill
WHERE bill.date < DateADD(d, - 60, getDate())))
ORDER BY [date] DESC
if your date format is dd/mm/yyy
use this query
select * from tablename where datefield between convert(datetime,'03/12/20
if your date format is mm/dd/yyy
then use this query
select * from tablename where datefield between convert(datetime,'03/12/20
I always find that completing the format works best
ie you have
select * from tblstudents where classID='1' and studentstartdate between ('03/12/2004') and ('03/12/2004')
should be
select * from tblstudents where classID='1' and studentstartdate between '03/12/2004 00:00:00' and '03/12/2004 23:59:59'
the add on bits are just hardcoded into the query, with the day being the dynamic element.
Business Accounts
Answer for Membership
by: KarinLoosPosted on 2005-01-03 at 08:08:09ID: 12944395
try
select * from tblstudents where classID='1' and studentstartdate between convert(datetime, 12/03/2004') and convert(datetime, '12/03/2004')
(ie mm dd yyy )
or
set dateformat dmy
select * from tblstudents where classID='1' and studentstartdate between convert(datetime, '03/12/2004') and convert(datetime,'03/12/20