January 10, 2006 at 3:46 am
Hi I am having a bit of trouble trying to display my times in 24hr format, at the moment it is displaying the times with am and pm im just wondering if anyone has any ideas of how to do this
Tim
select ID, SUBSTRING(CAST(Date as nVarChar), 13,20) as Date from Session where (SquadId = 5) and CONVERT(smalldatetime, CONVERT(varchar(8), Date, 112)) = '01/10/2006'
January 10, 2006 at 5:04 am
Ok, let me see if I understand correctly what you want.
SELECT ID, CONVERT(varchar(8), Date, 114) AS Date
FROM Session
WHERE SquadId = 5
AND Date BETWEEN '2006-01-10 00:00:00' AND '2006-01-10 23:59:59'
Does that give you what you want?
January 10, 2006 at 5:17 am
yes indeed thats exactly what i needed thanks alot man i appreciate it
January 10, 2006 at 7:05 am
Refer to the BOL, go to the Index tab and enter CONVERT. Go to the option for Transact SQL. You will see a chart there for the different formats you can return Dates and times in.
-SQLBill
January 10, 2006 at 8:53 pm
The nice thing about the CONVERT is the fact that you can use the format parameter when you convert from (VAR)CHAR into (SMALL)DATETIME.
For example if you have a string '01/02/2006' - what is this one?
January 2nd or February 1st.?
You can avoid guessing games when you specify the format 101 or 103 respectively.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
January 11, 2006 at 6:25 am
JaceK0,
Actually, that is what SET DATEFORMAT is for.....
In your example, you would put
SET DATEFORMAT MDY --if it's Jan 2nd
-SQLBill
January 11, 2006 at 6:53 am
You are right SQLBill but what I am pointing out is the fact that you can use the format not only when converting into VARCHAR but when converting back to DATETIME, which is an overlooked feature. Most people concentrate on the DATETIME -> VARCHAR aspect of the conversion.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply