I have a datetime variable coming from my ASP.NET application that has
a time portion. I give my users the option to perform an equals,
greater than, less than, or between comparison. The trouble comes in
the way the application builds the criteria string. The WHERE clause
passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
What I want to do is only compare the date portion of start_dt to the
date portion of the passed in time. Manipulating the start_dt with the
built-in SQL functions isn't a problem, but altering the date passed in
from the ASP.NET would be a massive framework change in the app.
Is there any way to only compare the date portions of both the SQL
field and the passed in value?
Thanks.Create a stored procedure instead creating the statement dynamically.
create procedure dbo.usp_proc1
@.sd datetime
as
set nocount on
select c1, ..., cn
from table1
where
start_dt >= convert(char(8), @.sd, 112)
and start_dt < convert(char(8), dateadd(day, 1, @.sd), 112)
return @.@.error
go
AMB
"colinhumber" wrote:
> I have a datetime variable coming from my ASP.NET application that has
> a time portion. I give my users the option to perform an equals,
> greater than, less than, or between comparison. The trouble comes in
> the way the application builds the criteria string. The WHERE clause
> passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
> What I want to do is only compare the date portion of start_dt to the
> date portion of the passed in time. Manipulating the start_dt with the
> built-in SQL functions isn't a problem, but altering the date passed in
> from the ASP.NET would be a massive framework change in the app.
> Is there any way to only compare the date portions of both the SQL
> field and the passed in value?
> Thanks.|||You have to convert it to a the valid format you want to comapre it to, e.g.
(from BOL --> Convert)
CONVERT(varchar(8),YourdateinHere,112) which will apply iso date formatting
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"colinhumber" <colinhumber@.discussions.microsoft.com> schrieb im Newsbeitrag
news:AE4EA0BE-51D8-459F-A071-225F6833AFAB@.microsoft.com...
>I have a datetime variable coming from my ASP.NET application that has
> a time portion. I give my users the option to perform an equals,
> greater than, less than, or between comparison. The trouble comes in
> the way the application builds the criteria string. The WHERE clause
> passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
> What I want to do is only compare the date portion of start_dt to the
> date portion of the passed in time. Manipulating the start_dt with the
> built-in SQL functions isn't a problem, but altering the date passed in
> from the ASP.NET would be a massive framework change in the app.
> Is there any way to only compare the date portions of both the SQL
> field and the passed in value?
> Thanks.|||You need to consider using CONVERT fnc with RIGHT
or using DATEPART !
exemple :
right(convert(varchar, @.datetime, 112),10)
or
cast(datepart(hour,@.datetime) as varchar) + ':' +
cast(datepart(minute,@.datetime) as varchar) + ':' +
cast(datepart(second,@.datetime) as varchar)|||Thanks for the quick reply.
Doing the conversion on the start_dt isn't a problem, but as the value being
passed in from the app is in a dynamic string, performing some string
manipulation would be difficult as the string length could vary. The
frameworks as it stands uses dynamic criteria strings so changing that is no
t
an option. I was hoping there was a way to compare only the date portions
without too much manipulation.
"Jens Sü?meyer" wrote:
> You have to convert it to a the valid format you want to comapre it to, e.
g.
> (from BOL --> Convert)
> CONVERT(varchar(8),YourdateinHere,112) which will apply iso date formattin
g
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "colinhumber" <colinhumber@.discussions.microsoft.com> schrieb im Newsbeitr
ag
> news:AE4EA0BE-51D8-459F-A071-225F6833AFAB@.microsoft.com...
>
>|||DateDiff(day, 0, <AnyDate> ) strips off the time portion...
so
Where DateDiff(day, 0, start_dt) <Operator> DateDiff(day, 0, @.PassedInDate)
is one way to do this generically. (Happens to be really fast too.)
"colinhumber" wrote:
> I have a datetime variable coming from my ASP.NET application that has
> a time portion. I give my users the option to perform an equals,
> greater than, less than, or between comparison. The trouble comes in
> the way the application builds the criteria string. The WHERE clause
> passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
> What I want to do is only compare the date portion of start_dt to the
> date portion of the passed in time. Manipulating the start_dt with the
> built-in SQL functions isn't a problem, but altering the date passed in
> from the ASP.NET would be a massive framework change in the app.
> Is there any way to only compare the date portions of both the SQL
> field and the passed in value?
> Thanks.
Showing posts with label users. Show all posts
Showing posts with label users. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
Date Parameters
Hello,
I have some Reporting Services users who want to setup subscriptions for
reports and enter the current month, trailing 7 days, this week, etc. in the
start and end date parameter fields. Can they do this with code (i.e.
currentdate(-7)) or does this have to be setup as a schedule on the server?
Any assistance is appreciated
Thank you
David Mahler
dmahler@.superwarehouse.comDear David,
I am not sure if I understand your question well, but if you need to
address last week, you may use sql like
select ...
from ...
where date between dateadd(day,-7,getdate()) and getdate().
getdate() will give you current date-time, and dateadd will shift back
7 days.
See dateadd() for more help.
Best Regards,
leecz
On 1=D4=C24=C8=D5, =C9=CF=CE=E71=CA=B153=B7=D6, David Mahler <David
Mah...@.discussions.microsoft.com> wrote:
> Hello,
> I have some Reporting Services users who want to setup subscriptions for
> reports and enter the current month, trailing 7 days, this week, etc. in t=he
> start and end date parameter fields. Can they do this with code (i.e.
> currentdate(-7)) or does this have to be setup as a schedule on the server=?
> Any assistance is appreciated
> Thank you
> David Mahler
> dmah...@.superwarehouse.com|||give an exampl as how you would like to see the subscription..
On Jan 4, 11:41 am, leecz <li.cheng...@.gmail.com> wrote:
> Dear David,
> I am not sure if I understand your question well, but if you need to
> address last week, you may use sql like
> select ...
> from ...
> where date between dateadd(day,-7,getdate()) and getdate().
> getdate() will give you current date-time, and dateadd will shift back
> 7 days.
> See dateadd() for more help.
> Best Regards,
> leecz
> On 1=D4=C24=C8=D5, =C9=CF=CE=E71=CA=B153=B7=D6, David Mahler <David
>
> Mah...@.discussions.microsoft.com> wrote:
> > Hello,
> > I have some Reporting Services users who want to setup subscriptions for=
> > reports and enter the current month, trailing 7 days, this week, etc. in= the
> > start and end date parameter fields. Can they do this with code (i.e.
> > currentdate(-7)) or does this have to be setup as a schedule on the serv=er?
> > Any assistance is appreciated
> > Thank you
> > David Mahler
> > dmah...@.superwarehouse.com- Hide quoted text -
> - Show quoted text -|||Thanks for your replies. I apologize for not being clear in my description.
I am referrring to the front end web interface where users can setup their
own subscriptions.
The user creates a new subscription for a report. The select the delivery
method, how often the report runs, and other parrameters. When they enter
the Start Date and End Date (might be with the calendar control), could they
enter a getdate(-7) or something like that to run the report for the current
month, trailing week, etc.
My thought is that this may have to be done on the back end. I would hope
that the user can set the report to run every week and get the current week's
data on an automated basis.
Thanks for your help
David
"Sridar K" wrote:
> give an exampl as how you would like to see the subscription..
> On Jan 4, 11:41 am, leecz <li.cheng...@.gmail.com> wrote:
> > Dear David,
> > I am not sure if I understand your question well, but if you need to
> > address last week, you may use sql like
> > select ...
> > from ...
> > where date between dateadd(day,-7,getdate()) and getdate().
> > getdate() will give you current date-time, and dateadd will shift back
> > 7 days.
> > See dateadd() for more help.
> >
> > Best Regards,
> > leecz
> >
> > On 1æ'4æ?¥, ä¸?å'1æ?¶53å', David Mahler <David
> >
> >
> >
> > Mah...@.discussions.microsoft.com> wrote:
> > > Hello,
> >
> > > I have some Reporting Services users who want to setup subscriptions for
> > > reports and enter the current month, trailing 7 days, this week, etc. in the
> > > start and end date parameter fields. Can they do this with code (i.e.
> > > currentdate(-7)) or does this have to be setup as a schedule on the server?
> >
> > > Any assistance is appreciated
> >
> > > Thank you
> >
> > > David Mahler
> > > dmah...@.superwarehouse.com- Hide quoted text -
> >
> > - Show quoted text -
>
I have some Reporting Services users who want to setup subscriptions for
reports and enter the current month, trailing 7 days, this week, etc. in the
start and end date parameter fields. Can they do this with code (i.e.
currentdate(-7)) or does this have to be setup as a schedule on the server?
Any assistance is appreciated
Thank you
David Mahler
dmahler@.superwarehouse.comDear David,
I am not sure if I understand your question well, but if you need to
address last week, you may use sql like
select ...
from ...
where date between dateadd(day,-7,getdate()) and getdate().
getdate() will give you current date-time, and dateadd will shift back
7 days.
See dateadd() for more help.
Best Regards,
leecz
On 1=D4=C24=C8=D5, =C9=CF=CE=E71=CA=B153=B7=D6, David Mahler <David
Mah...@.discussions.microsoft.com> wrote:
> Hello,
> I have some Reporting Services users who want to setup subscriptions for
> reports and enter the current month, trailing 7 days, this week, etc. in t=he
> start and end date parameter fields. Can they do this with code (i.e.
> currentdate(-7)) or does this have to be setup as a schedule on the server=?
> Any assistance is appreciated
> Thank you
> David Mahler
> dmah...@.superwarehouse.com|||give an exampl as how you would like to see the subscription..
On Jan 4, 11:41 am, leecz <li.cheng...@.gmail.com> wrote:
> Dear David,
> I am not sure if I understand your question well, but if you need to
> address last week, you may use sql like
> select ...
> from ...
> where date between dateadd(day,-7,getdate()) and getdate().
> getdate() will give you current date-time, and dateadd will shift back
> 7 days.
> See dateadd() for more help.
> Best Regards,
> leecz
> On 1=D4=C24=C8=D5, =C9=CF=CE=E71=CA=B153=B7=D6, David Mahler <David
>
> Mah...@.discussions.microsoft.com> wrote:
> > Hello,
> > I have some Reporting Services users who want to setup subscriptions for=
> > reports and enter the current month, trailing 7 days, this week, etc. in= the
> > start and end date parameter fields. Can they do this with code (i.e.
> > currentdate(-7)) or does this have to be setup as a schedule on the serv=er?
> > Any assistance is appreciated
> > Thank you
> > David Mahler
> > dmah...@.superwarehouse.com- Hide quoted text -
> - Show quoted text -|||Thanks for your replies. I apologize for not being clear in my description.
I am referrring to the front end web interface where users can setup their
own subscriptions.
The user creates a new subscription for a report. The select the delivery
method, how often the report runs, and other parrameters. When they enter
the Start Date and End Date (might be with the calendar control), could they
enter a getdate(-7) or something like that to run the report for the current
month, trailing week, etc.
My thought is that this may have to be done on the back end. I would hope
that the user can set the report to run every week and get the current week's
data on an automated basis.
Thanks for your help
David
"Sridar K" wrote:
> give an exampl as how you would like to see the subscription..
> On Jan 4, 11:41 am, leecz <li.cheng...@.gmail.com> wrote:
> > Dear David,
> > I am not sure if I understand your question well, but if you need to
> > address last week, you may use sql like
> > select ...
> > from ...
> > where date between dateadd(day,-7,getdate()) and getdate().
> > getdate() will give you current date-time, and dateadd will shift back
> > 7 days.
> > See dateadd() for more help.
> >
> > Best Regards,
> > leecz
> >
> > On 1æ'4æ?¥, ä¸?å'1æ?¶53å', David Mahler <David
> >
> >
> >
> > Mah...@.discussions.microsoft.com> wrote:
> > > Hello,
> >
> > > I have some Reporting Services users who want to setup subscriptions for
> > > reports and enter the current month, trailing 7 days, this week, etc. in the
> > > start and end date parameter fields. Can they do this with code (i.e.
> > > currentdate(-7)) or does this have to be setup as a schedule on the server?
> >
> > > Any assistance is appreciated
> >
> > > Thank you
> >
> > > David Mahler
> > > dmah...@.superwarehouse.com- Hide quoted text -
> >
> > - Show quoted text -
>
Date Parameter Labels?
I have the usual report with date range parameters (from, to) that I have set
up as datetime format so that users can get the fancy calendar applet. thats
all fine and good. My problem is that I have creative users that like to hand
enter all kinds of date values other than the expected <mm/dd/yyyy> values
and are surprised that they dont return results.
I know I can create code in my sproc to handle all of the various
permutations on the backend, and that is probably the more "user friendly?
way to go, but what I want to know is if there is a way to put a label in the
parameter boxes that will show the expected format. I tried messing with
default parameters, but that didnt work when using a string as a default for
a datetime parameter. All I want is for the lable to show the expected
format, not act as a default value in any way.
If anyone has any ideas, please let me know. Perhaps this will be
functionality in the next version of RS?
Thanks!You can already put whatever label you want for the parameter. In layout
mode, Report Menu->Report Parameters
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:2A0DEA51-68E0-4329-A2AA-2728E456A21F@.microsoft.com...
>I have the usual report with date range parameters (from, to) that I have
>set
> up as datetime format so that users can get the fancy calendar applet.
> thats
> all fine and good. My problem is that I have creative users that like to
> hand
> enter all kinds of date values other than the expected <mm/dd/yyyy> values
> and are surprised that they dont return results.
> I know I can create code in my sproc to handle all of the various
> permutations on the backend, and that is probably the more "user friendly?
> way to go, but what I want to know is if there is a way to put a label in
> the
> parameter boxes that will show the expected format. I tried messing with
> default parameters, but that didnt work when using a string as a default
> for
> a datetime parameter. All I want is for the lable to show the expected
> format, not act as a default value in any way.
> If anyone has any ideas, please let me know. Perhaps this will be
> functionality in the next version of RS?
> Thanks!|||Different kind of label.
I know I can put the format in the NAME of the parameter, but this looks
kind of ugly and is only a last resort. I want to put the format INSIDE the
text box, much like a default value, but have it disappears when typed over
or the calendar applet is used. I dont want it seen by the reporting server
as an actual default value.
"Bruce L-C [MVP]" wrote:
> You can already put whatever label you want for the parameter. In layout
> mode, Report Menu->Report Parameters
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:2A0DEA51-68E0-4329-A2AA-2728E456A21F@.microsoft.com...
> >I have the usual report with date range parameters (from, to) that I have
> >set
> > up as datetime format so that users can get the fancy calendar applet.
> > thats
> > all fine and good. My problem is that I have creative users that like to
> > hand
> > enter all kinds of date values other than the expected <mm/dd/yyyy> values
> > and are surprised that they dont return results.
> > I know I can create code in my sproc to handle all of the various
> > permutations on the backend, and that is probably the more "user friendly?
> > way to go, but what I want to know is if there is a way to put a label in
> > the
> > parameter boxes that will show the expected format. I tried messing with
> > default parameters, but that didnt work when using a string as a default
> > for
> > a datetime parameter. All I want is for the lable to show the expected
> > format, not act as a default value in any way.
> >
> > If anyone has any ideas, please let me know. Perhaps this will be
> > functionality in the next version of RS?
> > Thanks!
>
>|||Ahhh, I see. Nope, can't do it. However, if they put in a non-date format
and you have the parameter value as date then RS will tell the user. Not the
best but not the worst error message. For instance: The value provided for
the report parameter 'FromDate' is not valid for its type
I have found most people use a date picker if it is there.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:0F768D55-C02B-4A3E-BBCA-2EA618B0490F@.microsoft.com...
> Different kind of label.
> I know I can put the format in the NAME of the parameter, but this looks
> kind of ugly and is only a last resort. I want to put the format INSIDE
> the
> text box, much like a default value, but have it disappears when typed
> over
> or the calendar applet is used. I dont want it seen by the reporting
> server
> as an actual default value.
> "Bruce L-C [MVP]" wrote:
>> You can already put whatever label you want for the parameter. In layout
>> mode, Report Menu->Report Parameters
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
>> news:2A0DEA51-68E0-4329-A2AA-2728E456A21F@.microsoft.com...
>> >I have the usual report with date range parameters (from, to) that I
>> >have
>> >set
>> > up as datetime format so that users can get the fancy calendar applet.
>> > thats
>> > all fine and good. My problem is that I have creative users that like
>> > to
>> > hand
>> > enter all kinds of date values other than the expected <mm/dd/yyyy>
>> > values
>> > and are surprised that they dont return results.
>> > I know I can create code in my sproc to handle all of the various
>> > permutations on the backend, and that is probably the more "user
>> > friendly?
>> > way to go, but what I want to know is if there is a way to put a label
>> > in
>> > the
>> > parameter boxes that will show the expected format. I tried messing
>> > with
>> > default parameters, but that didnt work when using a string as a
>> > default
>> > for
>> > a datetime parameter. All I want is for the lable to show the expected
>> > format, not act as a default value in any way.
>> >
>> > If anyone has any ideas, please let me know. Perhaps this will be
>> > functionality in the next version of RS?
>> > Thanks!
>>
up as datetime format so that users can get the fancy calendar applet. thats
all fine and good. My problem is that I have creative users that like to hand
enter all kinds of date values other than the expected <mm/dd/yyyy> values
and are surprised that they dont return results.
I know I can create code in my sproc to handle all of the various
permutations on the backend, and that is probably the more "user friendly?
way to go, but what I want to know is if there is a way to put a label in the
parameter boxes that will show the expected format. I tried messing with
default parameters, but that didnt work when using a string as a default for
a datetime parameter. All I want is for the lable to show the expected
format, not act as a default value in any way.
If anyone has any ideas, please let me know. Perhaps this will be
functionality in the next version of RS?
Thanks!You can already put whatever label you want for the parameter. In layout
mode, Report Menu->Report Parameters
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:2A0DEA51-68E0-4329-A2AA-2728E456A21F@.microsoft.com...
>I have the usual report with date range parameters (from, to) that I have
>set
> up as datetime format so that users can get the fancy calendar applet.
> thats
> all fine and good. My problem is that I have creative users that like to
> hand
> enter all kinds of date values other than the expected <mm/dd/yyyy> values
> and are surprised that they dont return results.
> I know I can create code in my sproc to handle all of the various
> permutations on the backend, and that is probably the more "user friendly?
> way to go, but what I want to know is if there is a way to put a label in
> the
> parameter boxes that will show the expected format. I tried messing with
> default parameters, but that didnt work when using a string as a default
> for
> a datetime parameter. All I want is for the lable to show the expected
> format, not act as a default value in any way.
> If anyone has any ideas, please let me know. Perhaps this will be
> functionality in the next version of RS?
> Thanks!|||Different kind of label.
I know I can put the format in the NAME of the parameter, but this looks
kind of ugly and is only a last resort. I want to put the format INSIDE the
text box, much like a default value, but have it disappears when typed over
or the calendar applet is used. I dont want it seen by the reporting server
as an actual default value.
"Bruce L-C [MVP]" wrote:
> You can already put whatever label you want for the parameter. In layout
> mode, Report Menu->Report Parameters
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:2A0DEA51-68E0-4329-A2AA-2728E456A21F@.microsoft.com...
> >I have the usual report with date range parameters (from, to) that I have
> >set
> > up as datetime format so that users can get the fancy calendar applet.
> > thats
> > all fine and good. My problem is that I have creative users that like to
> > hand
> > enter all kinds of date values other than the expected <mm/dd/yyyy> values
> > and are surprised that they dont return results.
> > I know I can create code in my sproc to handle all of the various
> > permutations on the backend, and that is probably the more "user friendly?
> > way to go, but what I want to know is if there is a way to put a label in
> > the
> > parameter boxes that will show the expected format. I tried messing with
> > default parameters, but that didnt work when using a string as a default
> > for
> > a datetime parameter. All I want is for the lable to show the expected
> > format, not act as a default value in any way.
> >
> > If anyone has any ideas, please let me know. Perhaps this will be
> > functionality in the next version of RS?
> > Thanks!
>
>|||Ahhh, I see. Nope, can't do it. However, if they put in a non-date format
and you have the parameter value as date then RS will tell the user. Not the
best but not the worst error message. For instance: The value provided for
the report parameter 'FromDate' is not valid for its type
I have found most people use a date picker if it is there.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:0F768D55-C02B-4A3E-BBCA-2EA618B0490F@.microsoft.com...
> Different kind of label.
> I know I can put the format in the NAME of the parameter, but this looks
> kind of ugly and is only a last resort. I want to put the format INSIDE
> the
> text box, much like a default value, but have it disappears when typed
> over
> or the calendar applet is used. I dont want it seen by the reporting
> server
> as an actual default value.
> "Bruce L-C [MVP]" wrote:
>> You can already put whatever label you want for the parameter. In layout
>> mode, Report Menu->Report Parameters
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
>> news:2A0DEA51-68E0-4329-A2AA-2728E456A21F@.microsoft.com...
>> >I have the usual report with date range parameters (from, to) that I
>> >have
>> >set
>> > up as datetime format so that users can get the fancy calendar applet.
>> > thats
>> > all fine and good. My problem is that I have creative users that like
>> > to
>> > hand
>> > enter all kinds of date values other than the expected <mm/dd/yyyy>
>> > values
>> > and are surprised that they dont return results.
>> > I know I can create code in my sproc to handle all of the various
>> > permutations on the backend, and that is probably the more "user
>> > friendly?
>> > way to go, but what I want to know is if there is a way to put a label
>> > in
>> > the
>> > parameter boxes that will show the expected format. I tried messing
>> > with
>> > default parameters, but that didnt work when using a string as a
>> > default
>> > for
>> > a datetime parameter. All I want is for the lable to show the expected
>> > format, not act as a default value in any way.
>> >
>> > If anyone has any ideas, please let me know. Perhaps this will be
>> > functionality in the next version of RS?
>> > Thanks!
>>
Thursday, March 8, 2012
Date Format Problem - SQL Server Insert From Web Application
Hi,
I've written a short aspx file so that end users can insert lines into our
SQL server database. The following string is sent by Internet Explorer to
the database where it updates the relevant table -
INSERT INTO Tbl_Manual([Data Date],[Staff ID], Flag1, Flag2, Flag3, Ref1,
Ref2, Timestamp, Inputter) values('15/05/2005', '89', '1', '0', '0', '77',
'8', '01/05/2005 08:54:10', 'HOME\username')
The 2nd date is sent as a string into an nvarchar field so it causes no
problems but the first (which is heading for a datetime field) is assumed by
SQL to be MM/dd/yyyy format no matter what I try to do. All regional
settings are set to UK English & the table in SQL correctly uses dd/MM/yyyy
format so the above insert command fails thinking that I'm trying to add a
date of the 5th of month 15.
If I try input a date as mm/dd/yyyy format into the aspx page, an error is
thrown back.
Any ideas as to what's going wrong ?
Thanks
SteveYou can set date in an universal format 'yyyymmdd',
or use convert(datetime,'dd/mm/yyyy',103) function, see MS SQL Help or
http://www.karaszi.com/SQLServer/info_datetime.asp
Vlastik
"Steve" <steve.henderson@.btinternet.c0m> pe v diskusnm pspvku
news:uBIy7ljTFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've written a short aspx file so that end users can insert lines into our
> SQL server database. The following string is sent by Internet Explorer to
> the database where it updates the relevant table -
> INSERT INTO Tbl_Manual([Data Date],[Staff ID], Flag1, Flag2, Flag3, Ref1,
> Ref2, Timestamp, Inputter) values('15/05/2005', '89', '1', '0', '0', '77',
> '8', '01/05/2005 08:54:10', 'HOME\username')
> The 2nd date is sent as a string into an nvarchar field so it causes no
> problems but the first (which is heading for a datetime field) is assumed
by
> SQL to be MM/dd/yyyy format no matter what I try to do. All regional
> settings are set to UK English & the table in SQL correctly uses
dd/MM/yyyy
> format so the above insert command fails thinking that I'm trying to add a
> date of the 5th of month 15.
> If I try input a date as mm/dd/yyyy format into the aspx page, an error is
> thrown back.
> Any ideas as to what's going wrong ?
> Thanks
> Steve
>|||Steve
Always use 'YYYYMMDD' to insert data into SQL Server table. To display dates
use FORMAT or other functions to format to be suitable to the client.
"Steve" <steve.henderson@.btinternet.c0m> wrote in message
news:uBIy7ljTFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've written a short aspx file so that end users can insert lines into our
> SQL server database. The following string is sent by Internet Explorer to
> the database where it updates the relevant table -
> INSERT INTO Tbl_Manual([Data Date],[Staff ID], Flag1, Flag2, Flag3, Ref1,
> Ref2, Timestamp, Inputter) values('15/05/2005', '89', '1', '0', '0', '77',
> '8', '01/05/2005 08:54:10', 'HOME\username')
> The 2nd date is sent as a string into an nvarchar field so it causes no
> problems but the first (which is heading for a datetime field) is assumed
by
> SQL to be MM/dd/yyyy format no matter what I try to do. All regional
> settings are set to UK English & the table in SQL correctly uses
dd/MM/yyyy
> format so the above insert command fails thinking that I'm trying to add a
> date of the 5th of month 15.
> If I try input a date as mm/dd/yyyy format into the aspx page, an error is
> thrown back.
> Any ideas as to what's going wrong ?
> Thanks
> Steve
>|||Date formats are set on the connection level in SQL Server, not database or
server wide. If they are not explicitly set, they are derived from the
default settings for the login that uses the connection. It appears to me
that the login you use to connect to the database has it's language (which
also includes the date format)set to British, but the login that you web app
uses to connect to the database, has it language set to English, i.e. U.S.
English.
Jacco Schalkwijk
SQL Server MVP
"Steve" <steve.henderson@.btinternet.c0m> wrote in message
news:uBIy7ljTFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've written a short aspx file so that end users can insert lines into our
> SQL server database. The following string is sent by Internet Explorer to
> the database where it updates the relevant table -
> INSERT INTO Tbl_Manual([Data Date],[Staff ID], Flag1, Flag2, Flag3, Ref1,
> Ref2, Timestamp, Inputter) values('15/05/2005', '89', '1', '0', '0', '77',
> '8', '01/05/2005 08:54:10', 'HOME\username')
> The 2nd date is sent as a string into an nvarchar field so it causes no
> problems but the first (which is heading for a datetime field) is assumed
> by SQL to be MM/dd/yyyy format no matter what I try to do. All regional
> settings are set to UK English & the table in SQL correctly uses
> dd/MM/yyyy format so the above insert command fails thinking that I'm
> trying to add a date of the 5th of month 15.
> If I try input a date as mm/dd/yyyy format into the aspx page, an error is
> thrown back.
> Any ideas as to what's going wrong ?
> Thanks
> Steve
>|||I suggest that you read up about SQL Code Injection once you have this
resolved. The implication from your question is that data is more or less
coming off a web form straight into the database and as such may be highly
vulnerable to hacking.
"Steve" <steve.henderson@.btinternet.c0m> wrote in message
news:uBIy7ljTFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've written a short aspx file so that end users can insert lines into our
> SQL server database. The following string is sent by Internet Explorer to
> the database where it updates the relevant table -
> INSERT INTO Tbl_Manual([Data Date],[Staff ID], Flag1, Flag2, Flag3, Ref1,
> Ref2, Timestamp, Inputter) values('15/05/2005', '89', '1', '0', '0', '77',
> '8', '01/05/2005 08:54:10', 'HOME\username')
> The 2nd date is sent as a string into an nvarchar field so it causes no
> problems but the first (which is heading for a datetime field) is assumed
> by SQL to be MM/dd/yyyy format no matter what I try to do. All regional
> settings are set to UK English & the table in SQL correctly uses
> dd/MM/yyyy format so the above insert command fails thinking that I'm
> trying to add a date of the 5th of month 15.
> If I try input a date as mm/dd/yyyy format into the aspx page, an error is
> thrown back.
> Any ideas as to what's going wrong ?
> Thanks
> Steve
>|||Thanks Jacco - You got it in one. You have no idea how long I've been trying
to fix this!!!
I'd set a local account up on the server for testing things - trust
Microsoft to default things to 'english' which being from England myself I
would have assumed to be correct rather than having to choose 'British
English' !
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:u39qoyjTFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Date formats are set on the connection level in SQL Server, not database
> or server wide. If they are not explicitly set, they are derived from the
> default settings for the login that uses the connection. It appears to me
> that the login you use to connect to the database has it's language (which
> also includes the date format)set to British, but the login that you web
> app uses to connect to the database, has it language set to English, i.e.
> U.S. English.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Steve" <steve.henderson@.btinternet.c0m> wrote in message
> news:uBIy7ljTFHA.2520@.TK2MSFTNGP09.phx.gbl...
>|||>
> I'd set a local account up on the server for testing things - trust
> Microsoft to default things to 'english' which being from England myself I
> would have assumed to be correct rather than having to choose 'British
> English' !
>
LOL|||The original poster might consider a parameterized query. This will address
both the SQL injection security issue as well as date string formatting.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mercury" <me@.spam.com> wrote in message
news:d52dsi$ac7$1@.lust.ihug.co.nz...
>I suggest that you read up about SQL Code Injection once you have this
>resolved. The implication from your question is that data is more or less
>coming off a web form straight into the database and as such may be highly
>vulnerable to hacking.
>
> "Steve" <steve.henderson@.btinternet.c0m> wrote in message
> news:uBIy7ljTFHA.2520@.TK2MSFTNGP09.phx.gbl...
>|||> The original poster might consider a parameterized query. This will
> address both the SQL injection security issue as well as date string
> formatting.
I may go on to look at that but I'm currently not too bothered about hacking
attempts as the form is for intranet use only - I just need something quick
& dirty !
Thanks
Steve
I've written a short aspx file so that end users can insert lines into our
SQL server database. The following string is sent by Internet Explorer to
the database where it updates the relevant table -
INSERT INTO Tbl_Manual([Data Date],[Staff ID], Flag1, Flag2, Flag3, Ref1,
Ref2, Timestamp, Inputter) values('15/05/2005', '89', '1', '0', '0', '77',
'8', '01/05/2005 08:54:10', 'HOME\username')
The 2nd date is sent as a string into an nvarchar field so it causes no
problems but the first (which is heading for a datetime field) is assumed by
SQL to be MM/dd/yyyy format no matter what I try to do. All regional
settings are set to UK English & the table in SQL correctly uses dd/MM/yyyy
format so the above insert command fails thinking that I'm trying to add a
date of the 5th of month 15.
If I try input a date as mm/dd/yyyy format into the aspx page, an error is
thrown back.
Any ideas as to what's going wrong ?
Thanks
SteveYou can set date in an universal format 'yyyymmdd',
or use convert(datetime,'dd/mm/yyyy',103) function, see MS SQL Help or
http://www.karaszi.com/SQLServer/info_datetime.asp
Vlastik
"Steve" <steve.henderson@.btinternet.c0m> pe v diskusnm pspvku
news:uBIy7ljTFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've written a short aspx file so that end users can insert lines into our
> SQL server database. The following string is sent by Internet Explorer to
> the database where it updates the relevant table -
> INSERT INTO Tbl_Manual([Data Date],[Staff ID], Flag1, Flag2, Flag3, Ref1,
> Ref2, Timestamp, Inputter) values('15/05/2005', '89', '1', '0', '0', '77',
> '8', '01/05/2005 08:54:10', 'HOME\username')
> The 2nd date is sent as a string into an nvarchar field so it causes no
> problems but the first (which is heading for a datetime field) is assumed
by
> SQL to be MM/dd/yyyy format no matter what I try to do. All regional
> settings are set to UK English & the table in SQL correctly uses
dd/MM/yyyy
> format so the above insert command fails thinking that I'm trying to add a
> date of the 5th of month 15.
> If I try input a date as mm/dd/yyyy format into the aspx page, an error is
> thrown back.
> Any ideas as to what's going wrong ?
> Thanks
> Steve
>|||Steve
Always use 'YYYYMMDD' to insert data into SQL Server table. To display dates
use FORMAT or other functions to format to be suitable to the client.
"Steve" <steve.henderson@.btinternet.c0m> wrote in message
news:uBIy7ljTFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've written a short aspx file so that end users can insert lines into our
> SQL server database. The following string is sent by Internet Explorer to
> the database where it updates the relevant table -
> INSERT INTO Tbl_Manual([Data Date],[Staff ID], Flag1, Flag2, Flag3, Ref1,
> Ref2, Timestamp, Inputter) values('15/05/2005', '89', '1', '0', '0', '77',
> '8', '01/05/2005 08:54:10', 'HOME\username')
> The 2nd date is sent as a string into an nvarchar field so it causes no
> problems but the first (which is heading for a datetime field) is assumed
by
> SQL to be MM/dd/yyyy format no matter what I try to do. All regional
> settings are set to UK English & the table in SQL correctly uses
dd/MM/yyyy
> format so the above insert command fails thinking that I'm trying to add a
> date of the 5th of month 15.
> If I try input a date as mm/dd/yyyy format into the aspx page, an error is
> thrown back.
> Any ideas as to what's going wrong ?
> Thanks
> Steve
>|||Date formats are set on the connection level in SQL Server, not database or
server wide. If they are not explicitly set, they are derived from the
default settings for the login that uses the connection. It appears to me
that the login you use to connect to the database has it's language (which
also includes the date format)set to British, but the login that you web app
uses to connect to the database, has it language set to English, i.e. U.S.
English.
Jacco Schalkwijk
SQL Server MVP
"Steve" <steve.henderson@.btinternet.c0m> wrote in message
news:uBIy7ljTFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've written a short aspx file so that end users can insert lines into our
> SQL server database. The following string is sent by Internet Explorer to
> the database where it updates the relevant table -
> INSERT INTO Tbl_Manual([Data Date],[Staff ID], Flag1, Flag2, Flag3, Ref1,
> Ref2, Timestamp, Inputter) values('15/05/2005', '89', '1', '0', '0', '77',
> '8', '01/05/2005 08:54:10', 'HOME\username')
> The 2nd date is sent as a string into an nvarchar field so it causes no
> problems but the first (which is heading for a datetime field) is assumed
> by SQL to be MM/dd/yyyy format no matter what I try to do. All regional
> settings are set to UK English & the table in SQL correctly uses
> dd/MM/yyyy format so the above insert command fails thinking that I'm
> trying to add a date of the 5th of month 15.
> If I try input a date as mm/dd/yyyy format into the aspx page, an error is
> thrown back.
> Any ideas as to what's going wrong ?
> Thanks
> Steve
>|||I suggest that you read up about SQL Code Injection once you have this
resolved. The implication from your question is that data is more or less
coming off a web form straight into the database and as such may be highly
vulnerable to hacking.
"Steve" <steve.henderson@.btinternet.c0m> wrote in message
news:uBIy7ljTFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've written a short aspx file so that end users can insert lines into our
> SQL server database. The following string is sent by Internet Explorer to
> the database where it updates the relevant table -
> INSERT INTO Tbl_Manual([Data Date],[Staff ID], Flag1, Flag2, Flag3, Ref1,
> Ref2, Timestamp, Inputter) values('15/05/2005', '89', '1', '0', '0', '77',
> '8', '01/05/2005 08:54:10', 'HOME\username')
> The 2nd date is sent as a string into an nvarchar field so it causes no
> problems but the first (which is heading for a datetime field) is assumed
> by SQL to be MM/dd/yyyy format no matter what I try to do. All regional
> settings are set to UK English & the table in SQL correctly uses
> dd/MM/yyyy format so the above insert command fails thinking that I'm
> trying to add a date of the 5th of month 15.
> If I try input a date as mm/dd/yyyy format into the aspx page, an error is
> thrown back.
> Any ideas as to what's going wrong ?
> Thanks
> Steve
>|||Thanks Jacco - You got it in one. You have no idea how long I've been trying
to fix this!!!
I'd set a local account up on the server for testing things - trust
Microsoft to default things to 'english' which being from England myself I
would have assumed to be correct rather than having to choose 'British
English' !
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:u39qoyjTFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Date formats are set on the connection level in SQL Server, not database
> or server wide. If they are not explicitly set, they are derived from the
> default settings for the login that uses the connection. It appears to me
> that the login you use to connect to the database has it's language (which
> also includes the date format)set to British, but the login that you web
> app uses to connect to the database, has it language set to English, i.e.
> U.S. English.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Steve" <steve.henderson@.btinternet.c0m> wrote in message
> news:uBIy7ljTFHA.2520@.TK2MSFTNGP09.phx.gbl...
>|||>
> I'd set a local account up on the server for testing things - trust
> Microsoft to default things to 'english' which being from England myself I
> would have assumed to be correct rather than having to choose 'British
> English' !
>
LOL|||The original poster might consider a parameterized query. This will address
both the SQL injection security issue as well as date string formatting.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mercury" <me@.spam.com> wrote in message
news:d52dsi$ac7$1@.lust.ihug.co.nz...
>I suggest that you read up about SQL Code Injection once you have this
>resolved. The implication from your question is that data is more or less
>coming off a web form straight into the database and as such may be highly
>vulnerable to hacking.
>
> "Steve" <steve.henderson@.btinternet.c0m> wrote in message
> news:uBIy7ljTFHA.2520@.TK2MSFTNGP09.phx.gbl...
>|||> The original poster might consider a parameterized query. This will
> address both the SQL injection security issue as well as date string
> formatting.
I may go on to look at that but I'm currently not too bothered about hacking
attempts as the form is for intranet use only - I just need something quick
& dirty !
Thanks
Steve
Saturday, February 25, 2012
Date fields causing screen refresh.
We have many reports where we have date parameter for a begin and end date.
When users either type a date or select it from the calender, it is causing
the screen to refresh when they are done with that field. This is very
anoyning to the users and is turning them off to RSS. Is there a setting
that is causing this?On May 11, 7:09 pm, Tom <T...@.discussions.microsoft.com> wrote:
> We have many reports where we have date parameter for a begin and end date.
> When users either type a date or select it from the calender, it is causing
> the screen to refresh when they are done with that field. This is very
> anoyning to the users and is turning them off to RSS. Is there a setting
> that is causing this?
Most likely, this is caused by one of two things:
- Either, the report parameters have default values set instead of
null (which would require the View Report button to be selected, if
null was the default). You can check this via: Layout view -> Report
tab -> Report Parameters...
- or, the Autorefresh is set (you can check this via: Layout view ->
Report tab -> Report Properties... -> General tab -> Autorefresh).
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
When users either type a date or select it from the calender, it is causing
the screen to refresh when they are done with that field. This is very
anoyning to the users and is turning them off to RSS. Is there a setting
that is causing this?On May 11, 7:09 pm, Tom <T...@.discussions.microsoft.com> wrote:
> We have many reports where we have date parameter for a begin and end date.
> When users either type a date or select it from the calender, it is causing
> the screen to refresh when they are done with that field. This is very
> anoyning to the users and is turning them off to RSS. Is there a setting
> that is causing this?
Most likely, this is caused by one of two things:
- Either, the report parameters have default values set instead of
null (which would require the View Report button to be selected, if
null was the default). You can check this via: Layout view -> Report
tab -> Report Parameters...
- or, the Autorefresh is set (you can check this via: Layout view ->
Report tab -> Report Properties... -> General tab -> Autorefresh).
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
Sunday, February 19, 2012
Date Conversion inconsistent with DB rules
I'm trying to validate data entered from users as legitimate dates by running them thru a conversion component. When conversion fails, I asssign NULLs, other wise land the date to a datetime field on the database. Dates that pass thru the conversion, however fail on insert into the DB.
It appears that the rules in the conversion component are different than that of the DB?
for instance if a user forgot the last digit on a year, i.e. "10/22/197" the convert creates a field for any of the various date datatypes as 10/22/0197, which fails on imsert into the DB, becasue it won't allow years prior to 1753.
Dates are very piccy, it is best to validate and make sure the date is correct in your package, in addition some rdms support dates before 1753 which is why SSIS supports them, SQL doesn't.
Tuesday, February 14, 2012
Date and Time Best Practice - storage in SQL Server database
With regards to time zones, daylight savings, and web users, is there a best practice for storing date & time information in a database?
For example, my databases are hosted in Time Zone A, but the web users are in Time Zone B. Then, when I create a rss feed (which is displayed in GMT), I add a third time zone into the mix for the same data. To date (no pun intended), I have been entering the date/time data in the time zone of the database server (Time Zone A), and then converting it using an application setting in the web.config file (i.e. TimeZoneBOffset = -1, GMTOffSet = -5). In other words, each time I display a date I calculate what it should be using the time-zone offset in the web.config. This also enables me to account for changes in day light savings, etc.
My concerns are three fold: 1. What if I move the database to another server and the time zone changes? 2. Right now the users are in only 1 time zone. If I expand it to several then the offset will have to be by users, which is do-able, but something I haven't had experience with in the past. 3. It is likely more efficient to calculate the time zone once on input into the DB, rather than in each use like I'm doing now. What time zone baseline for insert into the db should I use?
Thanks in advance for your help!
PS My application is primarily looking at 'smalldatetime' data - down to the 'minute' level.
Hey, this is an old song.
My point is that you have to store date in a universal format - UTC, this will prevent any troubles while moving database on another zone.
Next, your users have to setup theirs profile and specify in what timezone they are in. According to this you're making shifts.
E.g. look at this site, each user has Site Option section where he/she can setup time zone.
I guess it is that simple.
Do you know of any webservice or other source that has time zone data?
Thanks.
|||
Here is a good article on the subject:
http://aspnet.4guysfromrolla.com/articles/081507-1.aspx
Subscribe to:
Posts (Atom)