The code below is the standard way to get a date without the time in SQL Server.

Extensive testing (by me and many others) has shown that casting a date...

select

*

from

MyTable

where

-- Date greater than or equal to Monday of last week

MyDate >= dateadd(dd,((datediff(dd,'17530101',getdate())/7)*7)-7,'17530101')

and

-- Date before Saturday of last week

Mydate < dateadd(dd,((datediff(dd,'17530101',getdate())/7)*7)-2,'17530101')

Shorter.

select [Month] = right(100+month(getdate()),2)

Results:

Month

-----

03

So why did you post your question in the SQL Server 2005 forum?

The code should do what you want. Change the DB name from "test" to the database you are working with.

use master

go

declare @kill_spid varchar(20)

-- Find spid of user connection to...

select

[SQL Server Start Time]= convert(varchar(23),a.SQL_Start,121),

[SQL Agent Start Time]= convert(varchar(23),a.Agent_Start,121),

[SQL Uptime] =

convert(varchar(15),

right(10000000+datediff(dd,0,getdate()-a.SQL_Start),4)+' '+

convert(varchar(20),getdate()-a.SQL_Start,108)) ,

[Agent Uptime] =

convert(varchar(15),

right(10000000+datediff(dd,0,getdate()-a.Agent_Start),4)+' '+

convert(varchar(20),getdate()-a.Agent_Start,108))

from

(

Select

SQL_Start = min(aa.login_time),

Agent_Start =

nullif(min(case when aa.program_name like 'SQLAgent %' then aa.login_time else '99990101' end),

convert(datetime,'99990101'))

from

master.dbo.sysprocesses aa

where

aa.login_time...

This change to the inner WHERE clause will fix your problem. Conditions in a CASE statement are evaluated in order, so only values that pass the ISDATE checks will...

Bob Hovious (3/6/2009)

---

Many beers in fridge, we can make a campfire out back with the ISO standards.

By the way, does anyone else here follow the online comic XKCD? ...

Instead of this being a club, I like to think of SSC as more like a bar. Come in, set down, have a cold one, and start spouting off...

select

FirstMondayOfMonth =

dateadd(dd,(datediff(dd,'17530101',SeventhDayOfMonth)/7)*7,'17530101'),

FirstTuesdayOfMonth =

dateadd(dd,(datediff(dd,'17530102',SeventhDayOfMonth)/7)*7,'17530102'),

FirstWednesdayOfMonth =

dateadd(dd,(datediff(dd,'17530103',SeventhDayOfMonth)/7)*7,'17530103'),

FirstThursdayOfMonth =

dateadd(dd,(datediff(dd,'17530104',SeventhDayOfMonth)/7)*7,'17530104'),

FirstFridayOfMonth =

dateadd(dd,(datediff(dd,'17530105',SeventhDayOfMonth)/7)*7,'17530105'),

FirstSaturdayOfMonth =

dateadd(dd,(datediff(dd,'17530106',SeventhDayOfMonth)/7)*7,'17530106'),

FirstSundayOfMonth =

dateadd(dd,(datediff(dd,'17530107',SeventhDayOfMonth)/7)*7,'17530107')

from

(

Select SeventhDayOfMonth = dateadd(month,datediff(month,0,getdate()),0)+6

) a

You can also just right-click on a table in the SSMS object explorer, and pick Script Table as, SELECT

t.walker (3/5/2009)

---

Perhaps you real experts who know lots of stuff can tell me why you persist in advising people who can't blow their nose yet.

This is not a sarcastic point...

SQL Server 2008 Books Online (February 2009)

What's New (SQL Server 2008)

http://msdn.microsoft.com/en-us/library/bb500435.aspx?ppud=4

Being an MVP would be cool, but nothing beats basking in the warm glow of my own genuine and heartfelt self admiration. :satisfied:

There is real value in responding to even really stupid posts by people that are completely clueless and hopeless in their stupidity.

If no one responds to those sorts of things,...