SQL Cast Date

Using SQL Server 2008 This first option of removing the date uses the SQL Server 2008 method. This will return only the date or only the time: [cc lang=”sql”] — remove the time SELECT CAST(GETDATE() AS date) — 2009-07-12 — remove the date SELECT CAST(GETDATE() AS time) — 08:46:25.8130000 [/cc] If you’re not working with SQL Server 2008, you have to improvise.   Cast Date With No Time Using Floor This example removes the time from the date time by setting it to the beginning of the day. [cc lang=”sql”] — Get the current day with no time — 2009-06-29 00:00:00.000 SELECT CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime) — Get the next day — 2009-06-30 00:00:00.000 SELECT CAST(CEILING (CAST(GETDATE() AS float)) AS datetime) [/cc] Cast Date with No Time Using Convert Using convert you can remove the time for display purposes, usually for reporting or the front end. [cc lang=”sql”] — Month first SELECT CONVERT(varchar(12),GETDATE(), 101) — 06/29/2009 SELECT CONVERT(varchar(12),GETDATE(), 110) — 06-29-2009 SELECT CONVERT(varchar(12),GETDATE(), 100) — Jun 29 2009 SELECT CONVERT(varchar(12),GETDATE(), 107) — Jun 29, 2009 — Year first SELECT CONVERT(varchar(12),GETDATE(), 102) — 2009.06.29 SELECT CONVERT(varchar(12),GETDATE(), 111) — 2009/06/29 SELECT CONVERT(varchar(12),GETDATE(), 112) — 20090629 — Day first SELECT CONVERT(varchar(12),GETDATE(), 103) — 29/06/2009 SELECT CONVERT(varchar(12),GETDATE(), 105) — 29-06-2009 SELECT CONVERT(varchar(12),GETDATE(), 104) — 29.06.2009 SELECT CONVERT(varchar(12),GETDATE(), 106) — 29 Jun 2009 — Time only SELECT CONVERT(varchar(12),GETDATE(), 108) — 07:26:16 SELECT CONVERT(varchar(12),GETDATE(), 114) — 07:27:11:203 [/cc] Roll Your Own Date To roll your own date format, use the datename function. This is also […]

Continue reading ...

Insert Carriage Return Line Feed to String

Here is a clean and efficient way to embed carriage returns into a string. I prefer this way instead of concatenating the entire CHAR(13)+CHAR(10) to the end of every string. This replace function also comes in handy in most instances where you need to append strings. [cc lang=”sql”] declare @Note varchar (200) SET @Note = ‘Line One.[crlf];Line Two[crlf]Line Three.’ SET @Note = REPLACE(@Note,'[crlf]’,CHAR(13)+CHAR(10)) PRINT @Note [/cc] Output: [code] Line One. Line Two. Line Three. [/code]

Continue reading ...

Find Resource Currently Involved in Blocking

[cc lang=”sql”] SELECT DISTINCT objname = object_name(p.object_id) FROM sys.partitions p JOIN sys.dm_tran_locks t1 ON p.hobt_id = t1.resource_associated_entity_id [/cc]

Continue reading ...

Find Queries Using Most CPU

[cc lang=”sql”] SELECT TOP 5 object_name(objectID) ,[Avg CPU Time] = total_worker_time/execution_count ,execution_count ,Plan_handle ,query_plan FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) ORDER BY total_worker_time/execution_count DESC; [/cc]

Continue reading ...

Find Most Executed Queries

This DMV will show the actual queries sorted by highest execution count since the last time SQL Server was restarted. These queries could be either adhoc or queries that reside within stored procedures. These are important because often times optimizing these will yield huge results in performance. In order to find specific procedures that have been executed a lot, refer to the Most Executed Procedures DMV. [cc lang=”sql”] SELECT TOP 50 qs.execution_count ,OBJECT_NAME(objectid) ,query_text = SUBSTRING( qt.text, qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), qt.text)) * 2 ELSE qs.statement_end_offset END – qs.statement_start_offset)/2) ,qt.dbid ,dbname = db_name(qt.dbid) ,qt.objectid FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt ORDER BY qs.execution_count DESC [/cc]

Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!