Please help with silly issue formatting date

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 2005 Forums

Transact-SQL (2005)

Please help with silly issue formatting date

Author

Topic

hztm2
Starting Member

16 Posts

Posted - 2010-08-03 : 03:41:37

I have a query that I run from VB.NET where I need to return data ordered by the date part of a smalldatetime field. I am currently using the following:

SELECT convert(varchar,AssignedTime,103) AS DT
FROM.....
GROUP BY convert(varchar,AssignedTime,103)
ORDER BY convert(varchar,AssignedTime,103)

Problem is date as varchar so order comes out as
01/06/2010
01/07/2010
02/06/2010

Is there any way I can get this to order correctly by date.

Many thanks for your assistance.

hztm2
Starting Member

16 Posts

Posted - 2010-08-03 : 04:25:34

Just in case this helps anybody else - I have found a way of doing this..

User convert(char(8),AssignedTime,112) AS DT

This produces:
20100601
20100602
20100701

This can then be formatted in code ( I am using VB.NET )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-03 : 04:28:13

why are you converting dates to varchar in sql? it can be very easily done in VB.NET using formatting functions. Always try not to change native datatype of fields if possible

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hztm2
Starting Member

16 Posts

Posted - 2010-08-03 : 04:41:22

Hi, the only reason for doing this is to allow us to group the data within the query rather than pulling the data in to code and arranging it there. The full query contains partitioning to group data and it just seemed easier to do all the grouping in the query. However, I do take on board what you saying and fully agree it is easier to keep things in their native form. Many thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-03 : 05:38:08

for grouping why should you convert to varchar? can you explain?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-03 : 05:39:55

When you use .NET, you should do formation there
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/29/understanding-datetime-column-part-iv.aspx

Madhivanan

Failing to plan is Planning to fail

hztm2
Starting Member

16 Posts

Posted - 2010-08-03 : 11:15:11

Hi visakh16 - now no laughing !!! We have a query something like this shown below. It is not good but it seems to work - however, any advice on how this should be done would be most welcome. Our aim is to combine two lists of data (timesheets), find the earliest entry each day for each name, and finally obtain the difference between the two dates AssignedTime and ArrivedTime. The date conversion allows us to obtain the data ordered by date without any times. I guess we could just pull the transactions in to VB.NET and manipulate the data here BUT this was a query that we inherited and thus have done nothing much with it. We are going to re-write everything shortly to use stored procedues but at present everything is executed from vb.net. Thanks for your interest and assistance.

SELECT convert(varchar,AssignedTime,103) AS DT, Operative, DATEDIFF(MINUTE, AssignedTime, ArrivedTime) AS TM
FROM
(SELECT *, ROW_NUMBER() OVER(PARTITION BY operative, convert(varchar,AssignedTime,103) ORDER BY AssignedTime) AS RowNum
FROM
(SELECT JobNo, AssignedTime, ArrivedTime , O.Name as Operative
FROM tblVisit V LEFT JOIN tblOperative O ON V.OperativeCode = O.Code
WHERE O.IsSpecial = 0 and O.IsSubcontractor=0 and V.Status='Complete'
AND AssignedTime>='" & DateFrom & " 00:00' AND AssignedTime<='" & DateTo & " 23:59'
UNION
SELECT 'Extra', TimeFrom as AssignedTime, TimeTo as ArrivedTime, Name as Operative FROM tblTimeSheetExtras
WHERE Status<>'Lunch' AND TimeFrom>'" & DateFrom & " 00:00' AND TimeFrom<='" & DateTo & " 23:59') as T) AS T2
WHERE RowNum =1 AND JobNo<>'Extra'
ORDER BY convert(varchar,AssignedTime,103), Operative

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-03 : 13:06:00

A couple of things..

1. If you are using SQL 2008 you can CAST your DATETIMEs to DATE to remove the time portion. If, 2005 (which I assume by the forum we are in) or before then you can use: DATEADD(DAY, DATEDIFF(DAY, 0, AssignedTime), 0)

2. The LEFT JOIN is really an INNER JOIN because of your WHERE clause. So, I'd change that to an INNER JOIN or move your WHERE clause predicates to the ON clause in the join.

3. I'm not 100% sure about how you are setting up this query from VB, but. I'd avoid using a <= on a date when you specify "23:59" as the time portion incase any values should fall into that last minute. I'd add one day to DateTo and do: TimeFrom < DATEADD(DAY, 1, DateTo)

hztm2
Starting Member

16 Posts

Posted - 2010-08-05 : 04:10:43

Thank you very much indeed for your feedback on this - I am going to look through and review all our queries - and spend more time learning advance SQL !! Best Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources