Solved

Replacement for GetDate()

Posted on 2004-03-23
7
1,117 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
7 Comments
 
LVL 34

Accepted Solution

by:
arbert earned 125 total points
Comment Utility
set newdate=convert(char(10),getdate(),101)

Brett
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 34

Expert Comment

by:arbert
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now