Convert MSSQL date to MySQL date

Question

Convert MSSQL date to MySQL date

allan.madriaga

Old Hand

Points: 319
More actions
December 5, 2011 at 10:53 am

#264169

Hi, does anyone know how to convert a MSSQL date to MySQL date? MSSQL dates use '/' while MySQL use '-'. I am using an ODBC DSN in my SQL Server Reporting Services report and my query is returning nothing and I'm assuming it's due to the MSSQL date.
Thanks in advance!

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Does your query return anything outside of the report?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ben Teraberry SSChampion Points: 11845 More actions · Answer 2

It's actually safer not to use the separators anyway. '01-02-2011' does not inherently mean anything because it's not the ANSI standard way to express a date. Rather, it's a language specific format that's been accommodated by SQL Server. In some languages it represents MM-DD-YYYY and in others it represents DD-MM-YYYY. (So you can run the same exact query and get different results if the language setting of the connection is different.) However, if you use '20110102' (Jan 2, 2011), it will mean the same thing to the db regardless of the language settings because it is the accepted standard.

I'm not real familiar with MySQL or the specifics of what you're doing, but I would guess that using the ANSI standard would resolve the issues you're having.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 3

I assume you're using a linked server....can you post the SQL statement please?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

allan.madriaga Old Hand Points: 319 More actions · Answer 4

SQLRNNR,

My query selects the sum of a column which includes a where clause 'WHERE date BETWEEN @StartDate and @EndDate'. That query returns 0 but when I take out the parameters and change them to actual dates to something like '12-5-2011' it returns the correct sum.

allan.madriaga Old Hand Points: 319 More actions · Answer 5

Hi bteraberry, thank you, yes that is what I figured.

So now I need to convert a parameter, say @StartDate, to MySQL...Hmmm.

Lowell SSC Guru Points: 323518 More actions · Answer 6

MyDoggieJessie (12/5/2011)
I assume you're using a linked server....can you post the SQL statement please?

if you are using parameters, you are making sure the datatype of the parameters are datetimes and not varchars, right?

varchars could be converted into unexpected dates, like 01-07-2011 could be Jan 7 or July 1 depending on someones settings for DD-mm-YYYY vs MM-DD-YYYY.

assign the values as an ansi compatible date format, like 20010107 and let us know if that fixes the issue.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

allan.madriaga Old Hand Points: 319 More actions · Answer 7

MyDoggieJessie, no I'm not using a linked server however I should set one up and try the query there! I'll give it a shot.

My query within the SSRS report is below. My datasource in SSRS uses an ODBC connection with a MySQL driver.

SELECT SUM(Amount) AS Total

FROM test.records

WHERE (recordDateTime BETWEEN @StartDate AND @EndDate)

allan.madriaga Old Hand Points: 319 More actions · Answer 8

Hi Lowell, thanks for the response.

Yes, the parameters in the report are set to type date/time which is ANSI compatible. When I create a dataset in this report, the dataset returns a sum of 0. It's definitely a problem with the date and I'm stuck trying to convert the parameters to a MySQL friendly date/time.

Lowell SSC Guru Points: 323518 More actions · Answer 9

any chance teh report is returning results that create something like this, which would of course return no records?

WHERE (recordDateTime BETWEEN '2001-01-01' AND NULL)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

allan.madriaga Old Hand Points: 319 More actions · Answer 10

Hi Lowell, yes that goes through but returns 0 as expected.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

allan.madriaga (12/5/2011)
MyDoggieJessie, no I'm not using a linked server however I should set one up and try the query there! I'll give it a shot.
My query within the SSRS report is below. My datasource in SSRS uses an ODBC connection with a MySQL driver.
SELECT SUM(Amount) AS Total
FROM test.records
WHERE (recordDateTime BETWEEN @StartDate AND @EndDate)

What is the datatype of the recordDateTime field?

Also, run the exact same query against your mysql database with those parameters and make sure it returns something.

Set it up so you can instantiate the parameters as variables in your sql statement.

Running the query without the variables doesn't prove or disprove that the data format is incorrect.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jared SSC Guru Points: 61793 More actions · Answer 12

It has to be the data type of the MySql data, because the syntax '2010-01-01' works the same on both MySql and MSSQL.

Jared

Jared
CE - Microsoft

allan.madriaga Old Hand Points: 319 More actions · Answer 13

It definitely was a datatype mismatch with the dates but I managed to figure it out. I had to write my query as an expression:

="SELECT SUM(Amount) AS Total

FROM test.records

WHERE recordDateTime BETWEEN '" & Format(Parameters!StartDate.Value, "yyyy-MM-dd") & "' AND '" & Format(Parameters!EndDate.Value, "yyyy-MM-dd") & "'"

Thanks to everyone who responded!