Author |
Topic |
Anouar
Starting Member
8 Posts |
Posted - 2012-11-15 : 06:38:06
|
Hi all,I have a problem. I need the dates of my query to be converted to VarChar, for a nicer presentation. And the date format should be dd-mm-yyyy. However the conversion is good, but when I sort it by Order By, the dates are not in right order. Here is my query:SELECT DISTINCT tblKatalog.ID, VersionsNummer AS Version, tblOrdrer.TilLånerNr, LEFT(Titel, 25) AS Titel, CONVERT(VARCHAR(8), TryktMatAnkommet, 5) AS Ankomstdato, CONVERT(VARCHAR(8), dbo.funLeveringsdag(TryktMatAnkommet, Versionsnummer, FaktSider, tblKatalog.Id), 5) AS Leveringsdato, Bestillingsdato FROM tblKatalog INNER JOIN tblMaterialeriProduktion ON tblMaterialeriProduktion.ID = tblKatalog.ID INNER JOIN tblMatKøb ON tblMatKøb.Indeks_i_MiP = tblMaterialeriProduktion.Indeks INNER JOIN tblOrdrer ON tblOrdrer.Indeks_i_Mip = tblMaterialerIPRoduktion.Indeks WHERE VersionsNummer IN (4, 6, 7, 8, 21, 23) AND TryktMatAnkommet IS NOT NULL AND tblMaterialerIProduktion.blnSlettet = 0 AND OrdreType <> 'Kopi' ORDER BY LeveringsDato ASC Best RegardsAnouar |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-15 : 06:55:00
|
Convert it back to a date. This is another reason why formatting should be done in the front endORDER BY CONVERT(DATE, LeveringsDato) ASCJimEveryday I learn something that somebody else already knew |
|
|
Anouar
Starting Member
8 Posts |
Posted - 2012-11-15 : 07:27:17
|
If I add the line CONVERT(DATE, Leveringsdato) in the Order By clause I get an error.Msg 145, Level 15, State 1, Line 1ORDER BY items must appear in the select list if SELECT DISTINCT is specified.SELECT DISTINCT tblKatalog.ID, VersionsNummer AS Version, tblOrdrer.TilLånerNr, LEFT(Titel, 25) AS Titel, CONVERT(VARCHAR(8), TryktMatAnkommet, 5) AS Ankomstdato, CONVERT(VARCHAR(8), dbo.funLeveringsdag(TryktMatAnkommet, Versionsnummer, FaktSider, tblKatalog.Id), 5) AS Leveringsdato, CONVERT(VARCHAR(8), Bestillingsdato, 5) FROM tblKatalog INNER JOIN tblMaterialeriProduktion ON tblMaterialeriProduktion.ID = tblKatalog.ID INNER JOIN tblMatKøb ON tblMatKøb.Indeks_i_MiP = tblMaterialeriProduktion.Indeks INNER JOIN tblOrdrer ON tblOrdrer.Indeks_i_Mip = tblMaterialerIPRoduktion.Indeks WHERE VersionsNummer IN (4, 6, 7, 8, 21, 23) AND TryktMatAnkommet IS NOT NULL AND tblMaterialerIProduktion.blnSlettet = 0 AND OrdreType <> 'Kopi' ORDER BY CONVERT(DATE, LeveringsDato) ASCBest RegardsAnouar |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-15 : 07:46:55
|
Use GROUP BY instead of DISTINCT SELECT tblKatalog.ID, VersionsNummer AS Version, tblOrdrer.TilLånerNr, LEFT(Titel, 25) AS Titel, CONVERT(VARCHAR(8), TryktMatAnkommet, 5) AS Ankomstdato, CONVERT(VARCHAR(8), dbo.funLeveringsdag(TryktMatAnkommet, Versionsnummer, FaktSider, tblKatalog.Id), 5) AS Leveringsdato, CONVERT(VARCHAR(8), Bestillingsdato, 5)FROM tblKatalog INNER JOIN tblMaterialeriProduktion ON tblMaterialeriProduktion.ID = tblKatalog.ID INNER JOIN tblMatKøb ON tblMatKøb.Indeks_i_MiP = tblMaterialeriProduktion.Indeks INNER JOIN tblOrdrer ON tblOrdrer.Indeks_i_Mip = tblMaterialerIPRoduktion.IndeksWHERE VersionsNummer IN (4, 6, 7, 8, 21, 23) AND TryktMatAnkommet IS NOT NULL AND tblMaterialerIProduktion.blnSlettet = 0 AND OrdreType <> 'Kopi'GROUP BY <everything in the select list>ORDER BY CONVERT(DATE, LeveringsDato) ASCJimEveryday I learn something that somebody else already knew |
|
|
Anouar
Starting Member
8 Posts |
Posted - 2012-11-15 : 08:08:18
|
I've done exactly what you said, and it worked. However the date of Leveringsdato is not right in the order, here is what I got from the query:19-11-1223-11-1222-11-1222-11-1229-11-1229-11-1229-11-1228-11-1228-11-12SELECT tblKatalog.ID, VersionsNummer AS Version, tblOrdrer.TilLånerNr, LEFT(Titel, 25) AS Titel, CONVERT(VARCHAR(8), TryktMatAnkommet, 5) AS Ankomstdato, CONVERT(VARCHAR(8), dbo.funLeveringsdag(TryktMatAnkommet, Versionsnummer, FaktSider, tblKatalog.Id), 5) AS Leveringsdato, CONVERT(VARCHAR(8), Bestillingsdato, 5) FROM tblKatalog INNER JOIN tblMaterialeriProduktion ON tblMaterialeriProduktion.ID = tblKatalog.ID INNER JOIN tblMatKøb ON tblMatKøb.Indeks_i_MiP = tblMaterialeriProduktion.Indeks INNER JOIN tblOrdrer ON tblOrdrer.Indeks_i_Mip = tblMaterialerIPRoduktion.Indeks WHERE VersionsNummer IN (4, 6, 7, 8, 21, 23) AND FærdigMelding IS NULL AND TryktMatAnkommet IS NOT NULL AND tblMaterialerIProduktion.blnSlettet = 0 AND OrdreType <> 'Kopi' GROUP BY tblKatalog.ID, VersionsNummer, FaktSider, tblOrdrer.TilLånerNr, Titel, TryktMatAnkommet, LeveringsDato, BestillingsDato ORDER BY CONVERT(DATE, Leveringsdato) ASC Best RegardsAnouar |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2012-11-15 : 08:22:31
|
[code]WITH cte AS ( SELECT DISTINCT tblKatalog.ID, VersionsNummer AS Version, tblOrdrer.TilLånerNr, LEFT(Titel, 25) AS Titel, CONVERT(VARCHAR(8), TryktMatAnkommet, 5) AS Ankomstdato, dbo.funLeveringsdag(TryktMatAnkommet, Versionsnummer, FaktSider, tblKatalog.Id) AS Leveringsdato_tmp, CONVERT(VARCHAR(8), Bestillingsdato, 5) FROM tblKatalog INNER JOIN tblMaterialeriProduktion ON tblMaterialeriProduktion.ID = tblKatalog.ID INNER JOIN tblMatKøb ON tblMatKøb.Indeks_i_MiP = tblMaterialeriProduktion.Indeks INNER JOIN tblOrdrer ON tblOrdrer.Indeks_i_Mip = tblMaterialerIPRoduktion.Indeks WHERE VersionsNummer IN (4, 6, 7, 8, 21, 23) AND TryktMatAnkommet IS NOT NULL AND tblMaterialerIProduktion.blnSlettet = 0 AND OrdreType <> 'Kopi' )SELECT [list of columns], CONVERT(varchar(8), Leveringsdato_tmp, 5) AS LeveringsdatoFROM cteORDER BY Leveringsdato_tmp[/code]- LumbagoMy blog-> http://thefirstsql.com |
|
|
Anouar
Starting Member
8 Posts |
Posted - 2012-11-15 : 08:35:52
|
Here is what I did, but I get an error message...Msg 8155, Level 16, State 2, Line 1No column name was specified for column 7 of 'cte'. WITH cte AS ( SELECT DISTINCT tblKatalog.ID, VersionsNummer AS Version, tblOrdrer.TilLånerNr, LEFT(Titel, 25) AS Titel, CONVERT(VARCHAR(8), TryktMatAnkommet, 5) AS Ankomstdato, dbo.funLeveringsdag(TryktMatAnkommet, Versionsnummer, FaktSider, tblKatalog.Id) AS Leveringsdato_tmp, CONVERT(VARCHAR(8), Bestillingsdato, 5) FROM tblKatalog INNER JOIN tblMaterialeriProduktion ON tblMaterialeriProduktion.ID = tblKatalog.ID INNER JOIN tblMatKøb ON tblMatKøb.Indeks_i_MiP = tblMaterialeriProduktion.Indeks INNER JOIN tblOrdrer ON tblOrdrer.Indeks_i_Mip = tblMaterialerIPRoduktion.Indeks WHERE VersionsNummer IN (4, 6, 7, 8, 21, 23) AND TryktMatAnkommet IS NOT NULL AND tblMaterialerIProduktion.blnSlettet = 0 AND OrdreType <> 'Kopi' )SELECT tblKatalog.Id, VersionsNummer, tblOrdrer.TilLånerNr, Titel, TryktMatAnkommet, Leveringsdato, Bestillingsdato, CONVERT(varchar(8), Leveringsdato_tmp, 5) AS LeveringsdatoFROM cteORDER BY Leveringsdato_tmp Best RegardsAnouar |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2012-11-15 : 09:06:59
|
[code]WITH cte AS ( SELECT DISTINCT tblKatalog.ID, VersionsNummer AS Version, tblOrdrer.TilLånerNr, LEFT(Titel, 25) AS Titel, CONVERT(VARCHAR(8), TryktMatAnkommet, 5) AS Ankomstdato, dbo.funLeveringsdag(TryktMatAnkommet, Versionsnummer, FaktSider, tblKatalog.Id) AS Leveringsdato_tmp, CONVERT(VARCHAR(8), Bestillingsdato, 5) AS Bestillingsdato FROM tblKatalog ...[/code]- LumbagoMy blog-> http://thefirstsql.com |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2012-11-15 : 09:11:41
|
The cte is like a view you create on the fly and all columns have to have a name. After you have created the cte (common table expression) you can query it like a normal table nut of course only the columns that have been specified. I see that you specified "Leveringsdato" in the outer select statement but since only "Leveringsdato_tmp" exists inside the cte you will get an error here as well. Either replace the "Leveringsdato" with "Leveringsdato_tmp" in the outer query or just omit it since we generate the converted "Leveringsdato" in the proper format in the last column.- LumbagoMy blog-> http://thefirstsql.com |
|
|
Anouar
Starting Member
8 Posts |
Posted - 2012-11-15 : 09:27:31
|
I had to do some modifications, but now it works like a charm. Thank you all for the contributions. This saved my day!Here is what I ended up with:WITH cte AS ( SELECT DISTINCT tblKatalog.ID AS KatalogID, VersionsNummer AS [Version], tblOrdrer.TilLånerNr AS Lånernummer, LEFT(Titel, 25) AS Titel, CONVERT(VARCHAR(8), TryktMatAnkommet, 5) AS Ankomstdato, CONVERT(VARCHAR(8), dbo.funLeveringsdag(TryktMatAnkommet, Versionsnummer, FaktSider, tblKatalog.Id), 5) AS Leveringsdato_tmp, CONVERT(VARCHAR(8), Bestillingsdato, 5) AS Bestillingsdato FROM tblKatalog INNER JOIN tblMaterialeriProduktion ON tblMaterialeriProduktion.ID = tblKatalog.ID INNER JOIN tblMatKøb ON tblMatKøb.Indeks_i_MiP = tblMaterialeriProduktion.Indeks INNER JOIN tblOrdrer ON tblOrdrer.Indeks_i_Mip = tblMaterialerIPRoduktion.Indeks WHERE VersionsNummer IN (4, 6, 7, 8, 21, 23) AND FærdigMelding IS NULL AND TryktMatAnkommet IS NOT NULL AND tblMaterialerIProduktion.blnSlettet = 0 AND OrdreType <> 'Kopi' )SELECT KatalogID, [Version], Lånernummer, Titel, Ankomstdato, Leveringsdato_tmp, Bestillingsdato, CONVERT(varchar(8), Leveringsdato_tmp, 5) AS LeveringsdatoFROM cteORDER BY Leveringsdato_tmp Best RegardsAnouar |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-15 : 10:11:51
|
Out of curiosity, why is it not possible to do the date formatting at front end? and return date values as date itself from the query?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|