Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Sort Date After Conversion To Varchar

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 Regards
Anouar

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 end
ORDER BY CONVERT(DATE, LeveringsDato) ASC


Jim




Everyday I learn something that somebody else already knew
Go to Top of Page

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 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
Go to Top of Page

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.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
Go to Top of Page

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-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
Go to Top of Page

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 Leveringsdato
FROM cte
ORDER BY Leveringsdato_tmp[/code]


- Lumbago
My blog-> http://thefirstsql.com
Go to Top of Page

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 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
Go to Top of Page

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]

- Lumbago
My blog-> http://thefirstsql.com
Go to Top of Page

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.

- Lumbago
My blog-> http://thefirstsql.com
Go to Top of Page

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 Leveringsdato
FROM cte
ORDER BY Leveringsdato_tmp


Best Regards
Anouar
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -