• 0

SQL Query Help


Question

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

  • 0

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

  • 0
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

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.