TRY_PARSE to check valid date


DECLARE @s VARCHAR(6) = '022212'
 

SELECT IIF(TRY_PARSE((Left(@s,2)+'-'+ substring(@s,3,2)+'-20'+right(@s,2)) AS date) IS not NULL, 'True', 'False') AS 'Validdate';


SQL Server 2012 or 2014
Definition:
TRY_PARSE ( string_value AS data_type [ USING culture ] )


 
Advertisements

Date Range (Gap and Island) T-SQL Question


CREATE TABLE #temp(key_column VARCHAR(30),start_dt DATETIME,end_dt DATETIME)

INSERT INTO #temp VALUES 
('A', '2014-05-01 00:00:00.000','2014-05-31 00:00:00.000'),
('A', '2014-06-01 00:00:00.000','2014-06-20 00:00:00.000'),
('A', '2014-06-20 00:00:00.000','2014-07-10 00:00:00.000'),
('B', '2014-05-01 00:00:00.000','2014-05-31 00:00:00.000'),
('B', '2014-06-10 00:00:00.000','2014-06-20 00:00:00.000')


DECLARE @minstart_dt DATETIME, @maxend_dt DATETIME;
SELECT @minstart_dt = MIN(start_dt),  @maxend_dt = MAX(end_dt)  FROM #temp;

--**** you can create a calendar table
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1)

,myDateCTE as
(
select dateadd(day,n-1,(select min(start_dt) from #temp)) dt 
from Nums
)

--******

, mycte as (
select dt, key_column, row_number() Over(Partition by key_column order by dt) rn1 from 
(select distinct key_column from #temp) t1, myDateCTE  
)

,mycte1 as (
SELECT key_column, dt, rn1, row_number() Over(Partition by key_column order by dt) rn2
   FROM mycte t1
   WHERE  EXISTS 
     (SELECT * FROM #temp t2 
       WHERE t1.dt BETWEEN t2.start_dt AND t2.end_dt and t1.key_column=t2.key_column)

	   )


Select key_column, Min(m.dt) As start_dt, Max(m.dt) As end_dt
From mycte1 m
Group By key_column, m.rn1 - m.rn2




OPTION (MAXRECURSION 0); 


drop table #temp


 

http://social.msdn.microsoft.com/Forums/en-US/da9803e1-dde0-4823-967c-d65028804878/merge-date-value-records-depending-upon-key-columns-urgent?forum=transactsql


Modify Computed Column Data Type Using T-SQL

You need to drop the column first and add the column back with explicit data type CAST/CONVERT.
An example:



ALTER TABLE [dbo].[myTable] 
   DROP COLUMN [fullname];

ALTER TABLE [dbo].[myTable] 
ADD [fullname]  AS (CONVERT(varchar(250)
,case when gid=1 
then isnull([fname],'')+' '+isnull([lname],'')
else [busname] end));

  

 

If your computed column has mixed column data types in its formula, you should align (fix) these columns to appropriate data type. The last option is to use a cast/convert to control the final data type for the computed column.

If you have the computed column in the table already when you want to modify one of the column data type used in the formula, you need to drop the computed column first and recreate it when you finish the data type change for the column involved.
An example:



ALTER TABLE [dbo].[myTable] 
   DROP COLUMN [fullname];

ALTER TABLE [dbo].[myTable] 
  ALTER COLUMN [theOtheColumn] varchar(55) null

ALTER TABLE [dbo].[myTable] 
ADD [fullname]  AS (CONVERT(varchar(250)
,case when gid=1 
then isnull([fname],'')+' '+isnull([lname],'')
else [busname] end));

  

 

Count Distinct –Alternate Solution in T-SQL

http://social.msdn.microsoft.com/Forums/en-US/53b7d5af-875c-4399-8593-299f6c230ada/update-with-count-distinct?forum=transactsql

CREATE TABLE mytable(
  mukey nvarchar (30) NULL,
  cokey nvarchar (30) NULL,
  ncomp int NULL);
INSERT INTO mytable(mukey, cokey, ncomp)
VALUES 
  ('1017271', '9302647', NULL),
  ('1017271', '9302647', NULL),
  ('1017271', '9302647', NULL),
  ('1017271', '9302648', NULL),
  ('1017271', '9302648', NULL),
  ('1017271', '9302648', NULL),
  ('1017271', '9302648', NULL)


;with PartialSums as (
SELECT mukey, cokey,COUNT(*) AS countstarpartialcount FROM mytable  
GROUP BY mukey, cokey
)
,mycte as (SELECT mukey,
SUM(countstarpartialcount) AS countstar,
SUM(1) AS countproductkeys FROM partialsums 
Group by mukey)


Merge mytable tgt 
using mycte  as src on tgt.mukey=src.mukey
When matched Then 
Update 
set ncomp=src.countproductkeys;

select * from mytable


drop table mytable

 

http://blogs.msdn.com/b/sqlqueryprocessing/archive/2008/09/22/distinct-aggregation-considered-harmful.aspx


Register -t3226 to SQL Server Startup Parameter to suppress backup operation success messages in logs

SQLServer configuration manager >>SQL Server Services>>Right click on the service to get the Properties menu.
>>tabbed dialog window >>start up parameters tab
>>Type -t3226 in Specify a startup parameter
>>Click Add

–will take effect after restart service

–Check status
DBCC TRACESTATUS (3226)

http://www.sqlskills.com/blogs/paul/fed-up-with-backup-success-messages-bloating-your-error-logs/
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/10/30/when-is-too-much-success-a-bad-thing.aspx
http://msdn.microsoft.com/en-us/library/ms188396.aspx


Get data from a date range: Construct the start and end date – T-SQL



declare @StartMonth NVARCHAR(10),
@StartYear NVARCHAR(4),
@EndMonth NVARCHAR(10),
@EndYear NVARCHAR(4)

Select @StartMonth = N'January',
		@StartYear = N'2014',
		@EndMonth = N'February',
		--@EndMonth = N'December',
		@EndYear = N'2014'




Select Cast(@StartMonth + '1 ' + @StartYear as date) as beginningofstartdt
,Cast(@EndMonth + '1 ' + @EndYear as date) as beginningofenddt
,Dateadd(month,1,Cast(@EndMonth + '1 ' + @EndYear as date)) as beginningofNextMonth,
DateAdd(ms, -3, DateAdd(month, DateDiff(month, 0, Cast(@EndMonth + '1 ' + @EndYear as date))+1, 0) ) endofenddt


--Solutions for your question

WHERE ta.TimeByDay >= Cast(@StartMonth + '1 ' + @StartYear as date)
AND ta.TimeByDay <Dateadd(month,1,Cast(@EndMonth + '1 ' + @EndYear as date))

--Or

WHERE ta.TimeByDay >= Cast(@StartMonth + '1 ' + @StartYear as date)
AND ta.TimeByDay <=DateAdd(ms, -3, DateAdd(month, DateDiff(month, 0, Cast(@EndMonth + '1 ' + @EndYear as date))+1, 0) )


--
--new functions since SQL Server 2012 Datefromparts, Eomonth ...
 

http://social.msdn.microsoft.com/Forums/en-US/f4abec70-5278-42eb-9978-6305fd73ca06/selecting-records-based-on-month-and-year-parameters?forum=transactsql


Split Time Period into Equal Time Slots



declare @date datetime = '2014-07-10 00:00:00.000'
      , @start int = 820
	  , @end int = 1000
	  -- , @start varchar(5) = '8:20'
	  --, @end  varchar(5) = '10:00'
	  , @duration  int = 20 ; --minute


	  declare @start2 varchar(5)=Left(Right('00'+Cast(@start as varchar(5)),4),2)+':'+Right(Cast(@start as varchar(5)),2)
	  declare @end2 varchar(5)=Left(Right('00'+Cast(@end as varchar(5)),4),2)+':'+Right(Cast(@end as varchar(5)),2)

	 ;with mycte as ( 
	 select *, dateadd(minute, timeslot*n,cast(convert(varchar(10),dt,101)+' '+ stime as datetime)) dt2
	  ,cast(convert(varchar(10),dt,101)+' '+ etime as datetime) dt_end
	   from (Select @date as dt, @start2 stime, @end2 etime, @duration timeslot) t
	  cross apply (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) d(n)

	  )

	  select dt2 from mycte where dt2<dt_end
	  /*
2014-07-10 08:20:00.000
2014-07-10 08:40:00.000
2014-07-10 09:00:00.000
2014-07-10 09:20:00.000
2014-07-10 09:40:00.000

	  */


 

http://social.msdn.microsoft.com/Forums/en-US/14abedbb-dada-4392-97cc-851e20112d97/query-to-split-or-parse-times-into-separate-entries?forum=transactsql