Solved

ms sql server 2005 date time issues

Posted on 2011-02-16
8
770 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:GlobaLevel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 34913140
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)
0
 
LVL 12

Expert Comment

by:jmcmunn
ID: 34913163

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

and you're all set.
0
 
LVL 29

Expert Comment

by:QPR
ID: 34913170
101 is MM/DD/YYYY
120 is yyyy-mm-dd hh:mm:ss
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 12

Expert Comment

by:jmcmunn
ID: 34913181

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.  ;-)
0
 
LVL 12

Expert Comment

by:jmcmunn
ID: 34913183
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.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34913342
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

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34913565
as getdate() has date and time, you would need to convert to DATE, not to TIME ... however, that would only be possible as from sql 2008.

before, you need:

if @APP_DATE >= '2011-02-16 00:00:00' AND @APP_DATE > '2011-02-17 00:00:00'

0
 
LVL 12

Expert Comment

by:jmcmunn
ID: 34916752
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.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
View SQL 2005 Job package 16 76
MS SQL + Insert Into Table - If Doesnt Exist 9 58
Disable TLS1.0 on Win 2012 server 7 57
Using this function 4 39
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question