date queries

I have written a report that contains multiple queries that displays the
number of applications we submit month by month to various entities. I wrote
the report last year and have been asked to create it again this year.
I know that this is going to be require every year, and I don't want to
write it again.
My problem is that in order to get the month by month data, I had to write
"WHERE (tDocHistory.ChangeDate BETWEEN '1/1/2008' AND '2/1/2008')
GROUP BY tDocHistory.NewStatus, tDocHistory.ChangeDate, tCompany.CoName,
tApplication.ApplicationNum, CONVERT(CHAR(2), DATEPART(MM,
tDocHistory.ChangeDate)), CONVERT(CHAR(4),
DATEPART(yyyy, tDocHistory.ChangeDate)), tApplication1028.MarkUpAmt"
I would like to be able to allow my users to select the year they wish to
view.
I wanted to replace the 2008 with % in the statement:
(tDocHistory.ChangeDate BETWEEN '1/1/%' AND '2/1/%')
and then filter the datepart for the year by writing:
HAVING (tDocHistory.NewStatus = 'wv' OR
tDocHistory.NewStatus = 'dcs') AND (tCompany.CoName
LIKE @.FunderName) AND (CONVERT(CHAR(4), DATEPART(yyyy,
tDocHistory.ChangeDate))
LIKE @.Year)
Do you know any way I could make this work?
--
SamyraWhy dont you use report parameter to select the date.
On Jan 12, 8:28=A0pm, Samyra <Sam...@.discussions.microsoft.com> wrote:
> I have written a report that contains multiple queries that displays the
> number of applications we submit month by month to various entities. =A0I =wrote
> the report last year and have been asked to create it again this year.
> I know that this is going to be require every year, and I don't want to
> write it again.
> My problem is that in order to get the month by month data, I had to =A0wr=ite
> "WHERE =A0 =A0 (tDocHistory.ChangeDate BETWEEN '1/1/2008' AND '2/1/2008')
> GROUP BY tDocHistory.NewStatus, tDocHistory.ChangeDate, tCompany.CoName,
> tApplication.ApplicationNum, CONVERT(CHAR(2), DATEPART(MM,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 tDocHistory.ChangeDate)), CONV=ERT(CHAR(4),
> DATEPART(yyyy, tDocHistory.ChangeDate)), tApplication1028.MarkUpAmt"
> I would like to be able to allow my users to select the year they wish to
> view.
> I wanted to replace the 2008 with % in the statement:
> =A0 (tDocHistory.ChangeDate BETWEEN '1/1/%' AND '2/1/%')
> and then filter the datepart for the year by writing:
> HAVING =A0 =A0 =A0(tDocHistory.NewStatus =3D 'wv' OR
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 tDocHistory.NewStatus =3D 'dcs=') AND (tCompany.CoName
> LIKE @.FunderName) AND (CONVERT(CHAR(4), DATEPART(yyyy,
> tDocHistory.ChangeDate))
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 LIKE @.Year)
> Do you know any way I could make this work?
> --
> Samyra|||I would love to, but the report is built with text boxes with a separate
query in each box.
I am looking at a table that contains historical data for each record in the
database, not current data, so I wasn't able to build a table or a matrix
report that would display the data they wanted to see.
--
Samyra
"Sridar K" wrote:
> Why dont you use report parameter to select the date.
> On Jan 12, 8:28 pm, Samyra <Sam...@.discussions.microsoft.com> wrote:
> > I have written a report that contains multiple queries that displays the
> > number of applications we submit month by month to various entities. I wrote
> > the report last year and have been asked to create it again this year.
> >
> > I know that this is going to be require every year, and I don't want to
> > write it again.
> >
> > My problem is that in order to get the month by month data, I had to write
> >
> > "WHERE (tDocHistory.ChangeDate BETWEEN '1/1/2008' AND '2/1/2008')
> > GROUP BY tDocHistory.NewStatus, tDocHistory.ChangeDate, tCompany.CoName,
> > tApplication.ApplicationNum, CONVERT(CHAR(2), DATEPART(MM,
> > tDocHistory.ChangeDate)), CONVERT(CHAR(4),
> > DATEPART(yyyy, tDocHistory.ChangeDate)), tApplication1028.MarkUpAmt"
> >
> > I would like to be able to allow my users to select the year they wish to
> > view.
> >
> > I wanted to replace the 2008 with % in the statement:
> > (tDocHistory.ChangeDate BETWEEN '1/1/%' AND '2/1/%')
> >
> > and then filter the datepart for the year by writing:
> >
> > HAVING (tDocHistory.NewStatus = 'wv' OR
> > tDocHistory.NewStatus = 'dcs') AND (tCompany.CoName
> > LIKE @.FunderName) AND (CONVERT(CHAR(4), DATEPART(yyyy,
> > tDocHistory.ChangeDate))
> > LIKE @.Year)
> >
> > Do you know any way I could make this work?
> >
> > --
> > Samyra
>

Date Parameter

My report contains a date-time parameter. When the report is run it displays
both the date and time portions in my date drop down box e.g. 01/01/2004
12:00:00 AM.
How would I change this to only display the 01/01/2004 ?
Note: I want to maintain system localized date format.Try =FormatDateTime(Fields!myDate.Value, vbShortDate).
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:F313E096-482A-4541-AC49-D929964A14D4@.microsoft.com...
> My report contains a date-time parameter. When the report is run it
displays
> both the date and time portions in my date drop down box e.g. 01/01/2004
> 12:00:00 AM.
> How would I change this to only display the 01/01/2004 ?
> Note: I want to maintain system localized date format.|||Hi Ravi:
Where should I place it?
"Ravi Mumulla (Microsoft)" wrote:
> Try =FormatDateTime(Fields!myDate.Value, vbShortDate).
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:F313E096-482A-4541-AC49-D929964A14D4@.microsoft.com...
> > My report contains a date-time parameter. When the report is run it
> displays
> > both the date and time portions in my date drop down box e.g. 01/01/2004
> > 12:00:00 AM.
> >
> > How would I change this to only display the 01/01/2004 ?
> >
> > Note: I want to maintain system localized date format.
>
>|||Click on the control you want to format (the particular field in the table
control, or a texbox for example) and go to properties, format, select
expression and put this in when the expression box comes up.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:C04EC5F8-0C74-41E7-B0BD-8886FD0395B6@.microsoft.com...
> Hi Ravi:
> Where should I place it?
> "Ravi Mumulla (Microsoft)" wrote:
> > Try =FormatDateTime(Fields!myDate.Value, vbShortDate).
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > news:F313E096-482A-4541-AC49-D929964A14D4@.microsoft.com...
> > > My report contains a date-time parameter. When the report is run it
> > displays
> > > both the date and time portions in my date drop down box e.g.
01/01/2004
> > > 12:00:00 AM.
> > >
> > > How would I change this to only display the 01/01/2004 ?
> > >
> > > Note: I want to maintain system localized date format.
> >
> >
> >|||I am attempting to chnage the date format in the parameter control bar... as
far as I can see there is no properties selection to choose from.
"Bruce L-C [MVP]" wrote:
> Click on the control you want to format (the particular field in the table
> control, or a texbox for example) and go to properties, format, select
> expression and put this in when the expression box comes up.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:C04EC5F8-0C74-41E7-B0BD-8886FD0395B6@.microsoft.com...
> > Hi Ravi:
> >
> > Where should I place it?
> >
> > "Ravi Mumulla (Microsoft)" wrote:
> >
> > > Try =FormatDateTime(Fields!myDate.Value, vbShortDate).
> > >
> > > --
> > > Ravi Mumulla (Microsoft)
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > news:F313E096-482A-4541-AC49-D929964A14D4@.microsoft.com...
> > > > My report contains a date-time parameter. When the report is run it
> > > displays
> > > > both the date and time portions in my date drop down box e.g.
> 01/01/2004
> > > > 12:00:00 AM.
> > > >
> > > > How would I change this to only display the 01/01/2004 ?
> > > >
> > > > Note: I want to maintain system localized date format.
> > >
> > >
> > >
>
>|||Ahh, sorry. Everybody answering you was answering with regards to the
report. The parameter tool bar can not be modified as far as I know. You
have the option of creating your own web page and then integrate into RS
using URL control or web services.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:90B96A06-51F8-4E7C-BBD3-BBD671BA5887@.microsoft.com...
> I am attempting to chnage the date format in the parameter control bar...
as
> far as I can see there is no properties selection to choose from.
> "Bruce L-C [MVP]" wrote:
> > Click on the control you want to format (the particular field in the
table
> > control, or a texbox for example) and go to properties, format, select
> > expression and put this in when the expression box comes up.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > news:C04EC5F8-0C74-41E7-B0BD-8886FD0395B6@.microsoft.com...
> > > Hi Ravi:
> > >
> > > Where should I place it?
> > >
> > > "Ravi Mumulla (Microsoft)" wrote:
> > >
> > > > Try =FormatDateTime(Fields!myDate.Value, vbShortDate).
> > > >
> > > > --
> > > > Ravi Mumulla (Microsoft)
> > > > SQL Server Reporting Services
> > > >
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > > news:F313E096-482A-4541-AC49-D929964A14D4@.microsoft.com...
> > > > > My report contains a date-time parameter. When the report is run
it
> > > > displays
> > > > > both the date and time portions in my date drop down box e.g.
> > 01/01/2004
> > > > > 12:00:00 AM.
> > > > >
> > > > > How would I change this to only display the 01/01/2004 ?
> > > > >
> > > > > Note: I want to maintain system localized date format.
> > > >
> > > >
> > > >
> >
> >
> >|||Crystal Reports has the option to convert all date-time to dates.
This allows you to bring up all records which occured between a certain date
range irrespective of the time.
Having the time portion in RS causes problems. If I want to pull up all
records which occured from 1 jan 2004 to 1 jan 2004. RS views this as being 1
jan 2004 12 am to 1 jan 2004 12 am. It therefore leaves out the other 23:59
hours on Jan 1.
Are you saying this is not possible in RS without a custom webpage?
"Bruce L-C [MVP]" wrote:
> Ahh, sorry. Everybody answering you was answering with regards to the
> report. The parameter tool bar can not be modified as far as I know. You
> have the option of creating your own web page and then integrate into RS
> using URL control or web services.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:90B96A06-51F8-4E7C-BBD3-BBD671BA5887@.microsoft.com...
> > I am attempting to chnage the date format in the parameter control bar...
> as
> > far as I can see there is no properties selection to choose from.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Click on the control you want to format (the particular field in the
> table
> > > control, or a texbox for example) and go to properties, format, select
> > > expression and put this in when the expression box comes up.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > news:C04EC5F8-0C74-41E7-B0BD-8886FD0395B6@.microsoft.com...
> > > > Hi Ravi:
> > > >
> > > > Where should I place it?
> > > >
> > > > "Ravi Mumulla (Microsoft)" wrote:
> > > >
> > > > > Try =FormatDateTime(Fields!myDate.Value, vbShortDate).
> > > > >
> > > > > --
> > > > > Ravi Mumulla (Microsoft)
> > > > > SQL Server Reporting Services
> > > > >
> > > > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > > > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > > > news:F313E096-482A-4541-AC49-D929964A14D4@.microsoft.com...
> > > > > > My report contains a date-time parameter. When the report is run
> it
> > > > > displays
> > > > > > both the date and time portions in my date drop down box e.g.
> > > 01/01/2004
> > > > > > 12:00:00 AM.
> > > > > >
> > > > > > How would I change this to only display the 01/01/2004 ?
> > > > > >
> > > > > > Note: I want to maintain system localized date format.
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||If you are going against SQL Server database it only has a datetime data
type. It does not have a separate date and time datatypes. I do all my
reports >= fromdate and < todate. So if you want a day's worth of data it is
between 1/1/04 00:00:00 and 1/2/04 00:00:00
One point, you can always ignore the time portion. The report parameter and
the query parameter are two different thing and you can map the query
parameter to an expression which takes the report parameter and strips the
time part of it. The downside of this is that the user will see the time in
the parameter bar.
One other point, you can have a text parameter instead of the date
parameter. The user puts in the date as text and then you do whatever you
want with it during the assignment to the query parameter.
The portal that RS provides in version 1 is limited with how you can
customize it. Hopefully we will see some improvements. For instance it sure
would be nice to format the parameters (as you mentioned) or add custom
error checking to it. Or a datepicker would be nice. No special knowledge of
what the improvements would be. Just that I do agree with you that more
control and options with it would be nice.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:53A33C93-0483-4305-BBF2-988B618FFF2D@.microsoft.com...
> Crystal Reports has the option to convert all date-time to dates.
> This allows you to bring up all records which occured between a certain
date
> range irrespective of the time.
> Having the time portion in RS causes problems. If I want to pull up all
> records which occured from 1 jan 2004 to 1 jan 2004. RS views this as
being 1
> jan 2004 12 am to 1 jan 2004 12 am. It therefore leaves out the other
23:59
> hours on Jan 1.
> Are you saying this is not possible in RS without a custom webpage?
> "Bruce L-C [MVP]" wrote:
> > Ahh, sorry. Everybody answering you was answering with regards to the
> > report. The parameter tool bar can not be modified as far as I know. You
> > have the option of creating your own web page and then integrate into RS
> > using URL control or web services.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > news:90B96A06-51F8-4E7C-BBD3-BBD671BA5887@.microsoft.com...
> > > I am attempting to chnage the date format in the parameter control
bar...
> > as
> > > far as I can see there is no properties selection to choose from.
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > Click on the control you want to format (the particular field in the
> > table
> > > > control, or a texbox for example) and go to properties, format,
select
> > > > expression and put this in when the expression box comes up.
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > > news:C04EC5F8-0C74-41E7-B0BD-8886FD0395B6@.microsoft.com...
> > > > > Hi Ravi:
> > > > >
> > > > > Where should I place it?
> > > > >
> > > > > "Ravi Mumulla (Microsoft)" wrote:
> > > > >
> > > > > > Try =FormatDateTime(Fields!myDate.Value, vbShortDate).
> > > > > >
> > > > > > --
> > > > > > Ravi Mumulla (Microsoft)
> > > > > > SQL Server Reporting Services
> > > > > >
> > > > > > This posting is provided "AS IS" with no warranties, and confers
no
> > > > rights.
> > > > > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > > > > news:F313E096-482A-4541-AC49-D929964A14D4@.microsoft.com...
> > > > > > > My report contains a date-time parameter. When the report is
run
> > it
> > > > > > displays
> > > > > > > both the date and time portions in my date drop down box e.g.
> > > > 01/01/2004
> > > > > > > 12:00:00 AM.
> > > > > > >
> > > > > > > How would I change this to only display the 01/01/2004 ?
> > > > > > >
> > > > > > > Note: I want to maintain system localized date format.
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||I know this is an old thread but in my searches for an answer.....
Anyway I figured out a solution:
=Today.ToShortDateString()
or if you need to add days:
=Today.AddDays(-2).ToShortDateString()
Hope this helps
"Bruce L-C [MVP]" wrote:
> If you are going against SQL Server database it only has a datetime data
> type. It does not have a separate date and time datatypes. I do all my
> reports >= fromdate and < todate. So if you want a day's worth of data it is
> between 1/1/04 00:00:00 and 1/2/04 00:00:00
> One point, you can always ignore the time portion. The report parameter and
> the query parameter are two different thing and you can map the query
> parameter to an expression which takes the report parameter and strips the
> time part of it. The downside of this is that the user will see the time in
> the parameter bar.
> One other point, you can have a text parameter instead of the date
> parameter. The user puts in the date as text and then you do whatever you
> want with it during the assignment to the query parameter.
> The portal that RS provides in version 1 is limited with how you can
> customize it. Hopefully we will see some improvements. For instance it sure
> would be nice to format the parameters (as you mentioned) or add custom
> error checking to it. Or a datepicker would be nice. No special knowledge of
> what the improvements would be. Just that I do agree with you that more
> control and options with it would be nice.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:53A33C93-0483-4305-BBF2-988B618FFF2D@.microsoft.com...
> > Crystal Reports has the option to convert all date-time to dates.
> >
> > This allows you to bring up all records which occured between a certain
> date
> > range irrespective of the time.
> >
> > Having the time portion in RS causes problems. If I want to pull up all
> > records which occured from 1 jan 2004 to 1 jan 2004. RS views this as
> being 1
> > jan 2004 12 am to 1 jan 2004 12 am. It therefore leaves out the other
> 23:59
> > hours on Jan 1.
> >
> > Are you saying this is not possible in RS without a custom webpage?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Ahh, sorry. Everybody answering you was answering with regards to the
> > > report. The parameter tool bar can not be modified as far as I know. You
> > > have the option of creating your own web page and then integrate into RS
> > > using URL control or web services.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > news:90B96A06-51F8-4E7C-BBD3-BBD671BA5887@.microsoft.com...
> > > > I am attempting to chnage the date format in the parameter control
> bar...
> > > as
> > > > far as I can see there is no properties selection to choose from.
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > Click on the control you want to format (the particular field in the
> > > table
> > > > > control, or a texbox for example) and go to properties, format,
> select
> > > > > expression and put this in when the expression box comes up.
> > > > >
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > > > news:C04EC5F8-0C74-41E7-B0BD-8886FD0395B6@.microsoft.com...
> > > > > > Hi Ravi:
> > > > > >
> > > > > > Where should I place it?
> > > > > >
> > > > > > "Ravi Mumulla (Microsoft)" wrote:
> > > > > >
> > > > > > > Try =FormatDateTime(Fields!myDate.Value, vbShortDate).
> > > > > > >
> > > > > > > --
> > > > > > > Ravi Mumulla (Microsoft)
> > > > > > > SQL Server Reporting Services
> > > > > > >
> > > > > > > This posting is provided "AS IS" with no warranties, and confers
> no
> > > > > rights.
> > > > > > > "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> > > > > > > news:F313E096-482A-4541-AC49-D929964A14D4@.microsoft.com...
> > > > > > > > My report contains a date-time parameter. When the report is
> run
> > > it
> > > > > > > displays
> > > > > > > > both the date and time portions in my date drop down box e.g.
> > > > > 01/01/2004
> > > > > > > > 12:00:00 AM.
> > > > > > > >
> > > > > > > > How would I change this to only display the 01/01/2004 ?
> > > > > > > >
> > > > > > > > Note: I want to maintain system localized date format.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>sql

date insert problem

hi...

my form has a text box which displays system date.

i am inserting date into MS SQL Server from this date textbox.

but it displays me error..

String was not recognized as a valid DateTime.

Line 154: myCommand1.ExecuteNonQuery()

i have written code as

myCommand1.Parameters.Add(New SqlParameter("@.date", SqlDbType.DateTime, 8))

myCommand1.Parameters("@.date").Value = FormatDateTime(datetxt.Text, DateFormat.GeneralDate)

and also tried to change date format with many other ways.

how should i solve this problem?

i also want to take time form a user with the help of web form and want to store it in other field called 'timein' and 'timeout'....

FormatDateTime returns a string. Use DateTime.Parse instead to create a valid datetime, and assign the datetime object parsed. Alternatively, you can use Convert.ToDateTime, but you get an exception for invalid dates. Tie your textbox in to a CompareValidator to ensure valid dates from the browser. I recommend turning off client validation for date validation as it does not validate medium date (e.g. 22-Apr-2007) correctly|||

Heh, who the hell ever types 22-Apr-2007? Personally, I wouldn't consider it valid input to begin with.

Since you have already stated that your sqldbtype is a datetime, just shove the string into the parameter.

EX:

cmd.parameter("@.MyDateTime).value=MyDateString

That of course assumes that you have already validated it as a valid date/time string format. Just make sure that you validate it both client and server side. Too many people I know drop the validators down on the client, then forget to wrap their "save" in a If page.IsValid(), and it works... Until a hacker comes along and posts some invalid data to the server.

Oh, and I would drop off the size parameter on the sqlparameter. Not sure if 8 is even the correct size for a datetime, but it's ignored anyway since datetime is a fixed size.

|||

The reason I suggest the use of medium date format is this. I've workd for a number of world-wide companies over many years, often on large projects. Hundreds of thousands of pounds have been wasted by developers and databases engineers, simply because of a US/UK date confusion, or forgetting to set the date locale to UK, then, some months later, finding a database full of data that is invalid, then having to fix it, and having systems off-line.

Medium date also still works in the local language, if you set the locale of a web site (e.g. Norway).

Developers should also be listening to business, who increasing, and in my view, rightly, want medium format. The whole of the rest of the world does not live in the US, that is why our web sites should be locale aware

Software development is also a team effort. It isn't down to the developer alone to ensure a web-site isn't hacked. Architects, Managers and Testers are all there to make it happen (safely).

As always, these are my opinions and suggestions, soI don't expect everyone to like or agree with them.

Happy coding all...

|||

That's all well and good, and I agree that applications should be built with globalization in mind, but that really has nothing to do with medium dates. We don't use them in the US, and they aren't used anywhere else in the world except for geeky tech documents. If you want to use a truly international standard, then use the ISO format (YYYY-MM-DD). It's the same format in every culture.

As for having incorrect dates in your database, that's why you should always use the datetime datatype. The value is the value no matter what culture you are in, infact I normally store all date/times in a database based on UTC. That way no matter where you are in the world, I can tell you what time and date a specific event occurred localized in your specific date format, and give you the time relative to your timezone.

Now, it may be in your company, that they have decided that medium format is best, but I can say that is an oddity, and not a rule. There is a standard date format, it's the ISO format that was approved by the international standards organization, and when people need a format that they don't want translated to their native culture's format, that is the one that should be used. After all, 01-Apr-2006 isn't correct for any other place but the US. They don't have "April" let alone "Apr" in other countries.

|||

More business documents in the UK are using Medium and even Long Dates than previously. You don't see YYYY-MM-DD used in the UK as most people would find it geeky (whatever that means). In my software I generally let the user override their locale anyway, and set a date format for the web site based on ISO8601, ISO, UK/US, etc.

Medium and Long dates do correctly translate into the local language. E.g. I just tried today's (long format - since Med format displays the same result) date on a simple web page, which returned 12-febrero-2006 when I changed the culture from UK English to international Spanish (es-ES).

BTW., It wasn't my database or even my company that had the incorrect dates. I spent 15 years contracting around the UK, working for various major companies. What I observed (and sometimes got asked for advice on) were issues where UK formatted dates (parsed as text using dd/mm/yy) were stored in a database that was assuming US format since the default language had never been changed. Eventually the systems broke, which gave rise to many issues and fixing. What I advised is this. If they had used medium format from the start, the issue would not have arose, because the text would have been corretcly converted into the underlying datetime type.

I am sure we can disagree long into the night about date formats. However, it's a minor issue, and you are correct to raise the point that storing dates in the underlying format is the correct way to go.

Date format problem

Hello all,
This has been bugging me for most of the day. The sql statement below works just fine. It displays the date in the correct format. But, i want to include the time as well.

SELECT id, CONVERT(varchar(10), request_id) + Space(1) + CONVERT(varchar(20), date, 101) AS REQUEST, seen FROM Notes WHERE seen = '0'

Any ideas?
Thanks in advance.

Richard M.

SELECT id, CONVERT(varchar(10), request_id) + Space(1) + CONVERT(varchar(20), date, 101) + Space(1) + CONVERT(varchar(20), date, 108) AS REQUEST, seen FROM Notes WHERE seen = '0'

|||Thanks. I can't believe it was that simple.

Richard M.

Date format

Hi,

I have a problem. I have a date field in which the month always displays as 00 wether I format as dd/mm/yy or mm/dd/yy ?

I also need to know how to format as longdate. Can any one help me with this?

try capitol MM

MM/dd/yy

|||

In the format property of the textbox add "d". Sounds weird but works. You can find more on date formatting in the foll post

http://technoblab.blogspot.com

..............................................................................

Please mark as "Answer" on the post that helped you

Date comparison problem

Hi,

I am designing an application which displays news topics until midnight
on the DisplayUntil date and then they should drop out. Unfortunately,
they seem to be dropping out at mid-day.

I'm storing the 'DisplayFrom' and 'DisplayUntil' dates as SmallDateTime
fields so the date is in the format "25/09/2006 00:0:00" and I'm
comparing them with GetDate() which (I suspect) includes the actual time
as well.

I'm using the following SQL..

SELECT *
FROM t_news
WHERE n_DisplayUntil >= GetDate() AND n_DisplayFrom <= GetDate()

in MS SQL Server Express 2005

I think this is probably all too simplistic - is there a way to compare
these dates so that they display from the beginning of the start date
(ie 00:00:01) until the end of the last day (ie 23:59:59)?

I'm getting so desparate that I'm even considering pulling the whole
recordset and doing the comparison in VBScript - and that's desparate!
Can anyone help me, please?

Regards
ukwebguynoone wrote:

Quote:

Originally Posted by

I'm getting so desparate that I'm even considering pulling the whole
recordset and doing the comparison in VBScript - and that's desparate!

If for some reason you can't get the back-end logic to work correctly,
at least try to get it to work almost-correctly (e.g. add/subtract one
day using the DateDiff function) and then use VBS to finish the job.|||On Mon, 25 Sep 2006 17:49:33 +0100, noone wrote:

Quote:

Originally Posted by

>Hi,
>
>I am designing an application which displays news topics until midnight
>on the DisplayUntil date and then they should drop out. Unfortunately,
>they seem to be dropping out at mid-day.
>
>I'm storing the 'DisplayFrom' and 'DisplayUntil' dates as SmallDateTime
>fields so the date is in the format "25/09/2006 00:0:00" and I'm
>comparing them with GetDate() which (I suspect) includes the actual time
>as well.
>
>I'm using the following SQL..
>
>SELECT *
>FROM t_news
>WHERE n_DisplayUntil >= GetDate() AND n_DisplayFrom <= GetDate()
>
>in MS SQL Server Express 2005
>
>I think this is probably all too simplistic - is there a way to compare
>these dates so that they display from the beginning of the start date
>(ie 00:00:01) until the end of the last day (ie 23:59:59)?
>
>I'm getting so desparate that I'm even considering pulling the whole
>recordset and doing the comparison in VBScript - and that's desparate!
>Can anyone help me, please?
>
>Regards
>ukwebguy

Hi ukwebguy,

First, read the information in Tibor's "ultimate guide to the datetime
datatype": http://www.karaszi.com/SQLServer/info_datetime.asp.

If the DisplayFrom and DisplayUntil columns contain a date only (ie, the
time part is set to the default value of midnight), you could use this:

DECLARE @.Today smalldatetime
-- Strip time portion from current date and time
SET @.Today = DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
SELECT *
FROM news
WHERE DisplayUntil >= @.Today
AND DisplayFrom <= @.Today

If DisplayFrom and DisplayUntil contain time portions as well, then you
need to clarify your requirements. Here's a syntax that MIGHT work for
you, but only if my guesswork about your actual requirements is correct.

DECLARE @.Today smalldatetime
-- Strip time portion from current date and time
SET @.Today = DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
SELECT *
FROM news
WHERE DisplayUntil >= @.Today
AND DisplayFrom < @.Today + 1

--
Hugo Kornelis, SQL Server MVP|||http://sql-server-performance.com/fk_datetime.asp
Madhivanan

Hugo Kornelis wrote:

Quote:

Originally Posted by

On Mon, 25 Sep 2006 17:49:33 +0100, noone wrote:
>

Quote:

Originally Posted by

Hi,

I am designing an application which displays news topics until midnight
on the DisplayUntil date and then they should drop out. Unfortunately,
they seem to be dropping out at mid-day.

I'm storing the 'DisplayFrom' and 'DisplayUntil' dates as SmallDateTime
fields so the date is in the format "25/09/2006 00:0:00" and I'm
comparing them with GetDate() which (I suspect) includes the actual time
as well.

I'm using the following SQL..

SELECT *
FROM t_news
WHERE n_DisplayUntil >= GetDate() AND n_DisplayFrom <= GetDate()

in MS SQL Server Express 2005

I think this is probably all too simplistic - is there a way to compare
these dates so that they display from the beginning of the start date
(ie 00:00:01) until the end of the last day (ie 23:59:59)?

I'm getting so desparate that I'm even considering pulling the whole
recordset and doing the comparison in VBScript - and that's desparate!
Can anyone help me, please?

Regards
ukwebguy

>
Hi ukwebguy,
>
First, read the information in Tibor's "ultimate guide to the datetime
datatype": http://www.karaszi.com/SQLServer/info_datetime.asp.
>
If the DisplayFrom and DisplayUntil columns contain a date only (ie, the
time part is set to the default value of midnight), you could use this:
>
DECLARE @.Today smalldatetime
-- Strip time portion from current date and time
SET @.Today = DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
SELECT *
FROM news
WHERE DisplayUntil >= @.Today
AND DisplayFrom <= @.Today
>
If DisplayFrom and DisplayUntil contain time portions as well, then you
need to clarify your requirements. Here's a syntax that MIGHT work for
you, but only if my guesswork about your actual requirements is correct.
>
DECLARE @.Today smalldatetime
-- Strip time portion from current date and time
SET @.Today = DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
SELECT *
FROM news
WHERE DisplayUntil >= @.Today
AND DisplayFrom < @.Today + 1
>
>
--
Hugo Kornelis, SQL Server MVP

Date comparison problem

Hi,

I am designing an application which displays news topics until midnight
on the DisplayUntil date and then they should drop out. Unfortunately,
they seem to be dropping out at mid-day.

I'm storing the 'DisplayFrom' and 'DisplayUntil' dates as SmallDateTime
fields so the date is in the format "25/09/2006 00:0:00" and I'm
comparing them with GetDate() which (I suspect) includes the actual time
as well.

I'm using the following SQL..

SELECT *
FROM t_news
WHERE n_DisplayUntil >= GetDate() AND n_DisplayFrom <= GetDate()

in MS SQL Server Express 2005

I think this is probably all too simplistic - is there a way to compare
these dates so that they display from the beginning of the start date
(ie 00:00:01) until the end of the last day (ie 23:59:59)?

I'm getting so desparate that I'm even considering pulling the whole
recordset and doing the comparison in VBScript - and that's desparate!
Can anyone help me, please?

Regards
ukwebguyHi,

what about something like this here:

DECLARE @.Start DATETIME
DECLARE @.End DATETIME

SET @.Start = '20060924'
SET @.End = '20060924'

Select 1
Where @.Start <= GETDATE() AND DATEDIFF(dd,GETDATE(),@.END) >= 0

HTH, jens K. Suessmeyer.

--
http://www.sqlserver2005.de
--