Comparison of Date functions between SQL Server and MySQL

In our support life we all are working of multiple RDBMS at same. It's tough to remember all function available in different RDBMS. We try to run same query which was running in another RDBMS :). Here i am comparing the date function available in MS SQL Server and MySQL. Hope this will help.

SQL SERVER	MYSQL
DATEADD(day, 1, GETDATE())	DATE_ADD(NOW(), INTERVAL 1 DAY)
DATEDIFF(day, GETDATE(), GETDATE()-1)	DATEDIFF(NOW(), NOW() – INTERVAL 1 DAY)
DATENAME(month, GETDATE())	DATE_FORMAT(NOW(), ‘%M’) MONTHNAME(NOW())
DATENAME(weekday, GETDATE())	DATE_FORMAT(NOW(), ‘%W’) DAYNAME(NOW())
DATEPART(month, GETDATE())	DATE_FORMAT(NOW(), ‘%m’)
DAY(GETDATE())	DATE_FORMAT(NOW(), ‘%d’) DAY(NOW())) DAYOFMONTH(NOW())
GETDATE()	NOW() SYSDATE() CURRENT_TIMESTAMP CURRENT_TIMESTAMP()
GETDATE() + 1	NOW() + INTERVAL 1 DAY CURRENT_TIMESTAMP + INTERVAL 1 DAY
GETUTCDATE()	UTC_TIMESTAMP()
MONTH(GETDATE())	MONTH(NOW())
YEAR(GETDATE())	YEAR(NOW())

Some Useful SQL Server Commands

From last some time i am working with one of my SQL DBA friend. In this time i have learned something about SQL Server Also, 

Here are some of useful SQL DBA Commands. There all queries are using SQL Server DMV.

Get the information about CPU Consuming Queries

SELECT TOP ( 10 )
SUBSTRING(ST.text, ( QS.statement_start_offset / 2 ) + 1,
( ( CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset
END - QS.statement_start_offset ) / 2 ) + 1)
AS statement_text ,
execution_count ,
total_worker_time / 1000 AS total_worker_time_ms ,
( total_worker_time / 1000 ) / execution_count
AS avg_worker_time_ms ,
total_logical_reads ,
total_logical_reads / execution_count AS avg_logical_reads ,
total_elapsed_time / 1000 AS total_elapsed_time_ms ,
( total_elapsed_time / 1000 ) / execution_count
AS avg_elapsed_time_ms ,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC

Find Out Number of Logical CPU in System using SQL Server

SELECT cpu_countFROM sys.dm_os_sys_info
GO-- Identify Virtual Processors in for SQL Server 2000CREATE TABLE #TempTable([Index] VARCHAR(2000),[Name] VARCHAR(2000),[Internal_Value] VARCHAR(2000),[Character_Value] VARCHAR(2000)) ;INSERT INTO #TempTableEXEC xp_msver;SELECT Internal_Value AS VirtualCPUCountFROM #TempTableWHERE Name = 'ProcessorCount';DROP TABLE #TempTableGO

Recently Executed Query

SELECT
deqs.last_execution_time AS [Time], dest.TEXT AS [Query]

FROM sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

ORDER BY deqs.last_execution_time DESC

Query To Find Out Full Details of Databases 

SELECT database_id,CONVERT(VARCHAR(25), DB.name) AS dbName,CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],state_desc,
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB],
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB],user_access_desc AS [User access],recovery_model_desc AS [Recovery model],CASE compatibility_levelWHEN 60 THEN '60 (SQL Server 6.0)'WHEN 65 THEN '65 (SQL Server 6.5)'WHEN 70 THEN '70 (SQL Server 7.0)'WHEN 80 THEN '80 (SQL Server 2000)'WHEN 90 THEN '90 (SQL Server 2005)'WHEN 100 THEN '100 (SQL Server 2008)'END AS [compatibility level],CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],-- last backupISNULL((SELECT TOP 1CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +' (' + CAST(DATEDIFF(second, BK.backup_start_date,BK.backup_finish_date) AS VARCHAR(4)) + ' '+ 'seconds)'FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup],CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE '' END AS [fulltext],CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [autoclose],page_verify_option_desc AS [page verify option],CASE WHEN is_read_only = 1 THEN 'read only' ELSE '' END AS [read only],CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [autoshrink],CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [auto create statistics],CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [auto update statistics],CASE WHEN is_in_standby = 1 THEN 'standby' ELSE '' END AS [standby],CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE '' END AS [cleanly shutdown]FROM sys.databases DBORDER BY dbName, [Last backup] DESC, NAME

Hope these will also help you. :)

 

Useful MySQL command line Shortcuts and commands

In our daily MySQL Process we need to do some task repeatedly. Below are some useful  command line shortcut to work with MySQL. 

\? = help 

\c = clear command, It Will cancel last executed command. 

\C = switch to another charset, It will help if you want to use another character set. Make sure you are choosing correct charcter Set.

\d = set delimiter (use something else after a statement instead of ;), It will help whenever you are  creating function and procedure.

\g = send command to server , it will help to execute command , Will work as delimiter

\G = send command to server, Will work as delimiter , It will show resultset in grid format.

With \g and \G you can generate "vertical" output with each column value on a separate line. This makes large outputs far more readable !

\h = help, to See the help content and commands

\p = print current command

\q = quit MySQL
\r = reconnect (or see which database you're in)

\R = change your mysql prompt (personalize your prompt, which can be funny)

\s = show status (which can be very informative)

\t = don't write to into outfile
\T = write into outfile
\u = use database

\w = don't show warnings after every statement
\W = show warnings after every statement
\# = rebuild completion hash
\. = execute an SQL scriptfile

select database()= To know current database.

Enjoy Short cuts :)