?
Solved

Show milliseconds from SQL in Crystal Reports

Posted on 2014-03-22
14
Medium Priority
?
847 Views
Last Modified: 2014-03-23
I have a datetime in SQL SERVER 2008 that shows up to milliseconds :
2014-03-22 11:35:33.450 and want to know if there is a way to show the milliseconds in Crystal Reports which only goes to seconds.

I need this as I'm sorting by exact time stamps and with only the seconds reflected I seem to have duplicate timestamp even though in SQL there is always a difference (milliseconds.
0
Comment
Question by:John-S Pretorius
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
14 Comments
 
LVL 23

Expert Comment

by:Ido Millet
ID: 39948030
Use a Command, SQL Expression, SP, or a VIEW to Convert or Cast the DateTime into a String.
0
 
LVL 23

Assisted Solution

by:Ido Millet
Ido Millet earned 375 total points
ID: 39948041
This provides details on the Style argument required for the Convert() function:
http://www.w3schools.com/sql/func_convert.asp
Since you want to be able to sort on the resulting string, you need to use a style such as 121.  So this looks like a good choice:
CONVERT(VARCHAR(24), <Your_Date_Column>, 121)
0
 

Author Comment

by:John-S Pretorius
ID: 39948076
Thank you Ido,
I think this is what I'm looking for - Having trouble getting a SP together to convert this column (Time,{datetime},not null) within the dbo.ContractParkerMovements table.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 101

Expert Comment

by:mlmcc
ID: 39948229
It should be something like

SELECT  Convert(Varchar(24),ContractParkerMovements.Time,121) as strTime
FROM   ContractParkerMovements

mlmcc
0
 

Author Comment

by:John-S Pretorius
ID: 39948550
Thank you mlmcc, that works perfectly but I then loose all my other tables (doing this only show strTime) - how do I add this table and keep everything else in place.
0
 
LVL 23

Expert Comment

by:Ido Millet
ID: 39948564
That column expression is in addition to all your other columns. Not instead of.
0
 

Author Comment

by:John-S Pretorius
ID: 39948574
If I run(Execute) your statement (which is within a Stored Procedure)
SELECT  Convert(Varchar(24),ContractParkerMovements.Time,121) as strTime
FROM   ContractParkerMovements

I just get 1 table :-  strTime as a result
0
 
LVL 23

Expert Comment

by:Ido Millet
ID: 39948578
Your SP already has a SELECT statement with several columns.  Add just the expression (without the SELECT and the FROM) as an additional column.
0
 

Author Comment

by:John-S Pretorius
ID: 39949248
My SP is based on an already complex built View (ContractParkerMovements):- (See below) so when I run your command :- Convert(Varchar(24),ContractParkerMovements.Time,121) as strTime I just get 1 new column Strtime
-------------------------------------------------------------------------------------------------------------------------

SELECT        1 AS Quantity, dbo.MVKARTEBEWEGUNG.Ztpkt AS Time, dbo.KUNDE.AnlagenNr AS FacilityNo, dbo.KUNDE.KundenNr AS CustomerNo,
                         dbo.KUNDE.Titel AS CustomerTitle, dbo.KUNDE.Nachname AS CustomerSurname, dbo.KUNDE.Vorname AS CustomerFirstName,
                         dbo.KUNDE.Strasse AS Street, dbo.KUNDE.Plz AS ZipCode, dbo.KUNDE.Ort AS City, dbo.SDSYSLAND.Landeskennung3 AS Country,
                         dbo.KUNDE.SteuerNr AS TaxCode, dbo.KUNDE.AusweisNr AS IdDocumentNo, dbo.KUNDE.Telefon AS Telephone, dbo.KUNDE.VertragsNr AS RentalAgmtNo,
                         dbo.KUNDE.VertragsbeginnDat AS RentalAgmtBeginDate, dbo.KUNDE.KuendigungsDat AS RentalAgmtTerminationDate, dbo.KUNDE.Kaution AS Deposit,
                         dbo.KUNDE.Maximalstand AS MaximumLevel, dbo.KUNDE.Iststand AS CurrentLevel, dbo.KUNDE.Bemerkung AS CustomerRemarks,
                         dbo.KUNDE.Bemerkung2 AS CustomerRemarks2, dbo.KUNDE.Bemerkung3 AS CustomerRemarks3, dbo.KUNDE.Abteilung AS CustomerDepartment,
                         dbo.KUNDE.EMail AS CustomerEmail, CAST(dbo.KUNDE.IstGesp AS tinyint) AS RentalAgmtIsBlocked, dbo.KUNDE.GespAbDat AS RentalAgmtBlockedDate,
                         dbo.BENUTZER.BenutzerNr AS UserNo, dbo.BENUTZER.Titel AS UserTitle, dbo.BENUTZER.Nachname AS UserSurname,
                         dbo.BENUTZER.Vorname AS UserFirstName, dbo.BENUTZER.GeburtsDat AS UserDateOfBirth, dbo.BENUTZER.StellplatzNr AS UserSpaceNo,
                         dbo.BENUTZER.Bemerkung AS UserRemarks, dbo.BENUTZER.Bemerkung2 AS UserRemarks2, dbo.BENUTZER.Bemerkung3 AS UserRemarks3,
                         dbo.BENUTZER.Abteilung AS UserDepartment, dbo.BENUTZER.EMail AS UserEmail, dbo.BENUTZER.IdentifikationsNr AS UserIdTag,
                         dbo.MVKARTE.ArtikNr AS ArticleNo, dbo.MVKARTE.VonDat AS CardValidFrom, dbo.MVKARTE.BisDat AS CardValidUntil,
                         dbo.MVKARTE.KarenziertVonDat AS SuspendPeriodFrom, dbo.MVKARTE.KarenziertBisDat AS SuspendPeriodUntil,
                         dbo.MVKARTE.ProdStatus AS CardProductionStatus, dbo.MVKARTE.ProdGrund AS CardProductionReason, CAST(dbo.MVKARTE.IstNeutral AS tinyint)
                         AS CardIsNeutral, CAST(dbo.MVKARTE.IstEinmalneutral AS tinyint) AS CardIsSingleNeutral, CAST(dbo.MVKARTE.IstGesp AS tinyint) AS CardIsBlocked,
                         dbo.MVKARTE.GespAbDat AS CardBlockedDate, dbo.MVKARTEBEWEGUNG.Restwert AS CardRemainingValue,
                         dbo.MVKARTEBEWEGUNG.WaehrungRestwert AS CurrencyRemainingValue, dbo.MVKARTE.Kartentyp AS CardType,
                         dbo.sd_mask_seriennummer(RTRIM(CONVERT(nvarchar(30), DecryptByKey(dbo.MVKARTE.SerienNr))), dbo.MVKARTE.Kartenart) AS SerialNo,
                         dbo.MVKARTE.Kartenart AS CardNoMask, dbo.ARTIK.ArtikBez AS ArticleDesig, dbo.ARTIK.ArtikKbez AS ArticleAbbr, dbo.SDSYSTXT.Txt AS ArticleCategory,
                         dbo.sd_cardno(dbo.MVKARTE.PhNr, dbo.MVKARTE.AnlageZtpkt, dbo.MVKARTE.KartenNr) AS CardNo,
                         dbo.MVKARTEBEWEGUNG.Bewegung AS MovementType, dbo.MVKARTEBEWEGUNG.AnlagenNr AS MovementFacilityNo,
                         dbo.MVKARTEBEWEGUNG.PhNr AS CarparkNo, dbo.MVKARTEBEWEGUNG.PhBez AS CarparkDesig, dbo.MVKARTEBEWEGUNG.GerNr AS DeviceNo,
                         dbo.MVKARTEBEWEGUNG.GerBez AS DeviceDesig, dbo.MVKARTEBEWEGUNG.Betrag AS Amount,
                         dbo.MVKARTEBEWEGUNG.WaehrungBetrag AS CurrencyOfAmount, dbo.MVKARTEBEWEGUNG.Txt AS AdditionalInfo,
                         SDSYSTXT_NATIONALITEAT.Txt AS Nationality, dbo.MVKARTEBEWEGUNG.Kartenwertart AS CardValueType,
                         dbo.MVKARTEBEWEGUNG.AbweisungsNr AS RejectionNo, SDSYSTXT_ABWEISUNG.Txt AS RejectionDesig, dbo.KUNDE.BuchhaltungsNr AS AccountingNo,
                         dbo.MVKARTEBEWEGUNG.VonZtpkt AS PaidFrom, SDSYSTXT_BEWEGUNG.Txt AS MovementTypeDesig, dbo.ANLAGE.AnlagenBez AS FacilityDesig,
                         dbo.ANLAGE.AnlagenKbez AS FacilityAbbr
FROM            dbo.PARAM AS PARAM_NATIONALITAET WITH (NOLOCK) INNER JOIN
                         dbo.SDSYSTXT AS SDSYSTXT_NATIONALITEAT WITH (NOLOCK) ON PARAM_NATIONALITAET.Sprache = SDSYSTXT_NATIONALITEAT.Sprache INNER JOIN
                         dbo.BENUTZER WITH (NOLOCK) INNER JOIN
                         dbo.KUNDE WITH (NOLOCK) ON dbo.BENUTZER.KundenNr = dbo.KUNDE.KundenNr AND dbo.BENUTZER.AnlagenNr = dbo.KUNDE.AnlagenNr INNER JOIN
                         dbo.MVKARTE WITH (NOLOCK) ON dbo.BENUTZER.BenutzerNr = dbo.MVKARTE.BenutzerNr AND
                         dbo.BENUTZER.AnlagenNr = dbo.MVKARTE.AnlagenNr INNER JOIN
                         dbo.ARTIK WITH (NOLOCK) ON dbo.MVKARTE.ArtikNr = dbo.ARTIK.ArtikNr INNER JOIN
                         dbo.KARTENARTIK WITH (NOLOCK) ON dbo.MVKARTE.ArtikNr = dbo.KARTENARTIK.ArtikNr INNER JOIN
                         dbo.SDSYSKART WITH (NOLOCK) ON dbo.KARTENARTIK.Syskart = dbo.SDSYSKART.Syskart INNER JOIN
                         dbo.SDSYSTXT WITH (NOLOCK) ON dbo.SDSYSKART.BezTxtCode = dbo.SDSYSTXT.TxtCode INNER JOIN
                         dbo.PARAM WITH (NOLOCK) ON dbo.SDSYSTXT.Sprache = dbo.PARAM.Sprache INNER JOIN
                         dbo.MVKARTEBEWEGUNG WITH (NOLOCK) ON dbo.MVKARTE.PhNr = dbo.MVKARTEBEWEGUNG.KartePhNr AND
                         dbo.MVKARTE.AnlageZtpkt = dbo.MVKARTEBEWEGUNG.KarteZtpkt AND dbo.MVKARTE.KartenNr = dbo.MVKARTEBEWEGUNG.KarteKartenNr INNER JOIN
                         dbo.SDSYSLAND WITH (NOLOCK) ON dbo.KUNDE.Landescode = dbo.SDSYSLAND.Landescode INNER JOIN
                         dbo.SDSYSLAND AS SDSYLAND_NATIONALITAET WITH (NOLOCK) ON dbo.KUNDE.Nationalitaet = SDSYLAND_NATIONALITAET.Landescode ON
                         SDSYSTXT_NATIONALITEAT.TxtCode = SDSYLAND_NATIONALITAET.BezTxtCode INNER JOIN
                         dbo.SDSYSABWEISUNG WITH (NOLOCK) ON dbo.MVKARTEBEWEGUNG.AbweisungsNr = dbo.SDSYSABWEISUNG.AbweisungsNr INNER JOIN
                         dbo.SDSYSTXT AS SDSYSTXT_ABWEISUNG WITH (NOLOCK) ON dbo.SDSYSABWEISUNG.TxtCodeLangTxt = SDSYSTXT_ABWEISUNG.TxtCode AND
                         dbo.PARAM.Sprache = SDSYSTXT_ABWEISUNG.Sprache INNER JOIN
                         dbo.SDSYSBEWEGUNG WITH (NOLOCK) ON dbo.MVKARTEBEWEGUNG.Bewegung = dbo.SDSYSBEWEGUNG.Bewegung INNER JOIN
                         dbo.SDSYSTXT AS SDSYSTXT_BEWEGUNG WITH (NOLOCK) ON dbo.SDSYSBEWEGUNG.BezTxtCode = SDSYSTXT_BEWEGUNG.TxtCode AND
                         dbo.PARAM.Sprache = SDSYSTXT_BEWEGUNG.Sprache INNER JOIN
                         dbo.ANLAGE WITH (NOLOCK) ON dbo.KUNDE.AnlagenNr = dbo.ANLAGE.AnlagenNr
WHERE        (dbo.MVKARTE.IstGeloescht = 0) AND (dbo.KUNDE.IstGeloescht = 0) AND (dbo.BENUTZER.IstGeloescht = 0) AND (dbo.MVKARTEBEWEGUNG.IstGeloescht = 0)
0
 

Author Comment

by:John-S Pretorius
ID: 39949256
To make it clear :- I created a SP 'Test' as below  which selects all columns from ContractParkerMovements (the View as described in my previous post) and I want to convert the Time column to a string so that I can see the milliseconds in Crystal reports - so far your convert seems adequate but I cannot seem to implement it properly in  my SP to keep everything ( ContractParkerMovements ) in tact and add the additional converted column.

ALTER PROCEDURE [dbo].[Test]
 
      -- Add the parameters for the stored procedure here
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

 select *  from dbo.ContractParkerMovements
 
END
0
 

Author Comment

by:John-S Pretorius
ID: 39949269
Thank you guys - The correct answer is :- (, *)

 select Convert(Varchar(24),ContractParkerMovements.Time,121) as strTime, *
from dbo.ContractParkerMovements
0
 

Author Comment

by:John-S Pretorius
ID: 39949295
I've requested that this question be closed as follows:

Accepted answer: 0 points for johnsp1234's comment #a39949269
Assisted answer: 150 points for IdoMillet's comment #a39948041
Assisted answer: 100 points for mlmcc's comment #a39948229

for the following reason:

Thank you - My apologies if I wasn't more clear
0
 
LVL 23

Expert Comment

by:Ido Millet
ID: 39949287
It is a bit strange that your definition of  "the correct answer" consists of the discovery that a comma separates columns and a * selects all columns in a SELECT statement.
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 375 total points
ID: 39949291
Replace this

dbo.MVKARTEBEWEGUNG.Ztpkt AS Time,
with

Convert(Varchar(24),dbo.MVKARTEBEWEGUNG.Ztpkt, 121) as Time,

mlmcc
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question