?
Solved

Replacement for GetDate()

Posted on 2004-03-23
7
Medium Priority
?
1,125 Views
Last Modified: 2012-06-21
We are trying to set up a job in SQL Server 2000 so that at a certain day and time, a column in our database will be updated with the current date but not the time.

The following works:

Update tablename
Set newdate = GetDATE ()

GO

but it dumps in the full date and time of the server. We just need the date and we do NOT want the time.

The date needs to be in the format of mm/dd/yyyy .  Nothing I have tried so far works.  I keep getting all kinds of errors.  Could someone please correct the code I have above to what it should read, to update the column newdate, with the correct date without the time?

Thanks!
0
Comment
Question by:khockady
[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
7 Comments
 
LVL 34

Accepted Solution

by:
arbert earned 375 total points
ID: 10664111
set newdate=convert(char(10),getdate(),101)

Brett
0
 
LVL 34

Expert Comment

by:arbert
ID: 10664114
You realize that a datefield does store date/time right?  Generally you store both the date and time and use CAST/CONVERT to extract the parts you need later.

Brett
0
 
LVL 12

Expert Comment

by:esteban_felipe
ID: 10664155
Hi khockady,

Here's a small script that represent what would I do:
---------------------------------------------------------------------------------
Declare @BaseDate datetime
Declare @DateWithoutTime datetime
Declare @FinalDate datetime
Set @BaseDate = '2001-03-22 20:13:18.077'

Set @DateWithoutTime= Convert(datetime,
            cast(DatePart(yy,getDate()) as varchar(4)) + '-' +
            cast(Datepart(mm,getDate()) as varchar(2)) + '-' +
            cast(DatePart(dd,getDate()) as varchar(2)))

Set @FinalDate =
      DateAdd(hh,DatePart(hh,@BaseDate),
            DateAdd(mi,DatePart(mi,@BaseDate),
                  DateAdd(ss,DatePart(ss,@BaseDate),
                        DateAdd(ms,DatePart(ms,@BaseDate),@DateWithoutTime))))

Print @FinalDate
---------------------------------------------------------------------------------
Basicly it will create a datetime value from getDate() ignoring time, then it will add milliseconds, seconds, minutes and hours taken from the @BaseDate value. Applying this procedure to an update statement it would be:

Update
      tablename
set newDate =
      DateAdd(hh,DatePart(hh,newDate),
            DateAdd(mi,DatePart(mi,newDate),
                  DateAdd(ss,DatePart(ss,newDate),
                        DateAdd(ms,DatePart(ms,newDate),
                              Convert(datetime,
                                          cast(DatePart(yy,getDate()) as varchar(4)) + '-' +
                                          cast(Datepart(mm,getDate()) as varchar(2)) + '-' +
                                          cast(DatePart(dd,getDate()) as varchar(2)))))))

I hope it helps


Esteban Felipe
www.estebanf.com
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 34

Expert Comment

by:arbert
ID: 10664199
Seems a little overcomplicated--the returned value doesn't look like the requested format (time is still included)....
0
 
LVL 2

Expert Comment

by:MePhantom
ID: 10664481
Hi khockady,

If newdate is defined as datetime
If you set newdate = to just the date (like newdate='2004-03-23') you will still get date AND time when you do a select newdate. In this case time will be 00:00:00, but it will still show.

Use the set statement as you show it, however on the select you use to look at this use

Select convert(char(10),newdate,101)

This will show newdate in the mm/dd/yyyy format. + you will still have time if you ever need it.
And Sooner or later somebody will want that time 8o)

Of course you could also define newdate as char[10] and use arbert's set statement.
Not the best way to store date in my opinion, but its your app.

Cheers!
0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 10667620
If the field in your database that you're comparing against has a time componant that is other than 00:00:000, then you're right, any comparision, even with a date with a time of 00:00:000 will not return what you want.  So, you have a couple of options.
If there is a lot of data, or you have a great index on your date field, dont use the first method, it alters the format of your date field for the comparision. The second one will get you all the values 'today', regardless of time.

where convert(char(10),yourdatefield,101) = convert(char(10),getdate(),101)  ---converts your date field so an index wouldnt be well used

where yourdatefield between convert(datetime,convert(char(10),getdate(),101)) and getdate()  --so between today 00:00:000 and today current time


0
 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 10667668
Sorry, read it wrong, thought you were looking to execute within a certain date range.
SQL dates are stored as datetime (until Yukon). However there is nothing to force you to use the time componant for display. Use the convert/convert for your assignment (it will store 0 time that way)

convert(datetime,convert(char(10),getdate(),101))

When you display the field, in your select, convert it to character for display using convert(char(10),yourdatefield,101)

Storing a date as a character field will often lead to issues in retrieving and sorting data later, not to mention enforcing integrity. Also, by keeping it as a datetime field, if somewhere down the road, someone in managment decides they like the date to be formatted as dd-mm-yyyy you can easily do it without making any data changes.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

771 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