SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Sort Date After Conversion To Varchar
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Anouar
Starting Member

Denmark
8 Posts

Posted - 11/15/2012 :  06:38:06  Show Profile  Reply with Quote
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
2869 Posts

Posted - 11/15/2012 :  06:55:00  Show Profile  Reply with Quote
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

Denmark
8 Posts

Posted - 11/15/2012 :  07:27:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/15/2012 :  07:46:55  Show Profile  Reply with Quote
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

Denmark
8 Posts

Posted - 11/15/2012 :  08:08:18  Show Profile  Reply with Quote
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

Norway
3271 Posts

Posted - 11/15/2012 :  08:22:31  Show Profile  Reply with Quote
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
Go to Top of Page

Anouar
Starting Member

Denmark
8 Posts

Posted - 11/15/2012 :  08:35:52  Show Profile  Reply with Quote
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

Norway
3271 Posts

Posted - 11/15/2012 :  09:06:59  Show Profile  Reply with Quote
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
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 11/15/2012 :  09:11:41  Show Profile  Reply with Quote
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

Denmark
8 Posts

Posted - 11/15/2012 :  09:27:31  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 11/15/2012 :  10:11:51  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000