Printable View
I want to be able to enter a time (Military time) without having to enter special characters (AM or PM or :) from a parameter and without a picklist. I am wanting to display my data between a begin time and end time. I already have a begin date and end date, but also need to pull from specific times. How would I formulate this?
What rdbms?
The RDBMS (Relational Database Management System) I use is MS SQL Server 2005.
I'm a bit unsure as to what you need. Is this a display issue? Are you trying to add time elements to your existing dates? Can you explain your situation a little more?
How does your Military time look like?
I have a forecast report which displays tickets on the report layout. On the parameter selection, the user can select the start date calendar and end date. Tickets will pull between startdate and enddate. I am also looking to have another option as start time and end time. I do not want a picklist but I want to allow the user to enter their own time without using characters such as AM, PM or colon. Besides the dates, I want the tickets to pull between 0600 to 1800 of when the tickets were entered on the particular dates chosen or whatever times the user wants to enter.
Does the table have such column? Do you have sample data of the column?
No, the tables does not have a column that displays just the time. I do have a date/time field which is used for the (between startdate and enddate). I don't have the sample with me. It just shows when the ticket was opened and the time and it is not military time.
Military time is like 0100 for 1:00 AM, 1100 for 11:00 AM, 1200 for 12:00 PM, 1300 for 1:00 PM, 1800 for 6:00 PM, 2400 for 12:00 AM
If I understand you correctly, all you have to do is insert a colon in the proper place if the user enters a 4 digit military time (and put a space between your existing date and the time):
DECLARE @time char(4)
SET @time = '1330'
SELECT CONVERT(datetime, '2007-01-01') + ' ' + STUFF(@time, 3, 0, ':')
I am not entering this in Stored Procedures. I have it hard coded in the query. How would I enter this in the Main dataset of the the SELECT DISTINCT and WHERE clause? And how would I enter this in the two datasets for the starttime and endtime? Or do I not have to have two seperate datasets for the starttime and endtime?
I'm still not sure exactly what you need. You probably need to post your code and your data and show us what you are missing.
Look at it this way. When you click on the Date and Times property on the bottom right side of your computer. Once the windows opens, it gives you the options to click up or down arrows to choose your time. Kind of the same way but instead of clicking the up and down arrow, you would enter your begin time military format without colon and ending time as well.
Well then, I thought I already gave you what you needed, or at least pointed you in the right direction. If you have a date like this:
'2007-01-01'
Or whatever format it's in, and then you have a time entered like this:
'1330'
Then you combine them to get a datetime format like this:
CONVERT(datetime, '2007-01-01') + ' ' + STUFF('1330', 3, 0, ':')
If you want to put it in a WHERE clause:
SELECT * FROM whatever
WHERE StartTime >= CONVERT(datetime, '2007-01-01') + ' ' + STUFF('1330', 3, 0, ':')
I tried what you gave me the other day and it gave me the date and time together. I have a field that does that. I am wanting the time to be seperate in its own parameter. Instead of the person entering their own time, I may have to have just a drop down menu listing the 24 hour clock to select. I will try from what you gave me. I appreciate all your time and help. THANK YOU SO MUCH! :-D