Gerard's codebase: SQL- Strpping out time from DateTime

Gerard's codebase

This is a personal repository of code snippets and information.

Over the years I have generated lots of little sub programs and stored away acres of useful code snippets. The problem is always to locate them.

Even more time wasting is forgetting how to do simple things when you havnt developed in an environment for a few years (or even a few months)

My new years resolution is to start putting them up in a common place as I produce them. (thanks google)

They are handy for me and, with a bit of a clean up and documentation, they might be handy for others if they wander in here.

Gerard 2008

Tuesday, November 18, 2008

SQL- Strpping out time from DateTime - going back to weekdays

-- locate last sunday
DECLARE @Date As DATETIME
DECLARE @CurDay AS INTEGER

SET @CurDay = DATEPART(weekday,GETDATE())
SET @Date =
CASE
WHEN 1 = @CurDay THEN GETDATE()
WHEN 2 = @CurDay THEN DATEADD(DAY,-1,GETDATE())
WHEN 3 = @CurDay THEN DATEADD(DAY,-2,GETDATE())
WHEN 4 = @CurDay THEN DATEADD(DAY,-3,GETDATE())
WHEN 5 = @CurDay THEN DATEADD(DAY,-4,GETDATE())
WHEN 6 = @CurDay THEN DATEADD(DAY,-5,GETDATE())
WHEN 7 = @CurDay THEN DATEADD(DAY,-6,GETDATE())
ELSE GETDATE()
END
--Strip out the time part
SET @Date = CAST((STR( YEAR( GETDATE() ) ) + '/' + STR( MONTH( GETDATE() ) ) + '/' + STR( DAY( GETDATE() ) ) )AS DATETIME )
Print @Date

Gerard's codebase