Month: April 2012

Formatting SSN in TSQL

  The SQL below is used for taking a number and formatting it to a SSN.   select ssn,LEFT(ssn,3) + '-' + SUBSTRING(ssn,4,2) + '-' + RIGHT(ssn,4) from DbTools..Sheet1  ...

Read More

SQL Cursor and Date formatting

  I working with SQL Server a lot and have certain statements that I write and often have to search for templates for. Below is one, well actually two that I have to check for the usage when I use them. The snippet below creates a cursor that can be used for generating several “UPDATE” statements. The cursor template is simple to use and a good quick reminder. Within that template I also use the CAST to change a date time value to ‘MM/DD/YYYY’. Below is the statement written in TSQL, and I am placing it here for a way to quickly find it again. declare my_cursor Cursor FOR select e.guidfield,e.hiredate from Millennium..EInfo e INNER JOIN DbTools..Sheet1 s on (e.ssn=s.ssn) where e.newhireReportDate is null order by e.ssn declare @guid varchar(128), @hiredate datetime open my_cursor fetch next from my_cursor into @guid,@hiredate while(@@FETCH_STATUS<>-1) BEGIN DECLARE @sql varchar(500) set @sql = 'UPDATE EInfo SET newhiredate=''' + convert(varchar(12),@hiredate,101) + ''' WHERE guidfield=''' + @guid + ''' AND newhireReportDate is null' print @sql fetch next from my_cursor into @guid,@hiredate END close my_cursor deallocate my_cursor...

Read More