| Author |
 Topic  |
|
|
Anouar
Starting Member
Denmark
8 Posts |
 Posted - 11/15/2012 : 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 Regards
Anouar |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/15/2012 : 06:55:00
|
Convert it back to a date
. This is another reason why formatting should be done in the front end
ORDER BY CONVERT(DATE, LeveringsDato) ASC
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
Anouar
Starting Member
Denmark
8 Posts |
Posted - 11/15/2012 : 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 1
ORDER 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) ASC
Best Regards
Anouar |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/15/2012 : 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.Indeks
WHERE 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) ASC
Jim
Everyday I learn something that somebody else already knew |
|
|
|
Anouar
Starting Member
Denmark
8 Posts |
Posted - 11/15/2012 : 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-12
23-11-12
22-11-12
22-11-12
29-11-12
29-11-12
29-11-12
28-11-12
28-11-12
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.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 Regards
Anouar |
|
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
Posted - 11/15/2012 : 08:22:31
|
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 Leveringsdato
FROM cte
ORDER BY Leveringsdato_tmp
- Lumbago
My blog-> http://thefirstsql.com |
|
|
|
Anouar
Starting Member
Denmark
8 Posts |
Posted - 11/15/2012 : 08:35:52
|
Here is what I did, but I get an error message...
Msg 8155, Level 16, State 2, Line 1
No 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 Leveringsdato
FROM cte
ORDER BY Leveringsdato_tmp
Best Regards
Anouar |
|
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
Posted - 11/15/2012 : 09:06:59
|
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
...
- Lumbago
My blog-> http://thefirstsql.com |
|
|
|
Lumbago
Norsk Yak Master
Norway
3241 Posts |
Posted - 11/15/2012 : 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.
- Lumbago
My blog-> http://thefirstsql.com |
|
|
|
Anouar
Starting Member
Denmark
8 Posts |
Posted - 11/15/2012 : 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 Leveringsdato
FROM cte
ORDER BY Leveringsdato_tmp
Best Regards
Anouar |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47154 Posts |
Posted - 11/15/2012 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Sort Date After Conversion To Varchar
