This blog is useful to all my friends who are working on the .Net Technology and wants to enhance their skills as well their problem solving ability.

Tuesday, July 19, 2016

Understand the Universal time-zone

Type DateTime

All date and time data is expressed as calendar dates (year, month, day) and a 24-hour day. Day and time data is always expressed in a combined format. No truncated formats are allowed. Dates are based on the Gregorian calendar. All date and time data is expressed numerically, leading zeros are preserved. Time can be expressed down to millisecond resolution, if necessary.
The basic type DateTime string pattern is yyyy-MM-ddTHH:mm:ss.sssZhhmm or date-element 'T' time-element 'Z' [zone-offset]. The character 'T' is arequired delimiter between the date and time sections. Times are expressed in the extended format: the ':' is used as a delimiter between hours, minutes and seconds. Fractional seconds are expressed with a decimal mark ('.' or ',') when needed.
 
 yyyy-MM-ddTHH:mm:ss.sssZ±hhmm
  • date-element = yyyy [year-element]
  • month-element = '-' MM [months-element]
  • day-element = '-' dd [days-element]
  • date-time delimiter = 'T'
  • time-element = HH [hours-element]
  • minute-element = ':' mm [minutes-element]
  • second-element = ':' ss [seconds-element]
  • fraction = ('.' | ',') digit+ [fraction]
  • zone-offset = 'Z' | (('+' | '-') HH [mm])
 

Type conflicts

All use of date and time data in Amazon MWS should be expressed as type DateTime. However, there are a few parameters that express date or time as a simple string. Check the parameter type definition to be sure of which type to use. Using the wrong type will generate an error.

Timezone considerations

Time data is always based on a 24-hour timekeeping system and Coordinated Universal Time (UTC). UTC is also known as Zulu (Z) time. The character 'Z' is expected as the UTC designator in the date time string.
Local timezones can be used, but must be expressed using a UTC offset. Offsets can be expressed as ±hh, where +hh is the local time (in hours) ahead of UTC and -hh is the local time behind UTC.
For example: Z+02 means UTC plus two hours ahead, and Z-04 means UTC minus four hours behind.
If no offset is specified, the API will assume UTC time, even if the 'Z' is missing.
While UTC can be expressed as Z+00, it is not required. The use of a terminal Z and no offset is permitted.

Time intervals

While time intervals and durations are allowed under ISO 8601, they are not currently used in Amazon MWS.

Example:

  2016-03-16T14:32:16.50Z-07
This example is read as the 16th day of March in 2016 at 14hrs 32min and 16.50 seconds UTC. The local time is 7 hours behind UTC.
 

Thursday, July 14, 2016

SQL Server Cross ref. Tables used in another database store procedures

----- Cross ref. Table used in another database



SELECT OBJECT_NAME (referencing_id) AS referencing_object, referenced_database_name,

referenced_schema_name, referenced_entity_name

FROM sys.sql_expression_dependencies

WHERE referenced_database_name ='XYZ' --- Target(Ref) DB

AND is_ambiguous = 0;

SQL Get List of Tables and SPs and Views Used/NotUsed of Database

 -- used XYZ DB views into PQR DB SPs


SELECT DISTINCT




--SP_Name = O.name,


Table_Name = OO.name

FROM sys.sysdepends D INNER JOIN sys.sysobjects O ON

O.id = D.id

INNER JOIN sys.sysobjects OO ON

OO.id = D.depid

WHERE O.xtype = 'P' And OO.xtype = 'V'



 

---- count of used sps in db



WITH T1




as


(


select SPECIFIC_NAME

from XYZ.information_schema.routines

where routine_type = 'PROCEDURE'

)

,



T2

as

(

SELECT o.name as SPECIFIC_NAME

FROM sys.dm_exec_procedure_stats ps

INNER JOIN

sys.objects o

ON ps.object_id = o.object_id

WHERE DB_NAME(ps.database_id) = 'XYZ' And O.type = 'P'

)

select *,COUNT (T1.SPECIFIC_NAME) OVER (PARTITION BY T1.SPECIFIC_NAME) from T1

LEFT JOIN T2 ON T1.SPECIFIC_NAME = T2.SPECIFIC_NAME





---- not used sp by execution time


select SPECIFIC_NAME

from XYZ.information_schema.routines

where routine_type = 'PROCEDURE'

And SPECIFIC_NAME not in




(


SELECT o.name as SPECIFIC_NAME

FROM sys.dm_exec_procedure_stats ps

INNER JOIN

sys.objects o

ON ps.object_id = o.object_id

WHERE DB_NAME(ps.database_id) = 'XYZ' And O.type = 'P'




)



----used sp by execution time

SELECT o.name,

ps.last_execution_time

FROM sys.dm_exec_procedure_stats ps

INNER JOIN

sys.objects o

ON ps.object_id = o.object_id

WHERE DB_NAME(ps.database_id) = 'XYZ' And O.type = 'P'

ORDER BY

ps.last_execution_time DESC




---- get name of sps


select *

from XYZ.information_schema.routines

where routine_type = 'PROCEDURE'



 

-- not used tables


SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='PQR'

And TABLE_NAME not in

(SELECT DISTINCT

Table_Name = OO.name

FROM sys.sysdepends D INNER JOIN sys.sysobjects O ON

O.id = D.id

INNER JOIN sys.sysobjects OO ON

OO.id = D.depid

WHERE O.xtype = 'P')




-- used tables


SELECT DISTINCT

SP_Name = O.name,

Table_Name = OO.name

FROM sys.sysdepends D INNER JOIN sys.sysobjects O ON

O.id = D.id

INNER JOIN sys.sysobjects OO ON

OO.id = D.depid

WHERE O.xtype = 'P' And OO.xtype = 'U'