John-S Pretorius
asked on
Show milliseconds from SQL in Crystal Reports
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.
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.
Use a Command, SQL Expression, SP, or a VIEW to Convert or Cast the DateTime into a String.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.ContractParkerMovement s table.
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.ContractParkerMovement
It should be something like
SELECT Convert(Varchar(24),Contra ctParkerMo vements.Ti me,121) as strTime
FROM ContractParkerMovements
mlmcc
SELECT Convert(Varchar(24),Contra
FROM ContractParkerMovements
mlmcc
ASKER
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.
That column expression is in addition to all your other columns. Not instead of.
ASKER
If I run(Execute) your statement (which is within a Stored Procedure)
SELECT Convert(Varchar(24),Contra ctParkerMo vements.Ti me,121) as strTime
FROM ContractParkerMovements
I just get 1 table :- strTime as a result
SELECT Convert(Varchar(24),Contra
FROM ContractParkerMovements
I just get 1 table :- strTime as a result
Your SP already has a SELECT statement with several columns. Add just the expression (without the SELECT and the FROM) as an additional column.
ASKER
My SP is based on an already complex built View (ContractParkerMovements): - (See below) so when I run your command :- Convert(Varchar(24),Contra ctParkerMo vements.Ti me,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.Landeskennun g3 AS Country,
dbo.KUNDE.SteuerNr AS TaxCode, dbo.KUNDE.AusweisNr AS IdDocumentNo, dbo.KUNDE.Telefon AS Telephone, dbo.KUNDE.VertragsNr AS RentalAgmtNo,
dbo.KUNDE.VertragsbeginnDa t 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.Identifikatio nsNr AS UserIdTag,
dbo.MVKARTE.ArtikNr AS ArticleNo, dbo.MVKARTE.VonDat AS CardValidFrom, dbo.MVKARTE.BisDat AS CardValidUntil,
dbo.MVKARTE.KarenziertVonD at AS SuspendPeriodFrom, dbo.MVKARTE.KarenziertBisD at AS SuspendPeriodUntil,
dbo.MVKARTE.ProdStatus AS CardProductionStatus, dbo.MVKARTE.ProdGrund AS CardProductionReason, CAST(dbo.MVKARTE.IstNeutra l AS tinyint)
AS CardIsNeutral, CAST(dbo.MVKARTE.IstEinmal neutral AS tinyint) AS CardIsSingleNeutral, CAST(dbo.MVKARTE.IstGesp AS tinyint) AS CardIsBlocked,
dbo.MVKARTE.GespAbDat AS CardBlockedDate, dbo.MVKARTEBEWEGUNG.Restwe rt AS CardRemainingValue,
dbo.MVKARTEBEWEGUNG.Waehru ngRestwert AS CurrencyRemainingValue, dbo.MVKARTE.Kartentyp AS CardType,
dbo.sd_mask_seriennummer(R TRIM(CONVE RT(nvarcha r(30), DecryptByKey(dbo.MVKARTE.S erienNr))) , 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.Bewegu ng AS MovementType, dbo.MVKARTEBEWEGUNG.Anlage nNr 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.Waehru ngBetrag AS CurrencyOfAmount, dbo.MVKARTEBEWEGUNG.Txt AS AdditionalInfo,
SDSYSTXT_NATIONALITEAT.Txt AS Nationality, dbo.MVKARTEBEWEGUNG.Karten wertart AS CardValueType,
dbo.MVKARTEBEWEGUNG.Abweis ungsNr AS RejectionNo, SDSYSTXT_ABWEISUNG.Txt AS RejectionDesig, dbo.KUNDE.BuchhaltungsNr AS AccountingNo,
dbo.MVKARTEBEWEGUNG.VonZtp kt 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.Sprach e = SDSYSTXT_NATIONALITEAT.Spr ache 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.KarteP hNr AND
dbo.MVKARTE.AnlageZtpkt = dbo.MVKARTEBEWEGUNG.KarteZ tpkt AND dbo.MVKARTE.KartenNr = dbo.MVKARTEBEWEGUNG.KarteK artenNr 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.Lan descode ON
SDSYSTXT_NATIONALITEAT.Txt Code = SDSYLAND_NATIONALITAET.Bez TxtCode INNER JOIN
dbo.SDSYSABWEISUNG WITH (NOLOCK) ON dbo.MVKARTEBEWEGUNG.Abweis ungsNr = dbo.SDSYSABWEISUNG.Abweisu ngsNr INNER JOIN
dbo.SDSYSTXT AS SDSYSTXT_ABWEISUNG WITH (NOLOCK) ON dbo.SDSYSABWEISUNG.TxtCode LangTxt = SDSYSTXT_ABWEISUNG.TxtCode AND
dbo.PARAM.Sprache = SDSYSTXT_ABWEISUNG.Sprache INNER JOIN
dbo.SDSYSBEWEGUNG WITH (NOLOCK) ON dbo.MVKARTEBEWEGUNG.Bewegu ng = dbo.SDSYSBEWEGUNG.Bewegung INNER JOIN
dbo.SDSYSTXT AS SDSYSTXT_BEWEGUNG WITH (NOLOCK) ON dbo.SDSYSBEWEGUNG.BezTxtCo de = 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.IstGe loescht = 0)
--------------------------
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.Landeskennun
dbo.KUNDE.SteuerNr AS TaxCode, dbo.KUNDE.AusweisNr AS IdDocumentNo, dbo.KUNDE.Telefon AS Telephone, dbo.KUNDE.VertragsNr AS RentalAgmtNo,
dbo.KUNDE.VertragsbeginnDa
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.Identifikatio
dbo.MVKARTE.ArtikNr AS ArticleNo, dbo.MVKARTE.VonDat AS CardValidFrom, dbo.MVKARTE.BisDat AS CardValidUntil,
dbo.MVKARTE.KarenziertVonD
dbo.MVKARTE.ProdStatus AS CardProductionStatus, dbo.MVKARTE.ProdGrund AS CardProductionReason, CAST(dbo.MVKARTE.IstNeutra
AS CardIsNeutral, CAST(dbo.MVKARTE.IstEinmal
dbo.MVKARTE.GespAbDat AS CardBlockedDate, dbo.MVKARTEBEWEGUNG.Restwe
dbo.MVKARTEBEWEGUNG.Waehru
dbo.sd_mask_seriennummer(R
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.
dbo.MVKARTEBEWEGUNG.Bewegu
dbo.MVKARTEBEWEGUNG.PhNr AS CarparkNo, dbo.MVKARTEBEWEGUNG.PhBez AS CarparkDesig, dbo.MVKARTEBEWEGUNG.GerNr AS DeviceNo,
dbo.MVKARTEBEWEGUNG.GerBez
dbo.MVKARTEBEWEGUNG.Waehru
SDSYSTXT_NATIONALITEAT.Txt
dbo.MVKARTEBEWEGUNG.Abweis
dbo.MVKARTEBEWEGUNG.VonZtp
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.Sprach
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.KarteP
dbo.MVKARTE.AnlageZtpkt = dbo.MVKARTEBEWEGUNG.KarteZ
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.Lan
SDSYSTXT_NATIONALITEAT.Txt
dbo.SDSYSABWEISUNG WITH (NOLOCK) ON dbo.MVKARTEBEWEGUNG.Abweis
dbo.SDSYSTXT AS SDSYSTXT_ABWEISUNG WITH (NOLOCK) ON dbo.SDSYSABWEISUNG.TxtCode
dbo.PARAM.Sprache = SDSYSTXT_ABWEISUNG.Sprache
dbo.SDSYSBEWEGUNG WITH (NOLOCK) ON dbo.MVKARTEBEWEGUNG.Bewegu
dbo.SDSYSTXT AS SDSYSTXT_BEWEGUNG WITH (NOLOCK) ON dbo.SDSYSBEWEGUNG.BezTxtCo
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
ASKER
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.ContractParkerMovement s
END
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.ContractParkerMovement
END
ASKER
Thank you guys - The correct answer is :- (, *)
select Convert(Varchar(24),Contra ctParkerMo vements.Ti me,121) as strTime, *
from dbo.ContractParkerMovement s
select Convert(Varchar(24),Contra
from dbo.ContractParkerMovement
ASKER
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
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
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.