Date Question

I want to select all records where the date is less than today minus 30. I have tried (Current_Date - 30) but the syntax is wrong. any ideas?

Maybe something like this:

declare @.whatTime table ( aTime datetime )
insert into @.whatTime values ('1/1/7')
insert into @.whatTime select dateadd (mi, -12, getdate())

select * from @.whatTime
where aTime >= getdate() - cast ('0:30:00.00' as datetime)

-- aTime
--
-- 2007-03-08 15:12:45.920

Another alternative would be something like:

select * frm @.whatTime
where aTIme >= dateadd (mi, -30, getdate())

|||

It wasn't clear from your original post exactly what time/date units the '30' (that you want to subtract) should be in.

Kent provided examples that will subtract 30 minutes, for a wider range of units for use in his DATEADD example then see the following - quoted from BOL:

DATEADD (datepart , number, date )

Datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms

Chris

|||

Oh, brother! You are SOO right! I think the question had to do with days and not minutes. :-) ( Still laughing at myself! )

If it is days try:

getdate() - 30

That should work fine.

|||I am laughing too!!! I was referring to days, and the gedate works fine. Thanks|||

Chris:

I so appreciate your answer. I had bricked this so badly and you did such a good job of re-directing AND leaving me an easy out. I really admire the work with words.

Kent

|||

No worries. :) Your answers were fundamentally correct - I also interpreted the requirement as being for 30 minutes when I first read the original post.

Thanks for the info over GETDATE() - x, I didn't realise you could do that...

Cheers
Chris

Date parameters in ="select.......where creationdate="&...."

Hi there,
I'm looking for the correct syntax for using datetime parameters in the SQL
statement.
The parameter is called ReceiveDate and defaults to =Today()
="select ...... where creationdate = "&.....
Thanks
LudoWhere you are writing the statemnt? In report or in stored procedure ?
If it is in report itself you can do as given belo:
If you declare ReceiveDate as DateTime Type. you can write the sql statement
directly .
Select ...... Where creationdate = @.ReceiveDate
If you Declare it as String then you need to use date conversion function
Select ...... Where creationdate = CDate(@.ReceiveDate)|||I think the issue here is that you are using an expression which is really a
mistake unless there is no other way. If you use the generic query designer
(two pane) then just do this:
select ... where creationdate = @.ReceiveDate
You don't have to do anything special. RS takes care of everything. If you
use an expression then you have to do a lot more (for instance, embed single
quotes).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
news:u670fn90FHA.1256@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I'm looking for the correct syntax for using datetime parameters in the
> SQL statement.
> The parameter is called ReceiveDate and defaults to =Today()
> ="select ...... where creationdate = "&.....
> Thanks
> Ludo
>|||Thanks,
it is in the report (RS 2005). It works when I put the parameter to string
and use query:
select part_number,serial_number from UNIT_STATUS_V where creation_time
between @.StartDate and @.EndDate
creation_time is a DateTime field.
But I'd rather use the parameter as a DateTime as well since then I get a
calender pick control.
I tried using CDate but with no result so far...
Ludo
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:O0VYnH%230FHA.612@.TK2MSFTNGP10.phx.gbl...
>I think the issue here is that you are using an expression which is really
>a mistake unless there is no other way. If you use the generic query
>designer (two pane) then just do this:
> select ... where creationdate = @.ReceiveDate
> You don't have to do anything special. RS takes care of everything. If you
> use an expression then you have to do a lot more (for instance, embed
> single quotes).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
> news:u670fn90FHA.1256@.TK2MSFTNGP09.phx.gbl...
>> Hi there,
>> I'm looking for the correct syntax for using datetime parameters in the
>> SQL statement.
>> The parameter is called ReceiveDate and defaults to =Today()
>> ="select ...... where creationdate = "&.....
>> Thanks
>> Ludo
>|||Thanks,
it is in the report (RS 2005). It works when I put the parameter to string
and use query:
select part_number,serial_number from UNIT_STATUS_V where creation_time
between @.StartDate and @.EndDate
creation_time is a DateTime field.
But I'd rather use the parameter as a DateTime as well since then I get a
calender pick control.
I tried using CDate but with no result so far...
Ludo
"Rama Prasad" <RamaPrasad@.discussions.microsoft.com> wrote in message
news:827DCC76-9E60-40EF-9065-EBB1D9B4A335@.microsoft.com...
> Where you are writing the statemnt? In report or in stored procedure ?
> If it is in report itself you can do as given belo:
> If you declare ReceiveDate as DateTime Type. you can write the sql
> statement
> directly .
> Select ...... Where creationdate = @.ReceiveDate
> If you Declare it as String then you need to use date conversion function
> Select ...... Where creationdate = CDate(@.ReceiveDate)
>|||This is very odd. RS should be handling this. There is no reason for you to
have to use CDate or anything like that. Try changing your SQL to be like
this:
select part_number,serial_number from UNIT_STATUS_V where creation_time >=@.StartDate and creation_time <= @.EndDate
You definitely should be able to have the parameter be a datetime.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
news:ONBhiqI1FHA.3780@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> it is in the report (RS 2005). It works when I put the parameter to string
> and use query:
> select part_number,serial_number from UNIT_STATUS_V where creation_time
> between @.StartDate and @.EndDate
> creation_time is a DateTime field.
> But I'd rather use the parameter as a DateTime as well since then I get a
> calender pick control.
> I tried using CDate but with no result so far...
> Ludo
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:O0VYnH%230FHA.612@.TK2MSFTNGP10.phx.gbl...
>>I think the issue here is that you are using an expression which is really
>>a mistake unless there is no other way. If you use the generic query
>>designer (two pane) then just do this:
>> select ... where creationdate = @.ReceiveDate
>> You don't have to do anything special. RS takes care of everything. If
>> you use an expression then you have to do a lot more (for instance, embed
>> single quotes).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
>> news:u670fn90FHA.1256@.TK2MSFTNGP09.phx.gbl...
>> Hi there,
>> I'm looking for the correct syntax for using datetime parameters in the
>> SQL statement.
>> The parameter is called ReceiveDate and defaults to =Today()
>> ="select ...... where creationdate = "&.....
>> Thanks
>> Ludo
>>
>|||Hi again....
thanks for your help so far, I found the problem and I'll try to explain.
My date settings are set to british "dd/MM/yyyy". When I do the preview of
the report the EndDate parameter defaults to =DateAdd("d",-1,Today()) and
the StartDate to =Today(). The readable fields read 19/10/2005 and
20/10/2005 which look fine. When I click on the preview tab the report runs
but when I click on view report the system returns an erro on the date
parameter. Now...when I select a date in the calender picker the readable
fields shows the date correctly like dd/MM/yyyy but the report returns the
same error. But when I select fi 08/08/2005 till 09/08/2005 and I click
view report the report runs and the 2 visible fields previously filled by
the calender picker change to 08/08/2005 till 08/09/2005 so the reports runs
from august 8th till september 8th ...I don't know the reason but when I
deploy the report it seems to run correctly, so the problem only occurs in
the preview....
Could this be a bug in the RS 2005 or do you know some settings I need to
check ?
Anyway many thanks for your help.
Ludo Van Dun
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eMgscQL1FHA.2540@.TK2MSFTNGP09.phx.gbl...
> This is very odd. RS should be handling this. There is no reason for you
> to have to use CDate or anything like that. Try changing your SQL to be
> like this:
> select part_number,serial_number from UNIT_STATUS_V where creation_time >=> @.StartDate and creation_time <= @.EndDate
> You definitely should be able to have the parameter be a datetime.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
> news:ONBhiqI1FHA.3780@.TK2MSFTNGP12.phx.gbl...
>> Thanks,
>> it is in the report (RS 2005). It works when I put the parameter to
>> string and use query:
>> select part_number,serial_number from UNIT_STATUS_V where creation_time
>> between @.StartDate and @.EndDate
>> creation_time is a DateTime field.
>> But I'd rather use the parameter as a DateTime as well since then I get a
>> calender pick control.
>> I tried using CDate but with no result so far...
>> Ludo
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:O0VYnH%230FHA.612@.TK2MSFTNGP10.phx.gbl...
>>I think the issue here is that you are using an expression which is
>>really a mistake unless there is no other way. If you use the generic
>>query designer (two pane) then just do this:
>> select ... where creationdate = @.ReceiveDate
>> You don't have to do anything special. RS takes care of everything. If
>> you use an expression then you have to do a lot more (for instance,
>> embed single quotes).
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ludo Van Dun" <ludovandun@.solectron.com> wrote in message
>> news:u670fn90FHA.1256@.TK2MSFTNGP09.phx.gbl...
>> Hi there,
>> I'm looking for the correct syntax for using datetime parameters in the
>> SQL statement.
>> The parameter is called ReceiveDate and defaults to =Today()
>> ="select ...... where creationdate = "&.....
>> Thanks
>> Ludo
>>
>>
>

Date Parameter Procedure

I am trying to create a Parameter for entering a Date Range for a report,
here is what I think the syntax should look like, but it is wrong;
"Create Procedure procAccrual Aging
as
Select Distinct Receiptdate
from pop30310
where (pop30310.receiptdate between @.BeginDate and @.EndDate)"
As you seen, I am trying to create a beginning and ending range parameter.
Thank you,
RyanOn Jun 17, 7:49 pm, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
wrote:
> I am trying to create a Parameter for entering a Date Range for a report,
> here is what I think the syntax should look like, but it is wrong;
> "Create Procedure procAccrual Aging
> as
> Select Distinct Receiptdate
> from pop30310
> where (pop30310.receiptdate between @.BeginDate and @.EndDate)"
> As you seen, I am trying to create a beginning and ending range parameter.
> Thank you,
> Ryan
If I'm understanding you correctly, you will want to create your
stored procedure for your report like this:
Create Procedure procAccrual Aging
@.BeginDate DATETIME,
@.EndDate DATETIME
as
Select Distinct Receiptdate
from pop30310
where (pop30310.receiptdate between @.BeginDate and @.EndDate)
Then have the @.BeginDate and @.EndDate report parameters based on a
standard calendar datetime control (default for datetime parameters).
Then in the Data view, where you define the dataset for the stored
procedure procAccrual Aging then in the Parameters tab of the Edit
Dataset [...] section, select the report parameters. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Ed,
The procedure works now, but when I preview the report and enter a range,
the data still spits out the same. Is there some linking that needs to be
done?
Thanks,
Ryan
"EMartinez" wrote:
> On Jun 17, 7:49 pm, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
> wrote:
> > I am trying to create a Parameter for entering a Date Range for a report,
> > here is what I think the syntax should look like, but it is wrong;
> >
> > "Create Procedure procAccrual Aging
> > as
> > Select Distinct Receiptdate
> > from pop30310
> > where (pop30310.receiptdate between @.BeginDate and @.EndDate)"
> >
> > As you seen, I am trying to create a beginning and ending range parameter.
> >
> > Thank you,
> >
> > Ryan
>
> If I'm understanding you correctly, you will want to create your
> stored procedure for your report like this:
> Create Procedure procAccrual Aging
> @.BeginDate DATETIME,
> @.EndDate DATETIME
> as
> Select Distinct Receiptdate
> from pop30310
> where (pop30310.receiptdate between @.BeginDate and @.EndDate)
> Then have the @.BeginDate and @.EndDate report parameters based on a
> standard calendar datetime control (default for datetime parameters).
> Then in the Data view, where you define the dataset for the stored
> procedure procAccrual Aging then in the Parameters tab of the Edit
> Dataset [...] section, select the report parameters. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Jun 18, 10:00 am, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
wrote:
> Ed,
> The procedure works now, but when I preview the report and enter a range,
> the data still spits out the same. Is there some linking that needs to be
> done?
> Thanks,
> Ryan
> "EMartinez" wrote:
> > On Jun 17, 7:49 pm, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
> > wrote:
> > > I am trying to create a Parameter for entering a Date Range for a report,
> > > here is what I think the syntax should look like, but it is wrong;
> > > "Create Procedure procAccrual Aging
> > > as
> > > Select Distinct Receiptdate
> > > from pop30310
> > > where (pop30310.receiptdate between @.BeginDate and @.EndDate)"
> > > As you seen, I am trying to create a beginning and ending range parameter.
> > > Thank you,
> > > Ryan
> > If I'm understanding you correctly, you will want to create your
> > stored procedure for your report like this:
> > Create Procedure procAccrual Aging
> > @.BeginDate DATETIME,
> > @.EndDate DATETIME
> > as
> > Select Distinct Receiptdate
> > from pop30310
> > where (pop30310.receiptdate between @.BeginDate and @.EndDate)
> > Then have the @.BeginDate and @.EndDate report parameters based on a
> > standard calendar datetime control (default for datetime parameters).
> > Then in the Data view, where you define the dataset for the stored
> > procedure procAccrual Aging then in the Parameters tab of the Edit
> > Dataset [...] section, select the report parameters. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
I'm assuming you are addressing me. Have you linked the parameters
that the stored procedure is expecting in the Data tab view with the
report parameters? You would check this via selecting the dataset that
references the stored procedure in the Data view, select the [...]
button to the right of the dataset, for Edit dataset, then select the
Parameters tab and set/verify that @.BeginDate, @.EndDate have the
correct Report parameters associated with them. The values should be
whatever your report parameters are named (i.e., Parameters!
BeginDate.Value and Parameters!EndDate.Value). Hope this clarifies
things for you.
Regards,
Enrique Martinez
Sr. Software Consultant|||Enrique,
Thanks for all of your help? What is your address? I am going to have to
send you a bottle of Scotch my friend!
Ryan
"EMartinez" wrote:
> On Jun 18, 10:00 am, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
> wrote:
> > Ed,
> > The procedure works now, but when I preview the report and enter a range,
> > the data still spits out the same. Is there some linking that needs to be
> > done?
> >
> > Thanks,
> > Ryan
> >
> > "EMartinez" wrote:
> > > On Jun 17, 7:49 pm, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
> > > wrote:
> > > > I am trying to create a Parameter for entering a Date Range for a report,
> > > > here is what I think the syntax should look like, but it is wrong;
> >
> > > > "Create Procedure procAccrual Aging
> > > > as
> > > > Select Distinct Receiptdate
> > > > from pop30310
> > > > where (pop30310.receiptdate between @.BeginDate and @.EndDate)"
> >
> > > > As you seen, I am trying to create a beginning and ending range parameter.
> >
> > > > Thank you,
> >
> > > > Ryan
> >
> > > If I'm understanding you correctly, you will want to create your
> > > stored procedure for your report like this:
> > > Create Procedure procAccrual Aging
> > > @.BeginDate DATETIME,
> > > @.EndDate DATETIME
> > > as
> >
> > > Select Distinct Receiptdate
> > > from pop30310
> > > where (pop30310.receiptdate between @.BeginDate and @.EndDate)
> >
> > > Then have the @.BeginDate and @.EndDate report parameters based on a
> > > standard calendar datetime control (default for datetime parameters).
> > > Then in the Data view, where you define the dataset for the stored
> > > procedure procAccrual Aging then in the Parameters tab of the Edit
> > > Dataset [...] section, select the report parameters. Hope this helps.
> >
> > > Regards,
> >
> > > Enrique Martinez
> > > Sr. Software Consultant
>
> I'm assuming you are addressing me. Have you linked the parameters
> that the stored procedure is expecting in the Data tab view with the
> report parameters? You would check this via selecting the dataset that
> references the stored procedure in the Data view, select the [...]
> button to the right of the dataset, for Edit dataset, then select the
> Parameters tab and set/verify that @.BeginDate, @.EndDate have the
> correct Report parameters associated with them. The values should be
> whatever your report parameters are named (i.e., Parameters!
> BeginDate.Value and Parameters!EndDate.Value). Hope this clarifies
> things for you.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Jun 18, 1:34 pm, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
wrote:
> Enrique,
> Thanks for all of your help? What is your address? I am going to have to
> send you a bottle of Scotch my friend!
> Ryan
> "EMartinez" wrote:
> > On Jun 18, 10:00 am, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
> > wrote:
> > > Ed,
> > > The procedure works now, but when I preview the report and enter a range,
> > > the data still spits out the same. Is there some linking that needs to be
> > > done?
> > > Thanks,
> > > Ryan
> > > "EMartinez" wrote:
> > > > On Jun 17, 7:49 pm, Ryan Mcbee <RyanMc...@.discussions.microsoft.com>
> > > > wrote:
> > > > > I am trying to create a Parameter for entering a Date Range for a report,
> > > > > here is what I think the syntax should look like, but it is wrong;
> > > > > "Create Procedure procAccrual Aging
> > > > > as
> > > > > Select Distinct Receiptdate
> > > > > from pop30310
> > > > > where (pop30310.receiptdate between @.BeginDate and @.EndDate)"
> > > > > As you seen, I am trying to create a beginning and ending range parameter.
> > > > > Thank you,
> > > > > Ryan
> > > > If I'm understanding you correctly, you will want to create your
> > > > stored procedure for your report like this:
> > > > Create Procedure procAccrual Aging
> > > > @.BeginDate DATETIME,
> > > > @.EndDate DATETIME
> > > > as
> > > > Select Distinct Receiptdate
> > > > from pop30310
> > > > where (pop30310.receiptdate between @.BeginDate and @.EndDate)
> > > > Then have the @.BeginDate and @.EndDate report parameters based on a
> > > > standard calendar datetime control (default for datetime parameters).
> > > > Then in the Data view, where you define the dataset for the stored
> > > > procedure procAccrual Aging then in the Parameters tab of the Edit
> > > > Dataset [...] section, select the report parameters. Hope this helps.
> > > > Regards,
> > > > Enrique Martinez
> > > > Sr. Software Consultant
> > I'm assuming you are addressing me. Have you linked the parameters
> > that the stored procedure is expecting in the Data tab view with the
> > report parameters? You would check this via selecting the dataset that
> > references the stored procedure in the Data view, select the [...]
> > button to the right of the dataset, for Edit dataset, then select the
> > Parameters tab and set/verify that @.BeginDate, @.EndDate have the
> > correct Report parameters associated with them. The values should be
> > whatever your report parameters are named (i.e., Parameters!
> > BeginDate.Value and Parameters!EndDate.Value). Hope this clarifies
> > things for you.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
Glad I could be of assistance. Thanks for the offer, however, I'm not
much into drinking.
Best Regards,
Enrique

Date Function

In SQL 2000 I need to return all records where the date is between date() and date()-365. I can not seem to get the syntax right. Any help would be appriciated.Try this:
where date between dateadd(dd,-365,getdate()) and getdate()

Good Luck.|||you can try also this

where date between dateadd(yy,-1,getdate()) and getdate()|||

Quote:

Originally Posted by iburyak

Try this:
where date between dateadd(dd,-365,getdate()) and getdate()

Good Luck.

Thanks, that did the trick

Date formatting

Can some one help me with the syntax to change the datetime data type to the
following format:
mmm yy
Example: Feb 05
Thanks= Format(Fields!myDateField.Value,"dd MMM")
"anthonysjo" wrote:
> Can some one help me with the syntax to change the datetime data type to the
> following format:
> mmm yy
> Example: Feb 05
> Thanks|||Can this syntax only be used in SRS or can I use it when writing queries in
query analyzer or when building views?
"Andre" wrote:
> = Format(Fields!myDateField.Value,"dd MMM")
> "anthonysjo" wrote:
> > Can some one help me with the syntax to change the datetime data type to the
> > following format:
> > mmm yy
> > Example: Feb 05
> >
> > Thanks|||For SQL I usually use syntax like this, but the datatype is not datetime
anymore:
Select datename(dd,myDate) + ' ' + datename(mm,myDate) from MyTable
OR
Select datename(dd,myDate) + ' ' + datename(mm,myDate) + ' ' +
datename(yy,myDate) from MyTable
Andre
"anthonysjo" wrote:
> Can this syntax only be used in SRS or can I use it when writing queries in
> query analyzer or when building views?
> "Andre" wrote:
> > = Format(Fields!myDateField.Value,"dd MMM")
> >
> > "anthonysjo" wrote:
> >
> > > Can some one help me with the syntax to change the datetime data type to the
> > > following format:
> > > mmm yy
> > > Example: Feb 05
> > >
> > > Thanks|||One last question...with mm the month comes back as January. Can I get it
to display just Jan?
"Andre" wrote:
> For SQL I usually use syntax like this, but the datatype is not datetime
> anymore:
> Select datename(dd,myDate) + ' ' + datename(mm,myDate) from MyTable
> OR
> Select datename(dd,myDate) + ' ' + datename(mm,myDate) + ' ' +
> datename(yy,myDate) from MyTable
> Andre
>
> "anthonysjo" wrote:
> > Can this syntax only be used in SRS or can I use it when writing queries in
> > query analyzer or when building views?
> >
> > "Andre" wrote:
> >
> > > = Format(Fields!myDateField.Value,"dd MMM")
> > >
> > > "anthonysjo" wrote:
> > >
> > > > Can some one help me with the syntax to change the datetime data type to the
> > > > following format:
> > > > mmm yy
> > > > Example: Feb 05
> > > >
> > > > Thanks|||You might also try
select convert(char(6),getdate(),107)
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:2DBB1EA5-B231-49FB-A548-1CAE3E763B1E@.microsoft.com...
> For SQL I usually use syntax like this, but the datatype is not datetime
> anymore:
> Select datename(dd,myDate) + ' ' + datename(mm,myDate) from MyTable
> OR
> Select datename(dd,myDate) + ' ' + datename(mm,myDate) + ' ' +
> datename(yy,myDate) from MyTable
> Andre
>
> "anthonysjo" wrote:
>> Can this syntax only be used in SRS or can I use it when writing queries
>> in
>> query analyzer or when building views?
>> "Andre" wrote:
>> > = Format(Fields!myDateField.Value,"dd MMM")
>> >
>> > "anthonysjo" wrote:
>> >
>> > > Can some one help me with the syntax to change the datetime data type
>> > > to the
>> > > following format:
>> > > mmm yy
>> > > Example: Feb 05
>> > >
>> > > Thanks|||Where do I define the field I want to use in this example?
select convert(char(6),getdate(),107)
"Wayne Snyder" wrote:
> You might also try
> select convert(char(6),getdate(),107)
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Andre" <Andre@.discussions.microsoft.com> wrote in message
> news:2DBB1EA5-B231-49FB-A548-1CAE3E763B1E@.microsoft.com...
> > For SQL I usually use syntax like this, but the datatype is not datetime
> > anymore:
> >
> > Select datename(dd,myDate) + ' ' + datename(mm,myDate) from MyTable
> > OR
> > Select datename(dd,myDate) + ' ' + datename(mm,myDate) + ' ' +
> > datename(yy,myDate) from MyTable
> >
> > Andre
> >
> >
> > "anthonysjo" wrote:
> >
> >> Can this syntax only be used in SRS or can I use it when writing queries
> >> in
> >> query analyzer or when building views?
> >>
> >> "Andre" wrote:
> >>
> >> > = Format(Fields!myDateField.Value,"dd MMM")
> >> >
> >> > "anthonysjo" wrote:
> >> >
> >> > > Can some one help me with the syntax to change the datetime data type
> >> > > to the
> >> > > following format:
> >> > > mmm yy
> >> > > Example: Feb 05
> >> > >
> >> > > Thanks
>
>|||replace the getdate().
select convert(char(6),somefield,107) from sometable
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
news:FD07C54C-0108-42EF-A32D-908BC5D1903E@.microsoft.com...
> Where do I define the field I want to use in this example?
> select convert(char(6),getdate(),107)
> "Wayne Snyder" wrote:
>> You might also try
>> select convert(char(6),getdate(),107)
>> --
>> Wayne Snyder MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> (Please respond only to the newsgroup.)
>> I support the Professional Association for SQL Server ( PASS) and it's
>> community of SQL Professionals.
>> "Andre" <Andre@.discussions.microsoft.com> wrote in message
>> news:2DBB1EA5-B231-49FB-A548-1CAE3E763B1E@.microsoft.com...
>> > For SQL I usually use syntax like this, but the datatype is not
>> > datetime
>> > anymore:
>> >
>> > Select datename(dd,myDate) + ' ' + datename(mm,myDate) from MyTable
>> > OR
>> > Select datename(dd,myDate) + ' ' + datename(mm,myDate) + ' ' +
>> > datename(yy,myDate) from MyTable
>> >
>> > Andre
>> >
>> >
>> > "anthonysjo" wrote:
>> >
>> >> Can this syntax only be used in SRS or can I use it when writing
>> >> queries
>> >> in
>> >> query analyzer or when building views?
>> >>
>> >> "Andre" wrote:
>> >>
>> >> > = Format(Fields!myDateField.Value,"dd MMM")
>> >> >
>> >> > "anthonysjo" wrote:
>> >> >
>> >> > > Can some one help me with the syntax to change the datetime data
>> >> > > type
>> >> > > to the
>> >> > > following format:
>> >> > > mmm yy
>> >> > > Example: Feb 05
>> >> > >
>> >> > > Thanks
>>|||Ok that getst the Month format correct but the year is wrong now...I get
things like Jan 01, Jan 08, Jan 15, Jan 22, Jan 29, etc...which are the weeks
for which each period closes. How do I get it to come out as 3char month and
year Like Jan 05?
convert(char(6), [EP].[PRD_FINISH_DATE],107)AS [Month]
"Bruce L-C [MVP]" wrote:
> replace the getdate().
> select convert(char(6),somefield,107) from sometable
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
> news:FD07C54C-0108-42EF-A32D-908BC5D1903E@.microsoft.com...
> > Where do I define the field I want to use in this example?
> > select convert(char(6),getdate(),107)
> >
> > "Wayne Snyder" wrote:
> >
> >> You might also try
> >>
> >> select convert(char(6),getdate(),107)
> >>
> >> --
> >> Wayne Snyder MCDBA, SQL Server MVP
> >> Mariner, Charlotte, NC
> >> (Please respond only to the newsgroup.)
> >>
> >> I support the Professional Association for SQL Server ( PASS) and it's
> >> community of SQL Professionals.
> >> "Andre" <Andre@.discussions.microsoft.com> wrote in message
> >> news:2DBB1EA5-B231-49FB-A548-1CAE3E763B1E@.microsoft.com...
> >> > For SQL I usually use syntax like this, but the datatype is not
> >> > datetime
> >> > anymore:
> >> >
> >> > Select datename(dd,myDate) + ' ' + datename(mm,myDate) from MyTable
> >> > OR
> >> > Select datename(dd,myDate) + ' ' + datename(mm,myDate) + ' ' +
> >> > datename(yy,myDate) from MyTable
> >> >
> >> > Andre
> >> >
> >> >
> >> > "anthonysjo" wrote:
> >> >
> >> >> Can this syntax only be used in SRS or can I use it when writing
> >> >> queries
> >> >> in
> >> >> query analyzer or when building views?
> >> >>
> >> >> "Andre" wrote:
> >> >>
> >> >> > = Format(Fields!myDateField.Value,"dd MMM")
> >> >> >
> >> >> > "anthonysjo" wrote:
> >> >> >
> >> >> > > Can some one help me with the syntax to change the datetime data
> >> >> > > type
> >> >> > > to the
> >> >> > > following format:
> >> >> > > mmm yy
> >> >> > > Example: Feb 05
> >> >> > >
> >> >> > > Thanks
> >>
> >>
> >>
>
>|||Look in books online for SQL Server. It provides all your different options
for convert (there are a lot of options).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
news:8C98B402-8A2F-4DCA-AE62-6985F5496FE6@.microsoft.com...
> Ok that getst the Month format correct but the year is wrong now...I get
> things like Jan 01, Jan 08, Jan 15, Jan 22, Jan 29, etc...which are the
> weeks
> for which each period closes. How do I get it to come out as 3char month
> and
> year Like Jan 05?
> convert(char(6), [EP].[PRD_FINISH_DATE],107)AS [Month]
> "Bruce L-C [MVP]" wrote:
>> replace the getdate().
>> select convert(char(6),somefield,107) from sometable
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
>> news:FD07C54C-0108-42EF-A32D-908BC5D1903E@.microsoft.com...
>> > Where do I define the field I want to use in this example?
>> > select convert(char(6),getdate(),107)
>> >
>> > "Wayne Snyder" wrote:
>> >
>> >> You might also try
>> >>
>> >> select convert(char(6),getdate(),107)
>> >>
>> >> --
>> >> Wayne Snyder MCDBA, SQL Server MVP
>> >> Mariner, Charlotte, NC
>> >> (Please respond only to the newsgroup.)
>> >>
>> >> I support the Professional Association for SQL Server ( PASS) and it's
>> >> community of SQL Professionals.
>> >> "Andre" <Andre@.discussions.microsoft.com> wrote in message
>> >> news:2DBB1EA5-B231-49FB-A548-1CAE3E763B1E@.microsoft.com...
>> >> > For SQL I usually use syntax like this, but the datatype is not
>> >> > datetime
>> >> > anymore:
>> >> >
>> >> > Select datename(dd,myDate) + ' ' + datename(mm,myDate) from MyTable
>> >> > OR
>> >> > Select datename(dd,myDate) + ' ' + datename(mm,myDate) + ' ' +
>> >> > datename(yy,myDate) from MyTable
>> >> >
>> >> > Andre
>> >> >
>> >> >
>> >> > "anthonysjo" wrote:
>> >> >
>> >> >> Can this syntax only be used in SRS or can I use it when writing
>> >> >> queries
>> >> >> in
>> >> >> query analyzer or when building views?
>> >> >>
>> >> >> "Andre" wrote:
>> >> >>
>> >> >> > = Format(Fields!myDateField.Value,"dd MMM")
>> >> >> >
>> >> >> > "anthonysjo" wrote:
>> >> >> >
>> >> >> > > Can some one help me with the syntax to change the datetime
>> >> >> > > data
>> >> >> > > type
>> >> >> > > to the
>> >> >> > > following format:
>> >> >> > > mmm yy
>> >> >> > > Example: Feb 05
>> >> >> > >
>> >> >> > > Thanks
>> >>
>> >>
>> >>
>>

DATE FORMAT/SYNTAX QUESTION

What getdate() syntax command can give me time in the following format:

10:41:55 AM

Regards,

Addi"addi" <addi_s@.hotmail.com> wrote in message news:llnGb.31580$zU5.19873@.news01.roc.ny...
> What getdate() syntax command can give me time in the following format:
> 10:41:55 AM
> Regards,
> Addi

SELECT CAST(((DATEPART(HOUR, CURRENT_TIMESTAMP) + 11) % 12) + 1
AS VARCHAR(2)) +
':' +
RIGHT('0' + DATENAME(MINUTE, CURRENT_TIMESTAMP), 2) +
':' +
RIGHT('0' + DATENAME(SECOND, CURRENT_TIMESTAMP), 2) +
CASE WHEN DATEPART(HOUR, CURRENT_TIMESTAMP) < 12
THEN ' AM'
ELSE ' PM'
END AS current_time

current_time
5:22:57 PM

Regards,
jag|||Note that John's solution may be ugly but remember that SQL is not a
report-writing language. It's usually better to format results in the
front-end. Many languages provide robust date formatting functions.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"addi" <addi_s@.hotmail.com> wrote in message
news:llnGb.31580$zU5.19873@.news01.roc.ny...
> What getdate() syntax command can give me time in the following format:
> 10:41:55 AM
> Regards,
> Addi

Date conversion ?

Is use this stored procedure.
This is the error mesage: "Syntax error converting datetime from character string"

Please help me !

Alter Procedure "Selectie_Date_Tabel" (@.datainceput datetime, @.datasfirsit datetime,@.Grupa AS nvarchar(20))

As

set nocount on

DECLARE @.NEWLINE AS char(1)

SET @.NEWLINE = CHAR(10)

DECLARE @.keyssql AS varchar(1000)

SET @.keyssql = 'SELECT * FROM View2'
+ @.NEWLINE + 'WHERE [Cod grupa] = ' + CHAR(39) + @.Grupa + CHAR(39)
+ @.NEWLINE + 'AND ([Day] BETWEEN ' + CONVERT(DATETIME, @.datainceput , 120) + ' AND ' + CONVERT(DATETIME, @.datasfirsit , 120) +')'

EXEC (@.keyssql)What parameters are you using to execute the stored procedure ?|||@.datainceput DATETIME
@.datasfirsit DATETIME

datainceput = 01.01.2004
datasfirsit = 15.01.2004

I want to make a SQL_String something like this :

SQL_String = 'SELECT * FROM TABLE WHERE ' .... date condition

EXECUTE (SQL_String)

All this inside a stored procedure

Sorry for my english|||I have two option

1. Sp_1

SELECT * FROM TABLE WHERE ............

Is ok, work

2. Sp_2

DECLARE @.keyssql AS varchar(8000)
SET @.keyssql ='SELECT * FROM TABLE WHERE' + 'Condition'

EXECUTE (@.keyssql) -- This line is inside at the same stored procedure.

This stored procedure Sp_2 don`t work|||Enjoy ...

Alter Procedure "Selectie_Date_Tabel" (@.datainceput datetime, @.datasfirsit datetime,@.Grupa AS nvarchar(20))

As

set nocount on

DECLARE @.NEWLINE AS char(1)

SET @.NEWLINE = CHAR(10)

DECLARE @.keyssql AS varchar(1000)

SET @.keyssql = 'SELECT * FROM View2'
+ @.NEWLINE + 'WHERE [Cod grupa] = ' + CHAR(39) + @.Grupa + CHAR(39)
+ @.NEWLINE + 'AND ([Day] BETWEEN ' + CONVERT(DATETIME, @.datainceput , 104) + ' AND ' + CONVERT(DATETIME, @.datasfirsit , 104) +')'

EXEC (@.keyssql)|||ALTER PROCEDURE SP_2
As
set nocount on

DECLARE @.keyssql AS varchar(8000)

SET @.keyssql = 'SELECT * FROM View2 WHERE (Data = CONVERT(DATETIME,' +CHAR(39)+ '2004-01-05 00:00:00'+CHAR(39)+', 102))'

EXEC (@.keyssql)
/*--------------*/

This SP work OK.

I want to use a parameter inside '2004-01-05 00:00:00'

Atention EXEC (@.keyssql) is inside a SP|||Enigma, Sorry don't work ........|||didnt get that !!! did the sp not work ?|||This is the original SP
But an solution for the precedent example it would usefull for me.

Alter Procedure sp_CrossTab
@.table AS sysname,
@.onrows AS nvarchar(128),
@.onrowsalias AS sysname = NULL,
@.oncols AS nvarchar(128),
@.sumcol AS sysname = NULL,
@.avgcol AS sysname = NULL,
@.Grupa AS nvarchar(20),
@.datainceput AS datetime,
@.datasfirsit AS datetime

AS
set nocount on

DECLARE @.sql AS varchar(8000), @.NEWLINE AS char(1)

SET @.NEWLINE = CHAR(10)

SET @.table = '['+ @.table + ']'
SET @.oncols = '['+ @.oncols + ']'
SET @.onrows = '['+ @.onrows + ']'
SET @.sumcol = '['+ @.sumcol + ']'
SET @.avgcol = '['+ @.avgcol + ']'

SET @.sql ='SELECT' + @.NEWLINE
+ 'DATEPART(ww,' + @.onrows+ ') AS Saptamina,' + ' '
+ 'DATEPART(mm,' + @.onrows+ ') AS Luna,' + ' '
+ 'DATEPART(yyyy,' + @.onrows+ ') AS Anul,' + ' '
+ @.onrows +

CASE
WHEN @.onrowsalias IS NOT NULL THEN ' AS ' + @.onrowsalias
ELSE ''
END

CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

DECLARE @.keyssql AS varchar(1000)

/* THIS PART DON'T WORK */

SET @.keyssql = 'INSERT INTO #keys ' +'SELECT DISTINCT CAST(' + @.oncols + ' AS nvarchar(100)) ' +'FROM ' + @.table
+ @.NEWLINE + 'WHERE [Cod grupa] = ' + CHAR(39) + @.Grupa + CHAR(39)
+ @.NEWLINE + 'AND ([Data] BETWEEN ' + CONVERT(DATETIME, @.datainceput , 120) +' AND ' +CONVERT(DATETIME, @.datasfirsit , 120) +')'

/* THIS PART WORK OK*/
/*SET @.keyssql = 'INSERT INTO #keys ' +'SELECT DISTINCT CAST(' + @.oncols + ' AS nvarchar(100)) ' +'FROM ' + @.table*/

PRINT @.keyssql

EXEC (@.keyssql)

DECLARE @.key AS nvarchar(100)
SELECT @.key = MIN(keyvalue) FROM #keys

WHILE @.key IS NOT NULL
BEGIN
...........................|||Use This >>>>
SET @.keyssql = 'INSERT INTO #keys ' +'SELECT DISTINCT CAST(' + @.oncols + ' AS nvarchar(100)) ' +'FROM ' + @.table
+ @.NEWLINE + 'WHERE [Cod grupa] = ' + CHAR(39) + @.Grupa + CHAR(39)
+ @.NEWLINE + 'AND ([Data] BETWEEN ' + CONVERT(DATETIME, @.datainceput , 104) +' AND ' +CONVERT(DATETIME, @.datasfirsit , 104) +')'|||I try but the same error: "Error converting data type varchar to datetime"

Please help me,
Try an easy example:
One table with 3 columns (Day, Field1, Field2)
Create a SP and see if work.|||maybe i am not understanding the dateformat you are passing ...

check up convert in the holy book ("SQL Server Books Online") and insert the no corresponding to your input format|||I have a strong suspicion that the '15.01.2004' date is causing the problem. Whereever possible, you should feed 'yyyy-mm-dd' strings to the sql server. If you can't, then you need to declare your parameters as STRING instead of datetime since 15.01.2004 may not be a date if the date format on the Sql Server is set to 'mm-dd-yyyy' There is no 15th month (not on Earth, anyway ;)).