TSQL: Time in Military Format in 00:00 or 0000 format

Here is a way you can get time in desired military format. I need this to be in 0000 format.

For example If the current time is 12:35 AM, I need to be display this in 1235 in integer format or if you want with 12:35 you can use the following to fulfill your requirement.

DECLARE @time TABLE (
  id INT IDENTITY(1, 1),
  dt DATETIME)

INSERT INTO @time
VALUES ('7:34:00 PM')

-- time in 24 hour military format
SELECT  CONVERT(VARCHAR(5), dt, 114) AS FormatedDT
FROM   @time
-- time without colon in military format
SELECT  REPLACE(CONVERT(VARCHAR(5), dt, 114),':','') AS FormatedDT
FROM   @time

Hope this is useful!!!

Missing Send to items in windows 7

Recently I lost few of my icons went missing from Send to folder when we right click on any item or folder. I frequently use Mail Recipient in this. Here is how we can restore this back from default profile

1. Go to following path for default profile setting to capture

"C:\Users\Default\AppData\Roaming\Microsoft\Windows\SendTo" or any other profiles which you have access to from below locations
"C:\Windows\ServiceProfiles\LocalService\AppData\Roaming\Microsoft\Windows\SendTo"
"C:\Windows\ServiceProfiles\NetworkService\AppData\Roaming\Microsoft\Windows\SendTo"

2. Now go to run command  or type in start-search box and type shell:sendto to access your own profile.

3. Copy missing files from your default profile to your own profile. If you want you can add  your own short cuts for quick access.

You are good to go now. Hope this helps!!

TSQL: DateTime in different formats

Here is how you can convert get date in different formats

SELECT
GETDATE() AS GetDate,
CONVERT(VARCHAR,GETDATE(),0) AS '0',
CONVERT(VARCHAR,GETDATE(),100) AS '100',
CONVERT(VARCHAR,GETDATE(),1) AS '1',
CONVERT(VARCHAR,GETDATE(),101) AS '101',
CONVERT(VARCHAR,GETDATE(),7) AS '7',
CONVERT(VARCHAR,GETDATE(),107) AS '107',
CONVERT(VARCHAR,GETDATE(),108) AS '108',
CONVERT(VARCHAR,GETDATE(),108) AS '108',
CONVERT(VARCHAR,GETDATE(),10) AS '10',
CONVERT(VARCHAR,GETDATE(),110) AS '110',
CONVERT(VARCHAR,GETDATE(),12) AS '12',
CONVERT(VARCHAR,GETDATE(),112) AS '112',
CONVERT(VARCHAR,GETDATE(),14) AS '14',
CONVERT(VARCHAR,GETDATE(),114) AS '114'

You can refer this URL for other formatting options or follow below URL

Find more help  http://www.sql-server-helper.com/tips/date-formats.aspx

Find all Stored Procedures depending on a Table in Database

We can use sp_depends, sp_depends displays information about database object dependencies, such as the views and procedures that depend on a table or view, and the tables and views that are depended on by the view or procedure

But some times sp_depends does not always return accurate results.

Here is how you can get accurate results by using this query’s below

SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%TableName%'

Save results with headers in SQL Server Management Studio

I am working a lot with excel and CSVs for my SSIS packages these days. so I need to some input files as CSV files from SQL Server. Here is what I did in SQL Server Management Studio to get CSV files with Headers

1. Go to Tools in SQL Server Management Studio and click options.
2. Now click on Query Results and select SQL Server.
3. Now go to Results to Grid.
4. Select “Include column headers when copying or saving the results. (shown in below image)
5. Click OK
6. Restart SQL Server Management Studio and you are good to go now.

  Now open your query window and write you query and execute. On the results window right click and select  "save results as" and then save to CSV file which I can open in excel.