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