Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Date Formatting...

Status
Not open for further replies.
nzgirl

nzgirl

Programmer
Feb 26, 2003
72
NZ
Hi.. again this has got to be easy but RingTFM isn't helping...

I have a DateEntered field in my database as: m/d/yyyy hh:mm:ss

I want to take that date, strip the time and add a number of days.

I've tried using (thanks bgaines72)
FormatDateTime(DateEntered, vbShortDate) but that changes it to a char field???

I'm about at the end of my tether here...(and its too early for beer)

I did try the ol'
dayenter = day(DateEntered) etc but when I add them back together
DateEntered = yearenter & monthenter and dayenter I end up with 200333 (rather than 20030303) which doesn't help with comparisons etc... I don't really wanna see if day <10 and add 0 to the start... and am not sure if it would work??

Any ideas appreciated :)
Thanks
 
Sort by date Sort by votes
Here is an idea. It seems that the DateEntered column in your database is a DATETIME datatype since it is displayed with the date and the time. If so that is good. You can use DATETIME functions to add days or display in various formats.

Dont bother with stripping the time out, you dont need to do that. To add 10 days

In Access

DateAdd(&quot;d&quot;, 10, [DateEntered])

In MS SQL Server

DATEADD(day, 10, DateEntered)


 
Upvote 0 Downvote
Thanks for the reply :)

Unfortuantly at the moment I need to set the time to 00:00:00 so that a compare against another field will return values...(eventually my users will be able to choose a time but for the moment they can't.)

The other field is also a datetime but I set it like
SendDate = Date and input into the database whereas DateEntered is a getdate() in the database...

could I put DateEntered into a Date Only field? I just can't find how to do this??

any ideas?
Thanks :)
 
Upvote 0 Downvote
If you have a function getdate() in the database then I think your database is SQL Server.

SQL Server does not have a datatype of DATE, or Date Only as you said.

Here are some ideas you might play with.

Think of the value of SendDate as 3/6/2003 00:00:00, instead of just 3/6/2003. This is the value created in the ASP (VBScript) program. The DateEntered value generated by getdate() in the database will be anything from 3/6/2003 00:00:00 to 3/6/2003 23:59:59 for things entered on the same date. So to match up SendDate and DateEntered you could write this condition in SQL

WHERE DateEntered BETWEEN @send_date AND DATEADD( day, 1, @send_date)

Here I am assuming you are passing the value of SendDate to a stored procedure in the parameter named @send_date; the column is named DateEntered.

Or, in the ASP script where we are working with variables named DateEntered and SendDate

If SendDate <= DateEntered And DateEntered < DateAdd(&quot;d&quot;, 1, SendDate) Then


Again, I would encourage you to work with these values as DATETIME datatypes rather than converting them to strings and back. Remember that a DATETIME value is a number, it starts at 0 meaning midnight on some date in the past and it increments as the seconds tick away. What we see when we display the value is not the number but a formatted interpretation of the number as a date and time. Sometimes the format only shows the date, but the number always refers to a time on a date. When you use the Date function to obtain a DATETIME value, it returns a number for midnight today.

Try this.
Code: 
<!DOCTYPE HTML PUBLIC &quot;-//W3C//DTD HTML 4.0 Transitional//EN&quot;>

<html>
<head>
	<title>VBScript Date</title>
</head>

<body>

<h3>What is the date?</h3><br>
<%
Dim SendDate
SendDate = Date
Response.Write SendDate & &quot;<br>&quot;
Response.Write FormatDateTime( SendDate, 3)

%>

</body>
</html>
 
Upvote 0 Downvote
Thanks for the reply :)

This worked!
I have DateEntered from one table which I need to compare with SendDate in another table.
DateEntered has the time component(3/4/2003 12:01:03)
and SendDate doesn't (3/4/2003 00:00:00) ..
so to get the same DATE I need to say

WHERE Send Date Between dateadd(d, -1, DateEntered) and DateEntered ie(3/3/2003 12:01:03 < 3/4/2003 00:00:00 < 3/4/2003 12:01:03)

but now I need to set the time to 00:00:00 for the update as I want SendDate to not have times associated...

do I give up and use char?

Thanks
Beth
 
Upvote 0 Downvote
Well you could modify all of the existing dates and change the time to 00:00:00 like this.
Code: 
UPDATE MyTable SET DateEntered = CONVERT(DATETIME, CONVERT(CHAR(10), DateEntered, 101))

Then you could change the default value for the DateEntered column from getdate() to

Code: 
CONVERT(DATETIME, CONVERT(CHAR(10), getdate(), 101))
 
Upvote 0 Downvote
Thanks.
I gave up over the weekend.. and I finally got it going yesterday.
I have another question (do they ever end?) but I'll start an approriate thread :)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
How to display an existing date in Input type=&quot;date&quot;?
Replies
4
Views
457
leifoet
leifoet
leifoet
  • Locked
  • Question
Using date picker gives an error message, only at application start
Replies
1
Views
598
MarkSweetland
MarkSweetland
leifoet
  • Locked
  • Question
How to compare (date+time) with now() ?
Replies
1
Views
260
GriffMG
GriffMG
RonnieKar
  • Locked
  • Question
Date/Time stamp
Replies
4
Views
409
RonnieKar
RonnieKar
penguinspeaks
  • Locked
  • Question
Date woes
Replies
7
Views
445
penguinspeaks
penguinspeaks

Part and Inventory Search

Sponsor

Back
Top