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 Query

How to display records between datetime field??WHERE dateCol BETWEEN @.date1 AND @.date2

???|||how do i display records between two fields Callstartdt and endcalldt bot the fields are datetime fields...

What would be syntax for the query??

Please help..|||You really have to give us more information

Like ddl, sample data and expected results

Read the hint sticky at the top of the forum to see how to post a question here|||Do i need to post the design of the table??

How do i copy a design of the table...

I want to display all the records between two dates.|||go to enterprise mangler and right click on the table

Choose all tasks then generate sql server script

Did you read the hint link at the top of the page?

it's all explained up there, but in any case

When you say betyween 2 dates?

Which 2 dates?

Or is it a number of days betwen 2 dates

The question is not very clear, and that's why code examples would help us immensly|||How to display records between datetime field??



***********
datetime * records * field
***********|||Lmao! :D

Datetime only time needed

Wel maybe someone could help me.
I'm working with an excisting database using MSSQL, where are 2 T_datetime
fields.
If i display these 2 columns i see the date time format as follow:
Aug 5 2004 6:03PM
The problem is that i only need de time format like 6:03PM or better if
possible 18:03.
Could anybody help mee with these 2 problems.See function CONVERT in BOL. It is better to do the formatting in your clien
t
app, reporting tool, or programming language.
select left(right(convert(varchar(25), getdate(), 100), 7), 5)
AMB
"Smarteye" wrote:

> Wel maybe someone could help me.
> I'm working with an excisting database using MSSQL, where are 2 T_datetime
> fields.
> If i display these 2 columns i see the date time format as follow:
> Aug 5 2004 6:03PM
> The problem is that i only need de time format like 6:03PM or better if
> possible 18:03.
> Could anybody help mee with these 2 problems.|||I cannot change the excisting Database,
If i use this code i see no output in Php.
does any know
"Alejandro Mesa" wrote:
> See function CONVERT in BOL. It is better to do the formatting in your cli
ent
> app, reporting tool, or programming language.
>
> select left(right(convert(varchar(25), getdate(), 100), 7), 5)
>
> AMB
>
> "Smarteye" wrote:
>|||> I cannot change the excisting Database,
I did not say to change the db.

> If i use this code i see no output in Php.
> does any know
>
Which code?
AMB
"Smarteye" wrote:
> I cannot change the excisting Database,
> If i use this code i see no output in Php.
> does any know
> "Alejandro Mesa" wrote:
>|||What do you mean, if I display these 2 columns? Can you do a select in QA
and post the results?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Smarteye" <Smarteye@.discussions.microsoft.com> wrote in message
news:4EDDC42C-6CCE-4685-B84E-936C177C2E6A@.microsoft.com...
> Wel maybe someone could help me.
> I'm working with an excisting database using MSSQL, where are 2 T_datetime
> fields.
> If i display these 2 columns i see the date time format as follow:
> Aug 5 2004 6:03PM
> The problem is that i only need de time format like 6:03PM or better if
> possible 18:03.
> Could anybody help mee with these 2 problems.|||$query = " select left(right(convert(varchar(25), PunchIn(), 100), 7), 5)";
//punch in is columname
this is my query what was suggest when i use this i have no output.
"Louis Davidson" wrote:

> What do you mean, if I display these 2 columns? Can you do a select in QA
> and post the results?
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Smarteye" <Smarteye@.discussions.microsoft.com> wrote in message
> news:4EDDC42C-6CCE-4685-B84E-936C177C2E6A@.microsoft.com...
>
>|||What is punchIn() or punchLn(). This is not valid SQL. Can you print what
is in your $query variable and post?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Smarteye" <Smarteye@.discussions.microsoft.com> wrote in message
news:40399FA1-87F5-4D8A-97B9-F1F655F814B7@.microsoft.com...
> $query = " select left(right(convert(varchar(25), PunchIn(), 100), 7),
> 5)";
> //punch in is columname
> this is my query what was suggest when i use this i have no output.
> "Louis Davidson" wrote:
>|||Well it's working,
for one field if i do :
$query = " SELECT left(right(convert(varchar(25), PunchIn,100), 7), 7) FROM
X_PunchIn"; //punch in is columname
it wil display the time good.
but i need to display 2 columns
so i've made a 2nd query :
$query2 = "SELECT left(right(convert(varchar(25), PunchOut,100), 7), 5) FROM
X_PunchIn"; //punch in is columname
but mssql could only display 1 query so i've a problem .
Could any one solve this problem? and the problem AM/PM to 24hours
"Louis Davidson" wrote:

> What is punchIn() or punchLn(). This is not valid SQL. Can you print wha
t
> is in your $query variable and post?
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Smarteye" <Smarteye@.discussions.microsoft.com> wrote in message
> news:40399FA1-87F5-4D8A-97B9-F1F655F814B7@.microsoft.com...
>
>

DateTime formating.

I am trying to query a calendar table that has the [start] datetime of an event and then a [title] of the event. I am trying to display them as;

* 8:00 AM Get to work
* Independance day

some of the events have a start time and some only have a date. the ones that do not have a start time have a start time of 12:00:00 How do I query the two fields and only show the start time & title of the events that have a start time and just the title for the ones that do not have a start time..

I've tried this...

selectEvent=(CONVERT(CHAR(8),start,8)+' '+ title)fromCalendarwhereCONVERT(CHAR(8),start,8)<>'12:00:00'

It does display the start and event title but only for the ones that have a start time. And how do I get the 8:00 AM format for the time?

Thanks for any help.

Mark

Perhaps something like this might work:

SELECTCASEWHEN(datevalisNOTNULLAND dateval!='')THEN REPLACE(REPLACE(RIGHT(CONVERT(varchar,dateval,100),7),'P',' P'),'A',' A')ELSE''END +' ' + titleFROM yourTableWHERE

|||

--Try somthing like this:

SELECTCASEWHENCONVERT(varchar(8),date,108)='12:00:00'THEN'*'+title --Or CONVERT(varchar(8),date,108)='00:00:00'

ELSE'*'+RIGHT(CONVERT(varchar(19),date,100), 7)+' '+ title

ENDASEvent

FROM calendarwithTitle

|||Perfect thanks alot that took care of it for me!

DateTime Format

I have a database field that is in DateTime format... in my report I want to
stick this date in the middle of a string and display it... as MMM d, yyyy...
nothing I try seems to work... any ideas?Hello MER78,
You can use the Visual Basic Format() function like this:
=Format(Fields!YourDate.Value, "MMM d yyyy")
and of course you should be able to concatenate as you said:
=Fields!AnyData.Value & Format(Fields!YourDate.Value, "MMM d yyyy")
Hope this helps.
Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
http://www.amazon.com/exec/obidos/tg/detail/-/1590594231/102-0081700-1383300
http://www.apress.com/book/bookDisplay.html?bID=365
"MER78" <MER78@.discussions.microsoft.com> wrote in message
news:32D29B62-ADAA-4A1F-B176-362E7088A7CA@.microsoft.com...
>I have a database field that is in DateTime format... in my report I want
>to
> stick this date in the middle of a string and display it... as MMM d,
> yyyy...
> nothing I try seems to work... any ideas?
>

DateTime Datatype, How to display just Date, not time

I have a column with DateTime Datatype. But I want to display just Date , not time.

Like 4/26/2006 not 4/26/2006 9:25:55AM

pls help

check out the CONVERT and CAST functions. Try the following. If you check out books on line for the CONVERT functions they have a list of values and the formats the function will produce with the value. Here's an example:

SELECT CONVERT(varchar, getdate(), 101)

|||

Format it before output.

Cdate("10/1/2006 11:00:00").Tostring("d")

or if you are using it, and databinding it to a grid, textbox, etc, specify a format of "d".

Datetime - Regional Settings

Hi,

I am using an ODBC connection with Visual Interdev 6.0 to connect to a SQL datbase and display SQL tables on a website. I am having difficulty displaying the datetime in the correct format.

It appears that the datetime is being displayed in a format that is independent of the REGIONAL settings on either the client or the server! If I change the regional settings on either machine, the datetime is still displayed in the same format.

Any ideas as to how to solve this much appreciated!::I am using an ODBC connection with Visual Interdev 6.0 to connect to a SQL datbase and
::display SQL tables on a website

These problems could be rlated to our understanding of the stuff you are using. Basically, you seem, in addition, not to be aware of the difference between ASP and ASP.NET.

ASP.NET is the successor of ASP - and has nothing in common, codewise and usage wise.

THESE forums HERE are talking about ASP.NET.

I suggest you take your ASP question to news.microsoft.com - into the ASP groups there.

Getting a sensible answer ehere would be luck - I personally have stoopped working with ASP and Interdev yeawars ago ahd hardly remember anything from these hard times.|||Pass the date pre-formatted as a string to the front-end.

DatePart Function

Hi,

I'm curren't writing a stored procedure for my sql server express database and need to display the year part of a date field as '04' but using the DatePart function it will only display as '2004'? Is it possible to get the Year part of the date to display showing the last 2 digits of the Year only? I don't require another part of the date field just the Year part. All help is well come.

Code been used is shown below:

DATEPART(yy,[Date])

Hi,

The datepart function doesn't have an argument to do this. You can do the following to get the desired result:

SELECT RIGHT(DATEPART(yy, [Date]), 2)

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||Thanks Geert it worked a treat!!

DateDiff Display All Fields Begin / End Greater 2 Min

I have a SQL Server 2000 database with the table listed below.
I would like to write sql statement from the table listed below to display
of the
fields in the table using datediff function where begin_time - end_time is
greater than 2 minutes.
Please help me complete this task.
Table City_Time
Name varchar(25),
City varchar(35),
State varchar(2),
Begin_Time datetime,
End_Time datetimeCould it be as simple as one of these?
SELECT *
FROM City_Time
WHERE datediff(second, Begin_Time, End_Time) > 120
or
SELECT *
FROM City_Time
WHERE datediff(minute, Begin_Time, End_Time) > 2
Roy Harvey
Beacon Falls, CT
On Tue, 30 Jan 2007 20:15:01 -0800, Joe K.
<JoeK@.discussions.microsoft.com> wrote:
>I have a SQL Server 2000 database with the table listed below.
>I would like to write sql statement from the table listed below to display
>of the
>fields in the table using datediff function where begin_time - end_time is
>greater than 2 minutes.
>Please help me complete this task.
>Table City_Time
>Name varchar(25),
>City varchar(35),
>State varchar(2),
>Begin_Time datetime,
>End_Time datetime
>

DateDiff Display All Fields Begin / End Greater 2 Min

I have a SQL Server 2000 database with the table listed below.
I would like to write sql statement from the table listed below to display
of the
fields in the table using datediff function where begin_time - end_time is
greater than 2 minutes.
Please help me complete this task.
Table City_Time
Name varchar(25),
City varchar(35),
State varchar(2),
Begin_Time datetime,
End_Time datetime
Could it be as simple as one of these?
SELECT *
FROM City_Time
WHERE datediff(second, Begin_Time, End_Time) > 120
or
SELECT *
FROM City_Time
WHERE datediff(minute, Begin_Time, End_Time) > 2
Roy Harvey
Beacon Falls, CT
On Tue, 30 Jan 2007 20:15:01 -0800, Joe K.
<JoeK@.discussions.microsoft.com> wrote:

>I have a SQL Server 2000 database with the table listed below.
>I would like to write sql statement from the table listed below to display
>of the
>fields in the table using datediff function where begin_time - end_time is
>greater than 2 minutes.
>Please help me complete this task.
>Table City_Time
>Name varchar(25),
>City varchar(35),
>State varchar(2),
>Begin_Time datetime,
>End_Time datetime
>

DateDiff Display All Fields Begin / End Greater 2 Min

I have a SQL Server 2000 database with the table listed below.
I would like to write sql statement from the table listed below to display
of the
fields in the table using datediff function where begin_time - end_time is
greater than 2 minutes.
Please help me complete this task.
Table City_Time
Name varchar(25),
City varchar(35),
State varchar(2),
Begin_Time datetime,
End_Time datetimeCould it be as simple as one of these?
SELECT *
FROM City_Time
WHERE datediff(second, Begin_Time, End_Time) > 120
or
SELECT *
FROM City_Time
WHERE datediff(minute, Begin_Time, End_Time) > 2
Roy Harvey
Beacon Falls, CT
On Tue, 30 Jan 2007 20:15:01 -0800, Joe K.
<JoeK@.discussions.microsoft.com> wrote:

>I have a SQL Server 2000 database with the table listed below.
>I would like to write sql statement from the table listed below to display
>of the
>fields in the table using datediff function where begin_time - end_time is
>greater than 2 minutes.
>Please help me complete this task.
>Table City_Time
>Name varchar(25),
>City varchar(35),
>State varchar(2),
>Begin_Time datetime,
>End_Time datetime
>

datediff comparison to previous row in select statement

In a table I have information of a vehicle's status with an appropriate
date, what I am trying to achieve is to display the number of days
between two dates of two different statuses. Problem is that the same
scenario can happen on more than one occasion and there is no data
linking 2 statuses together. The query ran is
select distinct v.[reg no_],vsh.status,convert(char(16),vsh.[from
datetime],20)
from vehicle v
left join [vehicle status history] vsh on v.[vehicle serial no_] =
vsh.[vehicle serial no_]
and vsh.status IN('BOOKING-IN','DESPATCHED')
where v.[reg no_]='R3RTF'
order by 3
reg no_ status
-- -- --
R3RTF BOOKING-IN 2005-01-11 08:22
R3RTF DESPATCHED 2005-02-03 18:34
R3RTF BOOKING-IN 2005-02-04 09:58
R3RTF DESPATCHED 2005-02-04 14:52
R3RTF BOOKING-IN 2005-04-05 14:33
R3RTF DESPATCHED 2005-06-01 17:37
Looking at these results what I need is the date difference between the
BOOKING-IN and DESPATCHED dates. From the result set above the 3
datediff (for days) values would be 23, 0 & 57.
This is all part of a bigger result set and ideally I would like the
sum of the 3 date differences shown in this example. (23 + 0 + 57 = 80)
I am struggling to get this in a select statement, I have sampled using
cursors but I'm not sure this is the way to go. Using a SELECT
statement would be ideal.
Any help would be much appreciated.You can get the time a vehicle was booked in at a certain occasion with:
SELECT vd.[vehicle serial no_], vd.[from datetime], MIN(DATEDIFF(dd,
vb.[from datetime], vd.[from datetime])
FROM [vehicle status history] vd
INNER JOIN [vehicle status history] vb
ON vd.[vehicle serial no_] = vb.[vehicle serial no_]
AND vd.[from datetime] > vb.[from datetime]
WHERE vd.status = 'DESPATCHED'
AND vd.status = 'BOOKING-IN'
GROUP BY vd.[vehicle serial no_], vd.[from datetime]
If you want to have the sum of the times a vehicle spend booked in, you can
SUM over the previous query:
SELECT [vehicle serial no_], SUM(days_spend)
FROM (
SELECT vd.[vehicle serial no_], MIN(DATEDIFF(dd, vb.[from datetime],
vd.[from datetime]) AS days_spend
FROM [vehicle status history] vd
INNER JOIN [vehicle status history] vb
ON vd.[vehicle serial no_] = vb.[vehicle serial no_]
AND vd.[from datetime] > vb.[from datetime]
WHERE vd.status = 'DESPATCHED'
AND vd.status = 'BOOKING-IN'
GROUP BY vd.[vehicle serial no_], vd.[from datetime]
) ds
GROUP BY [vehicle serial no_]
(everything untested)
Jacco Schalkwijk
SQL Server MVP
"robz8701" <robz8701@.hotmail.com> wrote in message
news:1128070087.999572.326490@.g49g2000cwa.googlegroups.com...
> In a table I have information of a vehicle's status with an appropriate
> date, what I am trying to achieve is to display the number of days
> between two dates of two different statuses. Problem is that the same
> scenario can happen on more than one occasion and there is no data
> linking 2 statuses together. The query ran is
> select distinct v.[reg no_],vsh.status,convert(char(16),vsh.[from
> datetime],20)
> from vehicle v
> left join [vehicle status history] vsh on v.[vehicle serial no_] =
> vsh.[vehicle serial no_]
> and vsh.status IN('BOOKING-IN','DESPATCHED')
> where v.[reg no_]='R3RTF'
> order by 3
> reg no_ status
> -- -- --
> R3RTF BOOKING-IN 2005-01-11 08:22
> R3RTF DESPATCHED 2005-02-03 18:34
> R3RTF BOOKING-IN 2005-02-04 09:58
> R3RTF DESPATCHED 2005-02-04 14:52
> R3RTF BOOKING-IN 2005-04-05 14:33
> R3RTF DESPATCHED 2005-06-01 17:37
> Looking at these results what I need is the date difference between the
> BOOKING-IN and DESPATCHED dates. From the result set above the 3
> datediff (for days) values would be 23, 0 & 57.
> This is all part of a bigger result set and ideally I would like the
> sum of the 3 date differences shown in this example. (23 + 0 + 57 = 80)
> I am struggling to get this in a select statement, I have sampled using
> cursors but I'm not sure this is the way to go. Using a SELECT
> statement would be ideal.
> Any help would be much appreciated.
>