Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
drbarnet
Starting Member
3 Posts |
 Posted - 2008-04-22 : 23:21:58
|
| My group is working on a project for school and neither of us have much experience with SQL or ASP. We are pretty much just writing an SQL query to get the data in a certain date range and using ASP to write the tables with the data in it. Our problem is the SQL database has separate fields for date and time. There is nothing we can do about this because the VB.NET program that populates the tables automatically does this. We can't figure out how to do the BETWEEN statement when they are separate fields. We can do it for a date range and it works fine. An example of what we want to do is show the data from 11:00 am on 04/01/2008 until 4:00pm 04/03/2008.Does anyone have any idea what we can do? I really want to learn this but it's frustrating because we can't seem to get much help at school. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-22 : 23:42:40
|
| Concatenate the date & time fields and use it like this:-YourQueryWHERE CAST(DateField + ' ' +Timefield AS datetime) BETWEEN @startdatetime AND @enddatetime |
 |
|
|
drbarnet
Starting Member
3 Posts |
Posted - 2008-04-23 : 02:21:32
|
quote:
Originally posted by visakh16
Concatenate the date & time fields and use it like this:-YourQueryWHERE CAST(DateField + ' ' +Timefield AS datetime) BETWEEN @startdatetime AND @enddatetime
Thanks a lot, I'll try it. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-04-23 : 12:26:05
|
| Are you using SQL 2008 with Date and Time fields? If not, how are you storing the Date and Time; VARCHAR or DATETIME columns?If you are storing them as actual datetimes (or using 2008) then you can query the columns directly without having to do concatenation. |
|
|
|
drbarnet
Starting Member
3 Posts |
Posted - 2008-04-23 : 17:31:44
|
| I'm using SQL Server 2005. Both fields are nvarchar(50).Here is what I'm going now and I'm still getting errors:Set sql = cnn.Execute("SELECT * FROM "& sql_data &" WHERE CAST(" & sql_date & " " & sql_time & " AS datetime) BETWEEN " & start_data &" AND " & end_data & "")I'm getting the error when I declare the following variables: sql_date = sql.Fields("Date").value sql_time = sql.Fields("Time").value |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
|
|
|
|
|
BETWEEN with Date/Time in Separate Fields