Date Parsing with DateValue Equivalent

Thanks in Advance,
MS Access has a flexible function called DateValue that will convert a
valid string into a date. Does anyone know a method of parsing ntext
values to detect and convert this strings into dates? The ntext values
might look something like this:
Established in 1974.
Created in Aug 1986 and dedicated on September 29th, 1986.
Abolished January 1, 1957. Reenacted on May 15, 1978, and transferred
functions on March 11, 1981 by executive order.
I suspect a combination of patindex search and other functions will do
the trick.
Mark
Napa, CAIf you are using SQL Server 2005, you could write a User Defined Function in
a .NET language that could do this type of parsing for you.
Hope this helps!
Chuck Heinzelman
MCSD, MCDBA
I support the Professional Association for SQL Server (www.sqlpass.org)
This posting is not an endoresment of any product.
Information is provided as-is, and carries no warranties - either express or
implied.
Please respond in newsgroups only.
<xxxdbaxxx@.gmail.com> wrote in message
news:1145893343.427405.182460@.v46g2000cwv.googlegroups.com...
> Thanks in Advance,
> MS Access has a flexible function called DateValue that will convert a
> valid string into a date. Does anyone know a method of parsing ntext
> values to detect and convert this strings into dates? The ntext values
> might look something like this:
> Established in 1974.
> Created in Aug 1986 and dedicated on September 29th, 1986.
> Abolished January 1, 1957. Reenacted on May 15, 1978, and transferred
> functions on March 11, 1981 by executive order.
> I suspect a combination of patindex search and other functions will do
> the trick.
> Mark
> Napa, CA
>|||Well, we are still in SQL 2000 Chuck. Any other thoughts?

Date parameter 7 days in advance

can somebody pls help me with the formula for a datetime parameter that is
say 7 days past today. this will be used as the end date parameter.
my start_date parameter default is =(today)
thanksTry using SQL to create a dataset.
SELECT dateadd(day,7,getdate()) as end_date
I use dateadd like crazy.
"Tango" <Tango@.discussions.microsoft.com> wrote in message
news:Tango@.discussions.microsoft.com:
> can somebody pls help me with the formula for a datetime parameter that is
> say 7 days past today. this will be used as the end date parameter.
> my start_date parameter default is =(today)
> thanks|||Thanks John,
I tried adding this statement in the default value of the query & it didnt
work
I also copied your statement into the query string of a new dataset & get
error message 'minimum capacity must be non negative'
Todd
"John Geddes" wrote:
> Try using SQL to create a dataset.
> SELECT dateadd(day,7,getdate()) as end_date
> I use dateadd like crazy.
>
> "Tango" <Tango@.discussions.microsoft.com> wrote in message
> news:Tango@.discussions.microsoft.com:
> > can somebody pls help me with the formula for a datetime parameter that is
> >
> > say 7 days past today. this will be used as the end date parameter.
> >
> > my start_date parameter default is =(today)
> >
> > thanks
>
>|||undefined function 'getdate' in expression is the error message i get when i
run the query in the new dataset
"John Geddes" wrote:
> Put the statement in a new dataset.
> Then, go to the parameter and select default value from dataset, pick
> your new dataset, and then pick the column name.
> Did that help?
>
> "Tango" <Tango@.discussions.microsoft.com> wrote in message
> news:Tango@.discussions.microsoft.com:
> > Thanks John,
> >
> > I tried adding this statement in the default value of the query & it didnt
> >
> > work
> > I also copied your statement into the query string of a new dataset & get
> >
> > error message 'minimum capacity must be non negative'
> >
> > Todd
> >
> > "John Geddes" wrote:
> >
> > > Try using SQL to create a dataset.
> > >
> > > SELECT dateadd(day,7,getdate()) as end_date
> > >
> > > I use dateadd like crazy.
> > >
> > >
> > > "Tango" <Tango@.discussions.microsoft.com> wrote in message
> > > news:Tango@.discussions.microsoft.com:
> > > > can somebody pls help me with the formula for a datetime parameter
> > > > that is
> > > >
> > > > say 7 days past today. this will be used as the end date parameter.
> > > >
> > > > my start_date parameter default is =(today)
> > > >
> > > > thanks
> > >
> > >
> > >
>
>|||Thanks saglamtimur
i am now getting an error message 'doesnt have the expected type'
"saglamtimur" wrote:
> If you want vb.net solution I use this function for one week (7 days);
> =format(dateadd("ww",1,Globals!ExecutionTime),"dd/MM/yyyy")
> "ww" equals week, 1 equals 1 week, if you want further info just google
> "vb.net dateadd function"
> Hope helps.
> Regards
> "John Geddes" <john_g@.alamode.com> wrote in message
> news:#KqRU#h4EHA.3236@.TK2MSFTNGP15.phx.gbl...
> > Put the statement in a new dataset.
> >
> > Then, go to the parameter and select default value from dataset, pick
> > your new dataset, and then pick the column name.
> >
> > Did that help?
> >
> >
> > "Tango" <Tango@.discussions.microsoft.com> wrote in message
> > news:Tango@.discussions.microsoft.com:
> > > Thanks John,
> > >
> > > I tried adding this statement in the default value of the query & it
> didnt
> > >
> > > work
> > > I also copied your statement into the query string of a new dataset &
> get
> > >
> > > error message 'minimum capacity must be non negative'
> > >
> > > Todd
> > >
> > > "John Geddes" wrote:
> > >
> > > > Try using SQL to create a dataset.
> > > >
> > > > SELECT dateadd(day,7,getdate()) as end_date
> > > >
> > > > I use dateadd like crazy.
> > > >
> > > >
> > > > "Tango" <Tango@.discussions.microsoft.com> wrote in message
> > > > news:Tango@.discussions.microsoft.com:
> > > > > can somebody pls help me with the formula for a datetime parameter
> > > > > that is
> > > > >
> > > > > say 7 days past today. this will be used as the end date parameter.
> > > > >
> > > > > my start_date parameter default is =(today)
> > > > >
> > > > > thanks
> > > >
> > > >
> > > >
> >
> >
>
>|||Use expression (for seven days before):
=DateTime.Now.AddDays(-7)
Use expression (for seven days after):
=DateTime.Now.AddDays(7)
"Tango" wrote:
> can somebody pls help me with the formula for a datetime parameter that is
> say 7 days past today. this will be used as the end date parameter.
> my start_date parameter default is =(today)
> thanks|||Thank you soooo much sathya
works a charm
"sathya" wrote:
> Use expression (for seven days before):
> =DateTime.Now.AddDays(-7)
> Use expression (for seven days after):
> =DateTime.Now.AddDays(7)
>
> "Tango" wrote:
> > can somebody pls help me with the formula for a datetime parameter that is
> > say 7 days past today. this will be used as the end date parameter.
> >
> > my start_date parameter default is =(today)
> >
> > thanks|||How could this be done w/ a parameter?
=format(dateadd("mm",-1,parameters!stardate),"MMMM")
Thanks...
"saglamtimur" wrote:
> If you want vb.net solution I use this function for one week (7 days);
> =format(dateadd("ww",1,Globals!ExecutionTime),"dd/MM/yyyy")
> "ww" equals week, 1 equals 1 week, if you want further info just google
> "vb.net dateadd function"
> Hope helps.
> Regards
> "John Geddes" <john_g@.alamode.com> wrote in message
> news:#KqRU#h4EHA.3236@.TK2MSFTNGP15.phx.gbl...
> > Put the statement in a new dataset.
> >
> > Then, go to the parameter and select default value from dataset, pick
> > your new dataset, and then pick the column name.
> >
> > Did that help?
> >
> >
> > "Tango" <Tango@.discussions.microsoft.com> wrote in message
> > news:Tango@.discussions.microsoft.com:
> > > Thanks John,
> > >
> > > I tried adding this statement in the default value of the query & it
> didnt
> > >
> > > work
> > > I also copied your statement into the query string of a new dataset &
> get
> > >
> > > error message 'minimum capacity must be non negative'
> > >
> > > Todd
> > >
> > > "John Geddes" wrote:
> > >
> > > > Try using SQL to create a dataset.
> > > >
> > > > SELECT dateadd(day,7,getdate()) as end_date
> > > >
> > > > I use dateadd like crazy.
> > > >
> > > >
> > > > "Tango" <Tango@.discussions.microsoft.com> wrote in message
> > > > news:Tango@.discussions.microsoft.com:
> > > > > can somebody pls help me with the formula for a datetime parameter
> > > > > that is
> > > > >
> > > > > say 7 days past today. this will be used as the end date parameter.
> > > > >
> > > > > my start_date parameter default is =(today)
> > > > >
> > > > > thanks
> > > >
> > > >
> > > >
> >
> >
>
>|||would it be safe to assume that you could insert the starting date parameter
instead of now. so the second date (end date) is x days after the starting
date'
"sathya" wrote:
> Use expression (for seven days before):
> =DateTime.Now.AddDays(-7)
> Use expression (for seven days after):
> =DateTime.Now.AddDays(7)
>
> "Tango" wrote:
> > can somebody pls help me with the formula for a datetime parameter that is
> > say 7 days past today. this will be used as the end date parameter.
> >
> > my start_date parameter default is =(today)
> >
> > thanks|||Hi Ben,
I have just wrote a solution for your previous post.
saglamtimur
"Tango" <Tango@.discussions.microsoft.com> wrote in message
news:CA9238B4-5E65-417A-89D8-F70FBD8610A8@.microsoft.com...
> would it be safe to assume that you could insert the starting date
parameter
> instead of now. so the second date (end date) is x days after the starting
> date'
> "sathya" wrote:
> > Use expression (for seven days before):
> > =DateTime.Now.AddDays(-7)
> > Use expression (for seven days after):
> > =DateTime.Now.AddDays(7)
> >
> >
> > "Tango" wrote:
> >
> > > can somebody pls help me with the formula for a datetime parameter
that is
> > > say 7 days past today. this will be used as the end date parameter.
> > >
> > > my start_date parameter default is =(today)
> > >
> > > thanks|||Here's the formula...
=CDate(Parameters!startdate.Value).AddMonths(-1).ToString("MMMM")
"saglamtimur" wrote:
> Hi Ben,
> I have just wrote a solution for your previous post.
> saglamtimur
> "Tango" <Tango@.discussions.microsoft.com> wrote in message
> news:CA9238B4-5E65-417A-89D8-F70FBD8610A8@.microsoft.com...
> > would it be safe to assume that you could insert the starting date
> parameter
> > instead of now. so the second date (end date) is x days after the starting
> > date'
> >
> > "sathya" wrote:
> >
> > > Use expression (for seven days before):
> > > =DateTime.Now.AddDays(-7)
> > > Use expression (for seven days after):
> > > =DateTime.Now.AddDays(7)
> > >
> > >
> > > "Tango" wrote:
> > >
> > > > can somebody pls help me with the formula for a datetime parameter
> that is
> > > > say 7 days past today. this will be used as the end date parameter.
> > > >
> > > > my start_date parameter default is =(today)
> > > >
> > > > thanks
>
>sql

Date functions

Hi all,

Are there a functions for last day of the month or first day of the month?

Thanks in advance,

Jen

--
Fast Track On Line -Web Design and Development
Portfolio http://www.fasttrackonline.co.uk

--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004Hi, Jenny!
You wrote on Fri, 21 May 2004 12:58:00 +0100:
J> Are there a functions for last day of the month
dateadd(month,1,dateadd(day,-day(getdate())+1,getdate()))
J> or first day of the month?
dateadd(day,-day(getdate())+1,getdate())

J> Thanks in advance,
NE ZA CHTO, ZAKHODITE ESCHYO!

J> Jen

-
exexe!|||"Jenny" <jennyysplace@.eidosnet.co.uk> wrote in message news:40adef3e@.212.67.96.135...
> Hi all,
> Are there a functions for last day of the month or first day of the month?
> Thanks in advance,
> Jen
>
> --
> Fast Track On Line -Web Design and Development
> Portfolio http://www.fasttrackonline.co.uk
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004

Last day of month:

SELECT DATEADD(MONTH, 1, CURRENT_TIMESTAMP) -
DAY(DATEADD(MONTH, 1, CURRENT_TIMESTAMP))

First day of month:

SELECT CURRENT_TIMESTAMP - DAY(CURRENT_TIMESTAMP) + 1

--
JAG

Date formatting

Hi,
How do I get rid of the seconds in a date:
10/13/2004 8:27:00 PM
should read as
10/13/2004 8:27 PM
Thanks in advance
ChristianHi
Look at CAST and COVERT in BOL
Regards
Mike
"Christian Perthen" wrote:
> Hi,
> How do I get rid of the seconds in a date:
> 10/13/2004 8:27:00 PM
> should read as
> 10/13/2004 8:27 PM
> Thanks in advance
> Christian
>
>