asked on

ms sql server 2005 date time issues

I need to pull a date that is this setup: '2011-02-16 00:00:00'

the below is not working....

DECLARE @APP_DATE smalldatetime
SET @APP_DATE = ISNULL(@APP_DATE, getDATE())

if @APP_DATE = '2011-02-16 00:00:00'
begin
DECLARE @CONFIRM NVARCHAR(10)
SET @CONFIRM = 'yes'
PRINT @CONFIRM
eND

print @APP_DATE

QPR

Won't @app_date always be null in your code?

The date format for yyyy-mm-dd hh:mm:ss is ....

CONVERT(VARCHAR(19), GETDATE(), 120)

jmcmunn

change your second line to this:
SET @APP_DATE = ISNULL(@APP_DATE, convert(varchar,getdate(), 101));

and you're all set.

QPR

101 is MM/DD/YYYY
120 is yyyy-mm-dd hh:mm:ss

jmcmunn

He doesn't want the time, he wants all zeroes for the hh:mm:ss. Thus, my method fills in the time as all zeroes by only selecting the MM/dd/yyyy. The rest of the short date time is filled with 00:00:00.

Trust me, I ran it, debugged it and it worked. ;-)

jmcmunn

presumably there is more code somewhere that MIGHT set Appdate...in which case it would not have to do the select and get the 00:00:00...at least that's the assumption I made since the above code wouldn't be of much use on it's own.

Sharath S

Convert GETDATE() to TIME that will take care of time part.

DECLARE @APP_DATE smalldatetime
 SET  @APP_DATE = ISNULL(CONVERT(TIME,@APP_DATE), CONVERT(TIME,GETDATE()))

if @APP_DATE = '2011-02-16 00:00:00'
begin
DECLARE @CONFIRM NVARCHAR(10)
SET @CONFIRM = 'yes'
PRINT @CONFIRM
eND

print @APP_DATE

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jmcmunn

That's ok if this really does solve your issue, but the accepted solution is correct for Sql 2008 and the question is labeled as Sql 2005 for anyone visiting this thread in the future. There are other valid solutions for 2005 listed here.