Date problem in SQL Server

Hi

Good day!

I am having some problem with the date-time datatype. I want to store only date in my table in this format - DD-MM-YYYY - and I do not need time. This must be compatible with the format of Visual Basic.

How to achieve this?

SQL Server datetime datatype stores both DATE and TIME. [datetime] has a presicion to milliseconds, and [shortdatetime] has a precision to minutes.

If you do NOT provide the TIME component, it will default to midnight (or 12:00:00 AM). It is very common for applications to ignore the time component. If you do not need the TIME component, use a shortdatetime datatype.

Refer to Books Online about [datetime] and [shortdatetime] datatypes.

|||

what is the problem in storing date and time. is it because of Storage size or is it because u wnat to show only date to the user. If you want to show only date.... this formating can be done at the frontend. Don't change the datatype... you store the data as datetime only and do the formating at the frontend. And also check CONVERT/CAST function in BOL.

Madhu

|||

You cannot do this with SQL Server 2005 and previous release. In Katmai (next release), we define several new datetime data types and you will be able to do it.

Or you can use the techniques mentioned by the previous posts.

Thanks.

|||

Hi Madhu

My problem is actually in queiry. When I use the type date/time and I assign a default value to GETDATE() in sql server, it stores both date and time. But when I run sproc with where clause like - WHERE Date = @.Date - where the time is not given, I can't find the date!

How to solve it? I am using VB to run the sproc.

Regards

Kapalic

|||

in this case you may need to use CONVERT Function of SQL Server. Suppose u r passing date mm/dd/yyyy then

Where convert(varchar(10),Date,101)='04-10-2007'

Madhu

|||

That is because when you are using @.Date without a time portion, it defaults to midnight (12:00:00.000), and you don't have any rows with that exact time.

Try the following WHERE clause:

Code Snippet

WHERE ( [Date] >= @.Date

AND [Date] < ( dateadd( day, 1, @.Date )

)

This assumes that @.Date is in the form of 'mm/dd/yyyy', and that you want all values from midnight until the last possible moment before the next midnight (Or [ >= 12:00:00.000 ] AND [ < 11:59:59.997 ]

(You don't really want to follow Madhu's suggestion of using convert(varchar(10), Date, 101) BECAUSE it would invalidate the use of an index and could be very inefficient.)

|||

Hi Arnie,

Thnk you very much for your reply. The problem is, I want to use the date format as DD/MM/YYYY. This I forgot to mention. And I want to link the sproc with a DateTimePicker control in Visual Basic.

Please resque...

Regards

Kapalic

|||

Read about CONVERT Function in BOL ... what u need to do is ... convert the date in sql to which ever format u required. the below statement will do that

if the supplied date is 04/10/2007 dd/mm/yyyy run the below mentioned statement and see teh result

Where convert(varchar(10),Date,105)='04-10-2007'

Madhu

|||

Using a VB date format of 'DD/MM/YYYY', and using a DateTimePicker is NOT a problem as long as you are using a Date datatype in VB, and the procedure call is using a SQL Datetime datatype.

If your VB date variable it used when you make the SQL Stored procedure call, the WHERE clause example above will work just fine.

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