Replacement for GetDate()

Posted on 2004-03-23
Medium Priority
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 ()


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?

Question by:khockady
LVL 34

Accepted Solution

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

LVL 34

Expert Comment

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.

LVL 12

Expert Comment

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 =

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:

set newDate =
                                          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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

LVL 34

Expert Comment

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

Expert Comment

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.


Expert Comment

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


Expert Comment

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)


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.

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

627 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