Datetime to Time Only

Is it possible in SRS 2K to use a String.Format to change a field of
MM/DD/YYYY HH:MM:SS to display as HH:MM AM/PM only?
ThanksIt is possible. Just go to the properties of the field/ Textbox where you
want to be in time format and go to Format tab. and select format code and
click on "..." you can see time format just select it or it is basically "t"
format.
Amarnath
"lucotc" wrote:
> Is it possible in SRS 2K to use a String.Format to change a field of
> MM/DD/YYYY HH:MM:SS to display as HH:MM AM/PM only?
>
> Thanks

Datetime issue

In my DB, the date is in the following format : mm/dd/yyyy hh:mm:ss AM/PM
But wheni read the DB from the SqlDataReader, it returns the date in a different format : dd/mm/yyyy hh:mm:ss AM/PM
Why doesn't it return the date like it is in the DB?
For example, when i open the DB with Entreprise Manager : 5/25/2005 8:26:54 AM
But when i run the Debugger, it reads 25/05/2005 8:26:54 AM
Please help,
Thanks,
I think it may be your Windows regional setting and check you SQL Server configuration properties. Hope this helps.

DATETIME FORMAT

Datetime for using SQLSERVER QUERY NOT ANOTHER

MM/DD/YYYY HH:MM:SS AM/PM format using only sql query not using SUBSTRING ANY IDEAS

??

regards

sadeesh

Hi I think this will be useful

http://sqlserver2000.databases.aspfaq.com/can-i-make-sql-server-format-dates-and-times-for-me.html

Datetime datatype conversion to int hhmmyy format

Is there a way of converting a datetime data type in the form [DD/MM/YYYY HH:MM:SS] to an integer containing just the time in the form [HHMMSS].SELECT REPLACE((CONVERT(VARCHAR,GETDATE(),24)),':','')

This converts it to a varchar. You actually don't want to convert it to integer as it will drop off the leading zeros.

datetime conversion

Hi,
I tried to convert sql datetime to string (hh:mm:ss), or filetime, but i wasn't successful.
Will somebody help me with my problem? I don't know how I can solve my problem really.
Thank'sWhat have you tried? Have you tried T-SQL'sCONVERT function?
|||Yes, I have, but I haven't successful.|||Well rather than us guessing, tell us what you've tried and we can helpyou troubleshoot your code. Let us know the data you have, thedata you want, and the code you're using to try to get there.
|||

Ok,

code is below:

...........
//split_time is sql datetime
myCommand.CommandText = "Select CAST(split_time AS timestamp) AS result from "some_table"+
" where number = '100' and control = '1'";

myCommand.Connection = connection;
//connection is ref SqlConnection connection (= connection string isn't null)
connection.Open();

SqlDataReader dr = myCommand.ExecuteReader();

while(dr3.Read())
{
string time = dr["result"].ToString();//I WANT TO FORMAT result AS STRING IN FORMAT hh:mm:ss
}
dr.Close();
connection.Close;
Thank's for help

|||OK, this makes it much easier to help you now. You can either dothe formatting in your SQL statement, or you can do the formatting inyour ToString method on your page. I would let SQL return thedata in its native format and use the ToString to manipulate it as yousee fit.
The largest part of your problem is that you are CASTing AS a timestamp column. With SQL Server,timestampis a misleading, misnamed data type as it is actually a binary datatype related to row versioning. As a developer you should neverneed to use the timestamp data type. What you really need is thedatetime data type. What is the data type of your split_time column?
Check outDateTimeFormatInfo for help on the parameters to choose for the ToString method.

|||Thank's a lot for your help with datetime data type. It works good !

DateTime column conversion

1. How can I convert a DateTime datatype column to hh:mm:ss AM (or PM) ?
For example:
The column dTime is of DateTime datatype and has the value of 12:26:05 AM.
When I do the following sql:
select CONVERT( CHAR(10), dTime, 8) from myTbl
it returns 00:26:13 instead of 12:26:13 AM
2. How can I convert DateTime datatype column to dd-mon-yy ?
For example:
The column dDay is of DateTime datatype and has the value of 11/29/2005.
When I do the following sql:
select CONVERT( CHAR(10), dDay, 6) from myTbl
it returns 29 Nov 05 instead of 29-Nov-05
Thank you.> 1. How can I convert a DateTime datatype column to hh:mm:ss AM (or PM) ?
SELECT LTRIM(RIGHT(CONVERT(CHAR(20), GETDATE(), 22), 11))
Or, format it where it belongs, in the presentation layer.

> 2. How can I convert DateTime datatype column to dd-mon-yy ?
SELECT REPLACE(CONVERT(CHAR(9), GETDATE(), 6), ' ', '-')
Or, format it where it belongs, in the presentation layer.|||fniles wrote:

> 1. How can I convert a DateTime datatype column to hh:mm:ss AM (or PM) ?
> For example:
> The column dTime is of DateTime datatype and has the value of 12:26:05 AM.
> When I do the following sql:
> select CONVERT( CHAR(10), dTime, 8) from myTbl
> it returns 00:26:13 instead of 12:26:13 AM
> 2. How can I convert DateTime datatype column to dd-mon-yy ?
> For example:
> The column dDay is of DateTime datatype and has the value of 11/29/2005.
> When I do the following sql:
> select CONVERT( CHAR(10), dDay, 6) from myTbl
> it returns 29 Nov 05 instead of 29-Nov-05
> Thank you.
The smartest answer is to format this stuff client side. After all,
some of your users may have a legitimate need to cut-and-paste or sort
the dates in some external app. That's hard to do if you return a
clumsy and ambiguous string format such as "29-Nov-05". Also, maybe not
every user's preferred language will be English.
If you are the only user or if you prefer to dictate to your users how
they should read dates and times, try these:
SELECT SUBSTRING(CONVERT(CHAR(26),CURRENT_TIMES
TAMP,9),13,8)
+ RIGHT(CONVERT(CHAR(26),CURRENT_TIMESTAMP
,9),2) ;
SELECT REPLACE(CONVERT(CHAR(10),CURRENT_TIMESTA
MP,6),' ','-') ;
David Portas
SQL Server MVP
--|||Thank you for your help.
Is it correct that "select CONVERT(CHAR(20), GETDATE(), 22) " return
something like "12/30/99 12:25:13 AM" ?
Thanks.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23wSsGoMBGHA.2576@.TK2MSFTNGP10.phx.gbl...
> SELECT LTRIM(RIGHT(CONVERT(CHAR(20), GETDATE(), 22), 11))
> Or, format it where it belongs, in the presentation layer.
>
> SELECT REPLACE(CONVERT(CHAR(9), GETDATE(), 6), ' ', '-')
> Or, format it where it belongs, in the presentation layer.
>|||> Is it correct that "select CONVERT(CHAR(20), GETDATE(), 22) " return
> something like "12/30/99 12:25:13 AM" ?
Did you try it?|||Yes, and it shows "12/30/99 1", which to me looks truncated, because when I
did LTRIM(RIGHT(CONVERT(CHAR(20), HistTradesOrig.filltime, 22), 20)), it
returns "12/30/99 12:25:13 AM"
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OmbQjwMBGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Did you try it?
>|||That looks like CONVERT(CHAR(10), not CONVERT(CHAR(20) ... otherwise, can
you show a repro?
"fniles" <fniles@.pfmail.com> wrote in message
news:emaa2%23MBGHA.216@.TK2MSFTNGP15.phx.gbl...
> Yes, and it shows "12/30/99 1", which to me looks truncated, because when
> I did LTRIM(RIGHT(CONVERT(CHAR(20), HistTradesOrig.filltime, 22), 20)), it
> returns "12/30/99 12:25:13 AM"
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OmbQjwMBGHA.1312@.TK2MSFTNGP09.phx.gbl...
>|||Yes, you are correct, my mistake. Sorry about that.
Thank you for your help.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%239zphBNBGHA.3916@.tk2msftngp13.phx.gbl...
> That looks like CONVERT(CHAR(10), not CONVERT(CHAR(20) ... otherwise, can
> you show a repro?
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:emaa2%23MBGHA.216@.TK2MSFTNGP15.phx.gbl...
>

Datedifference - Please help

Hi,
I have a problem with the following:
I have 2 dates in the following format mm/dd/yyyy hh:mm:ss AM.
I would like to measure the difference between 2 dates (Date1 and Date2).
When I use the following expression "DATEDIFF(d, Date1, Date2)" I get a value
without decimals. How can I have a value with decimals like e.g. 5.38
Please help!!!
Thanks!
DHLSounds like a SQL problem, so perhaps check with sqlserver.programming, not
ssrs.
DATEDIFF takes one portion of a date, so you can pick hours, days, minutes,
etc. To quote the BOL (via a google search with just "datediff")
http://msdn2.microsoft.com/en-us/library/aa258269(sql.80).aspx
"Returns the number of date and time **boundaries** crossed between two
specified dates."
If you want a decimal value where the days is before the decimal, and the
% of a day is after, then you can just use floats like this (using the ".0"
is important, it tells us that it is a floating point number. You could
also convert(float,24), but that is ugly =)
declare @.date1 datetime; set @.date1=getdate()
declare @.date2 datetime; set @.date2=dateadd(hour,-5,(getdate()-1))
select
5/24.0
, convert(float, @.date1 - @.date2)
, datediff(hour,@.date1,@.date2)/24.0 -- only hour resolution
Full days are before, and % of a day (hours/24) is after the decimal. Datediff
will only give you 'to the hour' results, because that is what it measures.
So if it is 1 day, 2 hours and 23 minutes, that will show up the same as
1d 2h 00m, etc.
Using convert returns a POSTIVE number becase @.date1 is greater than @.date2.
DateDiff understands what you are trying to do, so it tells you it is -1.2
days difference. Swap them as you see fit, or use ABS()
HTH,
// Andrew
> I have a problem with the following:
> I have 2 dates in the following format mm/dd/yyyy hh:mm:ss AM.
> I would like to measure the difference between 2 dates (Date1 and
> Date2). When I use the following expression "DATEDIFF(d, Date1,
> Date2)" I get a value without decimals. How can I have a value with
> decimals like e.g. 5.38
> Please help!!!
> Thanks!
> DHL
>|||DHL,
You could also do a DATEDIFF with say hours and then convert it to days to
get a decimal value.
Reeves
"DHL" wrote:
> Hi,
> I have a problem with the following:
> I have 2 dates in the following format mm/dd/yyyy hh:mm:ss AM.
> I would like to measure the difference between 2 dates (Date1 and Date2).
> When I use the following expression "DATEDIFF(d, Date1, Date2)" I get a value
> without decimals. How can I have a value with decimals like e.g. 5.38
> Please help!!!
> Thanks!
> DHL

Dateadd - Time accumulation

Hi.
I'm in the process of converting seconds into an HH:mm:ss format using the
following statement...
=DateAdd("s", Sum(Fields!TimeInSeconds.Value), #01/01/0001#)
This gives me an absolute value of the beginning of time, which is great.
The problem arises when I clock over the 24h scenario, and my output with
the format of HH:mm:ss just displays as 01:00:00 (if 25 hours have
accumulated).
The base value of my field would now show as #01/02/001 01:00:00#.
I would like to see this as 25:00:00
Any ideas.
Thanks
GaryYou may want to look at the DateDiff VB.NET function.
E.g. =Datediff("s", Fields!End_Time.Value, Fields!Start_Time.Value)
Details on MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vsfctdatediff.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:2E179571-45B0-4C7B-A316-FBA29E8F53D7@.microsoft.com...
> Hi.
> I'm in the process of converting seconds into an HH:mm:ss format using the
> following statement...
> =DateAdd("s", Sum(Fields!TimeInSeconds.Value), #01/01/0001#)
> This gives me an absolute value of the beginning of time, which is great.
> The problem arises when I clock over the 24h scenario, and my output with
> the format of HH:mm:ss just displays as 01:00:00 (if 25 hours have
> accumulated).
> The base value of my field would now show as #01/02/001 01:00:00#.
> I would like to see this as 25:00:00
> Any ideas.
> Thanks
> Gary