By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,264 Members | 1,755 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,264 IT Pros & Developers. It's quick & easy.

Strip Time from Datetime

P: n/a
I have a field with datetime values like below LISTING 1. Can someone help
me write code strip the time part so only values like "7/15/2005" will be
left.

Note - We must be able to strip dates with double digit months and days, so
i can't just use the right function with a hard coded parameter.
LISTING 1

mydatetimevalue = "7/15/2005 8:00:00 AM"
Jul 22 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
0 = vbGeneralDate
1 = vbLongDate
2 = vbShortDate
3 = vbLongTime
4 = vbShortTime

Response.Write FormatDateTime(mydatetimevalue, 2)

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

"scott" <sb*****@mileslumber.com> wrote in message
news:eN**************@TK2MSFTNGP14.phx.gbl...
I have a field with datetime values like below LISTING 1. Can someone help
me write code strip the time part so only values like "7/15/2005" will be
left.

Note - We must be able to strip dates with double digit months and days, so i can't just use the right function with a hard coded parameter.
LISTING 1

mydatetimevalue = "7/15/2005 8:00:00 AM"

Jul 22 '05 #2

P: n/a
scott wrote:
I have a field with datetime values like below LISTING 1. Can someone
help me write code strip the time part so only values like
"7/15/2005" will be left.

Note - We must be able to strip dates with double digit months and
days, so i can't just use the right function with a hard coded
parameter.

LISTING 1

mydatetimevalue = "7/15/2005 8:00:00 AM"


Use the FormatDateTime function. See:
http://www.aspfaq.com/show.asp?id=2313

Or are you are asking how to do it in a query? If so, you need to tell us
what database you are using.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 22 '05 #3

P: n/a
i've got to actually strip the time off, not just display because the date
is a parameter for a sql sproc.
"Steven Burn" <so*******@in-time.invalid> wrote in message
news:%2******************@TK2MSFTNGP15.phx.gbl...
0 = vbGeneralDate
1 = vbLongDate
2 = vbShortDate
3 = vbLongTime
4 = vbShortTime

Response.Write FormatDateTime(mydatetimevalue, 2)

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

"scott" <sb*****@mileslumber.com> wrote in message
news:eN**************@TK2MSFTNGP14.phx.gbl...
I have a field with datetime values like below LISTING 1. Can someone
help
me write code strip the time part so only values like "7/15/2005" will be
left.

Note - We must be able to strip dates with double digit months and days,

so
i can't just use the right function with a hard coded parameter.
LISTING 1

mydatetimevalue = "7/15/2005 8:00:00 AM"


Jul 22 '05 #4

P: n/a
I need just need some nifty use of string functions to chop off the time and
leave m/d/yyyy string. it's going to a sproc as a parameter so time needs to
be choped off, not just displayed right.
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:O1**************@TK2MSFTNGP12.phx.gbl...
scott wrote:
I have a field with datetime values like below LISTING 1. Can someone
help me write code strip the time part so only values like
"7/15/2005" will be left.

Note - We must be able to strip dates with double digit months and
days, so i can't just use the right function with a hard coded
parameter.

LISTING 1

mydatetimevalue = "7/15/2005 8:00:00 AM"


Use the FormatDateTime function. See:
http://www.aspfaq.com/show.asp?id=2313

Or are you are asking how to do it in a query? If so, you need to tell us
what database you are using.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Jul 22 '05 #5

P: n/a
"scott" <sb*****@mileslumber.com> wrote in message
news:eN**************@TK2MSFTNGP14.phx.gbl...
I have a field with datetime values like below LISTING 1. Can someone help
me write code strip the time part so only values like "7/15/2005" will be
left.

Note - We must be able to strip dates with double digit months and days, so i can't just use the right function with a hard coded parameter.
LISTING 1

mydatetimevalue = "7/15/2005 8:00:00 AM"

WScript.Echo Split(mydatetimevalue," ")(0)
Jul 22 '05 #6

P: n/a
Then you'll want to read this:
http://www.aspfaq.com/show.asp?id=2040

What datatype is the parameter?

Bob Barrows

scott wrote:
I need just need some nifty use of string functions to chop off the
time and leave m/d/yyyy string. it's going to a sproc as a parameter
so time needs to be choped off, not just displayed right.
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:O1**************@TK2MSFTNGP12.phx.gbl...
scott wrote:
I have a field with datetime values like below LISTING 1. Can
someone help me write code strip the time part so only values like
"7/15/2005" will be left.

Note - We must be able to strip dates with double digit months and
days, so i can't just use the right function with a hard coded
parameter.

LISTING 1

mydatetimevalue = "7/15/2005 8:00:00 AM"


Use the FormatDateTime function. See:
http://www.aspfaq.com/show.asp?id=2313

Or are you are asking how to do it in a query? If so, you need to
tell us what database you are using.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 22 '05 #7

P: n/a
it's datetime. Below is my sproc that works fine with m/d/yyyy datebase
values. now, if i add the time to the m/d/yyyy value in my database, my
sproc won't recognize them.

SPROC LISTING

CREATE PROCEDURE mlc_CALENDAR_GetDayEvents
/*
Returns calendar events for a single day on team calendar

*/
(
@dtDate datetime
)
AS
BEGIN

SELECT *
FROM t_events
WHERE eventArchived = 0 AND
eventStart_date >= CONVERT(datetime,(convert(varchar,@dtDate,101) + ' 12:00
AM')) AND
eventEnd_date <= CONVERT(datetime,(convert(varchar,@dtDate,101) + ' 11:59
PM'))
END
GO

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:Or**************@TK2MSFTNGP14.phx.gbl...
Then you'll want to read this:
http://www.aspfaq.com/show.asp?id=2040

What datatype is the parameter?

Bob Barrows

scott wrote:
I need just need some nifty use of string functions to chop off the
time and leave m/d/yyyy string. it's going to a sproc as a parameter
so time needs to be choped off, not just displayed right.
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:O1**************@TK2MSFTNGP12.phx.gbl...
scott wrote:
I have a field with datetime values like below LISTING 1. Can
someone help me write code strip the time part so only values like
"7/15/2005" will be left.

Note - We must be able to strip dates with double digit months and
days, so i can't just use the right function with a hard coded
parameter.

LISTING 1

mydatetimevalue = "7/15/2005 8:00:00 AM"

Use the FormatDateTime function. See:
http://www.aspfaq.com/show.asp?id=2313

Or are you are asking how to do it in a query? If so, you need to
tell us what database you are using.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Jul 22 '05 #8

P: n/a
"McKirahan" wrote in message news:TZ********************@comcast.com...
: "scott" <sb*****@mileslumber.com> wrote in message
: news:eN**************@TK2MSFTNGP14.phx.gbl...
: > I have a field with datetime values like below LISTING 1. Can someone
help
: > me write code strip the time part so only values like "7/15/2005" will
be
: > left.
: >
: > Note - We must be able to strip dates with double digit months and days,
: so
: > i can't just use the right function with a hard coded parameter.
: >
: >
: > LISTING 1
: >
: > mydatetimevalue = "7/15/2005 8:00:00 AM"
:
:
: WScript.Echo Split(mydatetimevalue," ")(0)

You beat me too it but you don't need " " do ya'? Space is the default
delimiter.

WScript.Echo Split(mydatetimevalue)(0)

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp
Jul 22 '05 #9

P: n/a
> it's datetime. Below is my sproc that works fine with m/d/yyyy

Yes, but it MIGHT NOT TOMORROW! Use a *SAFE* format, please. YYYYMMDD
works for SQL Server,YYYY-MM-DD works for Access. Please read
http://www.aspfaq.com/2023 -- just because something works fine does not
make it the right thing to do.
now, if i add the time to the m/d/yyyy value in my database, my sproc
won't recognize them.
What do you mean? Do you get an error message? If so, what is it?
SELECT *
FROM t_events
WHERE eventArchived = 0 AND
eventStart_date >= CONVERT(datetime,(convert(varchar,@dtDate,101) + '
12:00 AM')) AND
eventEnd_date <= CONVERT(datetime,(convert(varchar,@dtDate,101) + ' 11:59
PM'))


Do not use SELECT * in production code. And try the following instead, if
you look atthe queyr plan I think you will find that it is at least as
efficient if not more so, and is much easier to read.

-- always name the owner!
CREATE PROCEDURE dbo.mlc_CALENDAR_GetDayEvents
@dt SMALLDATETIME
AS
BEGIN
SET NOCOUNT ON

-- most efficient way to strip time:
SET @dt = DATEADD(DAY, 0, DATEDIFF(DAY, 0, @dt))

SELECT Column_Names
FROM t_events
WHERE eventArchived = 0
AND eventStart_date >= @dt
AND eventEnd_date < (@dt+1)
-- use less then the next day at midnight
-- then you don't lose values like 11:59:30
END
GO

Now, do you really mean to only capture events that last < 24 hours? Or did
you mean to capture events that start on @dt, end on @dt, or are in progress
on @dt?

A
Jul 22 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.