Date Tricks

22/04/2014

First Day Of Current Week. select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),0),106) Last Day Of Current Week. select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),6),106) First Day Of Last week. select CONVERT(varchar,DATEADD(week,datediff(week,7,getdate()),0),106) Last Day Of Last Week. select CONVERT(varchar,dateadd(week,datediff(week,7,getdate()),6),106) First Day Of Next Week. select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),7),106) Last Day Of Next Week. select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),13),106) First Day Of Current Month. select CONVERT(varchar,dateadd(d,-(day(getdate()-1)),getdate()),106) Last Day Of Current Month. select CONVERT(varchar,dateadd(d,-(day(dateadd(m,1,getdate()))),dateadd(m,1,getdate())),106) First Day Of Last Month. select CONVERT(varchar,dateadd(d,-(day(dateadd(m,-1,getdate()-2))),dateadd(m,-1,getdate()-1)),106) Last Day Of Last Month. select CONVERT(varchar,dateadd(d,-(day(getdate())),getdate()),106) First Day Of Next Month. select CONVERT(varchar,dateadd(d,-(day(dateadd(m,1,getdate()-1))),dateadd(m,1,getdate())),106) Last Day Of Next Month. select CONVERT(varchar,dateadd(d,-(day(dateadd(m,2,getdate()))),DATEADD(m,2,getdate())),106) First Day Of Current Year. select CONVERT(varchar,dateadd(year,datediff(year,0,getdate()),0),106) Last Day Of Current Year. select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+1,0))),106) First Day of Last Year. select CONVERT(varchar,dateadd(year,datediff(year,0,getdate())-1,0),106) Last Day Of Last Year. select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate()),0))),106) First Day Of Next Year. select CONVERT(varchar,dateadd(YEAR,DATEDIFF(year,0,getdate())+1,0),106) Last Day Of Next Year. select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+2,0))),106)
תגיות:
תגובה אחת

SFTP Task (SSIS Control Flow Custom Component)

06/04/2014

Somthing I used today. Cool one. http://ssissftp.codeplex.com/ As the name says it, this custom  "Control Flow" component can be used to send/receive files via a SFTP Connection. System requirements: Microsoft SQL Server 2008 Microsoft Visual Studio 2010 Please take a look at the documentation page to see how you can use it. PragmaticWorks has also a nice solution: http://pragmaticworks.com/Products/Task-Factory/Features/SecureFTPTask.aspx    
אין תגובות

SQL Server Migration Assistant

SQL Server Migration Assistant (SSMA) v5.2 is about to change the way you think :-). SSMA simplifies database migration process from Oracle/Sybase/MySQL and Microsoft Access to SQL Server and SQL Azure. SSMA automates all aspects of migration including migration assessment analysis, schema and SQL statement conversion, data migration as well as migration testing to reduce cost and reduce risk of your database migration project.   I have used it to migrate from ORACLE, it was quite easy and step by step technic.   try it here    http://blogs.msdn.com/b/ssma/archive/2012/01/31/microsoft-sql-server-migration-assistant-ssma-5-2-is-now-available.aspx      
אין תגובות

Microsoft SQL Server Data Tools Update (2)

With the release of SQL Server 2014 Community Technology Preview 2 (CTP2) and the commercial release of Visual Studio 2013, we are excited to announce new preview releases of SQL Server Data Tools to support both database and business intelligence projects. SQL Server Data Tools - Business Intelligence for SQL Server 2014 Preview To support new business intelligence projects, we have recently released a Community Technology Preview (CTP) of the SQL Server Data Tools – Business Intelligence (SSDT-BI) tools that work with SQL Server 2014 CTP2. This CTP release can be downloaded here. SQL Server Data Tools - Business Intelligence (SSDT-BI)...
אין תגובות

cut the date

03/04/2014

Sometimes you need to "Floor" the datetime to different needs: Here are most of them... Just copy it to SSMS and check it out :-) declare @date datetime set @date='2014-04-03 05:10:56.470' select DATEADD(year,datediff(year,0,@date),0) as year, DATEADD(month,datediff(month,0,@date),0) as month, DATEADD(day,datediff(day,0,@date),0) as day, DATEADD(hour,datediff(hour,0,@date),0) as hour, DATEADD(minute,datediff(minute,0,@date),0) as minute, DATEADD(day,datediff(day,0,@date),0) +convert(varchar(15),@date,108)as seconds , @date as milliseconds
אין תגובות