I have a rather large/complex query that has to work on both MySQL and MsSQL, all of the query is working other then this

date_format(date_sub(T_date, INTERVAL(extract(MINUTE from T_date)%1) MINUTE),'%m-%d-%Y %H:%i') as timekey

What this is doing is basically getting rid of the seconds of the date time does anyone know how to do this with MsSQL (btw the script will detect if its MsSQL or MySQL so it does not have to be one query i can have two different ones)

 

a simplified SQL im running is

SELECT date_format(date_sub(date, INTERVAL(extract(MINUTE from date)%1) MINUTE),'%m-%d-%Y %H:%i') as timekey, value FROM table GROUP BY timekey

Also on a side note the query could be handling millions of rows our local system has 146,000,000 rows in the db although the script should only be pulling a few hundred thousand at a time

Edited July 12, 201313 yr by D4Y0

will these pages help you http://linesofcode.net/snippets/45 or this http://www.w3schools.com/sql/func_convert.asp

Don't have much experience with MSSQL,

inlinedivblock, on 12 Jul 2013 - 10:09, said:

will these pages help you http://linesofcode.net/snippets/45 or this http://www.w3schools.com/sql/func_convert.asp

 

Don't have much experience with MSSQL,

That's what i tried originally but MsSQL does not like grouping by the converted date. But thanks anyway

Used your query to get it to work with one my tables, unfortunately i don't have a MSSQL environment to work on

Although i have found this page http://www.codeproject.com/Articles/566542/Date-and-Time-Data-Types-and-Functions-SQL-Server don't know if you have come across it already, looks pretty comprehensive and easy to read.

Edited July 12, 201313 yr by inlinedivblock

ah you can use the convert() function in the group by clause so the query would be

SELECT convert(datetime, date, 100), value FROM table GROUP BY convert(datetime, date, 100)

yay problem solved, celebrate with another tea i think

yay problem solved, celebrate with another tea i think

not a fan of hot drinks, will be an ice cold can of coke and a packet of skittles XD

haha you're going to be buzzing

A small selection of goodied ive got to eat

Awesome, my doctor will be like there should be fruit in there......

looks like we are ordering dominos here at work, im going to feel so guilty later, the wife is not giong to approve of me eating junk lol

inlinedivblock, on 12 Jul 2013 - 11:48, said:

Awesome, my doctor will be like there should be fruit in there......

 

looks like we are ordering dominos here at work, im going to feel so guilty later, the wife is not giong to approve of me eating junk lol

were down the pub today, and my awnser to your doctor would be ... i have strawberry laces

were down the pub today, and my awnser to your doctor would be ... i have strawberry laces

 

That is the best answer to a doctor ever... mainly because I love strawberry laces

you bad bad bad people lol,,,, im waiting on the dominos mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm

i had steak and chips for dinner, just a little bit more healthy

really i am so stuffed, i need to take a nap!!! i actually feel sick, too much BBQ pizza