Strip Time from Datetime

[scott]

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"

 

[Steven Burn]

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]

i've got to actually strip the time off, not just display because the date
is a parameter for a sql sproc.

 

[Bob Barrows [MVP]]

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.

 

[scott]

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.

 

[McKirahan]

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)

 

[Bob Barrows [MVP]]

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

What datatype is the parameter?

Bob Barrows

 

[scott]

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

 

[Roland Hall]

in message : : > 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

 

[Aaron Bertrand [SQL Server MVP]]

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