wilfried Posted September 20, 2009 Share Posted September 20, 2009 I need some help with a SQL query... This query returns the date: (like this: 2009-09-20 09:33:07.833) SELECT * FROM max.dbo.vGETDATE I then need to get all records from the max.dbo.Tickets table that 'startDate' match the first 10 digit of the above results, and sum the column 'ticketTotal' of these records... a sample of the table is like this: startDate ticketTotal 2009-09-20 09:33:07.833 125.32 2009-09-19 03:34:52.547 145.47 2009-09-20 10:03:17.253 178.02 Any help building this would be appreciated ! Link to comment Share on other sites More sharing options...
0 BGM Posted September 20, 2009 Share Posted September 20, 2009 MS SQL? to trim off the timestamp on a date, i usually convert it to a varchar, and then back to a datetime select GETDATE() --2009-09-20 18:54:24.850 select CONVERT(varchar,getdate()) --Sep 20 2009 6:54PM select CONVERT(varchar,getdate(),106) --20 Sep 2009 select convert(datetime,CONVERT(varchar,getdate(),106)) --2009-09-20 00:00:00.000 you can then easily do a comparison! :) Link to comment Share on other sites More sharing options...
0 GSDragoon Posted September 20, 2009 Share Posted September 20, 2009 http://msdn.microsoft.com/en-us/library/ms187928.aspx You can also use cast to bring back just the date (ignoring the time). Link to comment Share on other sites More sharing options...
0 Nyck Posted September 21, 2009 Share Posted September 21, 2009 I need some help with a SQL query...This query returns the date: (like this: 2009-09-20 09:33:07.833) SELECT * FROM max.dbo.vGETDATE I then need to get all records from the max.dbo.Tickets table that 'startDate' match the first 10 digit of the above results, and sum the column 'ticketTotal' of these records... a sample of the table is like this: startDate ticketTotal 2009-09-20 09:33:07.833 125.32 2009-09-19 03:34:52.547 145.47 2009-09-20 10:03:17.253 178.02 Any help building this would be appreciated ! As the others said, use CONVERT to parse out just the date... CONVERT(VARCHAR, [datefieldhere], 101) --gets the date Now depending on what you are doing, to get the ticket total from the first query, it would look something like this: SELECT CONVERT(VARCHAR, startDate, 101) [date], SUM(ticketTotal) [sum] FROM max.dbo.Tickets WHERE CONVERT(VARCHAR, startDate, 101) IN (SELECT CONVERT(VARCHAR, [datefield], 101) FROM max.dbo.vGETDATE) GROUP BY CONVERT(VARCHAR, startDate, 101) Now, this isn't the most efficient method of doing this, but it should work. I would be curious to know what other fields are in your vGETDATE. Link to comment Share on other sites More sharing options...
Question
wilfried
I need some help with a SQL query...
This query returns the date: (like this: 2009-09-20 09:33:07.833)
SELECT * FROM max.dbo.vGETDATE
I then need to get all records from the max.dbo.Tickets table that 'startDate' match the first 10 digit of the above results,
and sum the column 'ticketTotal' of these records...
a sample of the table is like this:
startDate ticketTotal
2009-09-20 09:33:07.833 125.32
2009-09-19 03:34:52.547 145.47
2009-09-20 10:03:17.253 178.02
Any help building this would be appreciated !
Link to comment
Share on other sites
3 answers to this question
Recommended Posts