My SQL vs SQL

Question

My SQL vs SQL

sharonsql2013

SSCertifiable

Points: 5908
More actions
September 4, 2014 at 11:34 am

#310695

I am trying to validate some MY sql data with SQl.
However in MY sql we use CURdate function
So I calculate the difference in hours with that..
ISNULL(MAX( case when c.Closed = 0 then DATEDIFF(HOUR ,i.opened_at,CURdate()) else 0 end),0) AS MAxClose
It returns around 715 rows.
However , in SQl since we don't have CURdate , I convert it into yyyy-mm-dd format
ISNULL(MAX( case when c.closed = 0 then DATEDIFF(HOUR ,i.Opened,CONVERT(VARCHAR(10),GETDATE(), 120)) else 0 end),0) AS MAxClose
But my Numbers are off.
Can anyone advise.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Why are you converting a datetime (GETDATE()) into a string that will need to be converted again into a datetime data type?

Could you post your table definition with sample data?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 2

All I am trying to do is get a format which is like CURdate in SQL.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

sharonsql2013 (9/4/2014)
All I am trying to do is get a format which is like CURdate in SQL.

Why? You don't need to format a datetime, it's not a string. You can use GETDATE() directly without problems. Your data, however, might be the one giving you problems.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 4

Quick thought, CURDATE() returns the current date in either string or numerical format, the formats being as string 'YYYY-MM-DD' or in numerical if one adds 0 to the function (select curdate() + 0) YYYYMMDD. It does not return a compatible value to the SQL Server DATETIME data type, since there is no time part. DATEDIFF(HOUR ,i.opened_at,CURdate()) will then always return the hours from midnight. Similar statement in T-SQL would be DATEDIFF(HOUR,i.opened_at,CAST(GETDATE() AS DATE))

😎

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 5

So, Eirikur, if you used DATE instead of DATETIME would that also solve the issue? Just asking. I don't know MySQL from MyElbow.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 6

Grant Fritchey (9/5/2014)
So, Eirikur, if you used DATE instead of DATETIME would that also solve the issue? Just asking. I don't know MySQL from MyElbow.

It is more a question of the functions used to retrieve the current date or date and time. This can be somewhat confusing. In MySql/MariaDB there are 3 main "timestamp" functions:

CURDATE(), current date to the day.

NOW(), date and time to the second at the start of batch execution, deterministic in the scope of the batch.

SYSDATE(), date and time to the second at the time of execution, non-deterministic in the scope of the batch.

Data types

DATE, almost the same apart from the range of 1000-01-01 to 9999-12-31

DATETIME almost the same apart from the range is 1000-01-01 00:00:00 to 9999-12-31 23:59:59

TIMESTAMP, similar to SMALLDATETIME with the range of 1970-01-01 00:00:01 to 2038-01-19 03:14:07

😎