I need to be able to determine whether a particular
datetime value is a public holiday (in the uk) or not and
I cannot find a documented way of deteriming this. Please
could you advise me whether there is a function or method
of determining this in SQL 2000 Enterprise Edition?
If there is not could you advise me a robust method of
providing this functionality for developers (I am a design
DBA who works with a number of different development teams
and it would seem appropriate for everyone to use the same
method)?
Many thanks,
DavePublic holiday dates are not always determined by a fixed logic. You should
build your own calendar table for this and populate it in advance with as
much data as you need.
CREATE TABLE Calendar (caldate DATETIME NOT NULL PRIMARY KEY, workingday
CHAR(1) NOT NULL CHECK (workingday IN ('Y','N')) DEFAULT 'Y')
INSERT INTO Calendar (caldate) VALUES ('20000101')
Populate it (this is 11 years worth of dates):
WHILE (SELECT MAX(caldate) FROM Calendar)<'20101231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(D,'19991231',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar
Now update the non-working days:
UPDATE Calendar SET workingday = 'N'
WHERE DATENAME(DW,caldate) IN ('Saturday','Sunday')
A source I use for public holiday dates is: http://www.bank-holidays.com
--
David Portas
--
Please reply only to the newsgroup
--
Showing posts with label value. Show all posts
Showing posts with label value. Show all posts
Thursday, March 22, 2012
DateTime.Min won't insert into SQL Server Mobile 3.0
Hi all,
In my C# code I have a Class property that takes the value DateTime.Min upon initialisation, but when I try to insert this into the database column (yes, it is DateTime data type :)) I get an 'Unexpected Error' from SQL Server Mobile.
Is this a known?
Tryst
Hi
It is due to the diffrence between the Min date of C# and the Min Date of SQL Server. The Min Date in C# is 01/01/01 while in SQL Server it is 01/01/1753 ... so create your own Min Date equlient to SQL Server Min Date and the Problem will be resolved.
|||ok, thanks, Akbar Khan.sqldatetime transformation
Hello,
How can I convert datetime value :
2005-10-11 00:00:00.000
to date value:
2005-10-11 ?
Thanks,
GBSee CONVERT() function in SQL Server Books Online.
Anith|||On Thu, 11 May 2006 20:46:39 GMT, GB wrote:
>Hello,
>How can I convert datetime value :
>2005-10-11 00:00:00.000
>to date value:
>2005-10-11 ?
Hi GB,
Technically, these are the same values. If a datetime is given without
time portion, it defaults to midnight. So no conversion needed.
If your question is about showing the date only and suppressing the time
part, my first recommendation would be to do the formatting in the
front-end. This has the advantage that you can take the locale settings
of the workstation into account and format the date the way the
individual user prefers.
If you HAVE to do it on the server, then check out the subject on CAST
and CONVERT in Books Online. Adjusting the style parameter allows you to
convert a datetime to a string variable with the foormatting of your
choice.
For instance, for yyyy-mm-dd, you could use
SELECT CONVERT(char(10), TheDate, 126)
Hugo Kornelis, SQL Server MVP
How can I convert datetime value :
2005-10-11 00:00:00.000
to date value:
2005-10-11 ?
Thanks,
GBSee CONVERT() function in SQL Server Books Online.
Anith|||On Thu, 11 May 2006 20:46:39 GMT, GB wrote:
>Hello,
>How can I convert datetime value :
>2005-10-11 00:00:00.000
>to date value:
>2005-10-11 ?
Hi GB,
Technically, these are the same values. If a datetime is given without
time portion, it defaults to midnight. So no conversion needed.
If your question is about showing the date only and suppressing the time
part, my first recommendation would be to do the formatting in the
front-end. This has the advantage that you can take the locale settings
of the workstation into account and format the date the way the
individual user prefers.
If you HAVE to do it on the server, then check out the subject on CAST
and CONVERT in Books Online. Adjusting the style parameter allows you to
convert a datetime to a string variable with the foormatting of your
choice.
For instance, for yyyy-mm-dd, you could use
SELECT CONVERT(char(10), TheDate, 126)
Hugo Kornelis, SQL Server MVP
DateTime to Varchar
Im trying to convert a datetime value to a varchar
Ive used cast( datetimevalue as varchar(10)) but am not geting the desired
result. Im looking for a DD/MM/YYYY resultUse function CONVERT instead.
Example:
select convert(char(10), getdate(), 103)
AMB
"Peter Newman" wrote:
> Im trying to convert a datetime value to a varchar
> Ive used cast( datetimevalue as varchar(10)) but am not geting the desire
d
> result. Im looking for a DD/MM/YYYY result|||try this one
Select
Convert(Varchar(12),GetDate(),101),
Convert(VarChar(12),GetDate(),102),
Convert(VarChar(12),GetDate(),103),
Convert(VarChar(12),GetDate(),104),
Convert(VarChar(12),GetDate(),105),
Convert(VarChar(12),GetDate(),106),
Convert(VarChar(12),GetDate(),107),
Convert(Varchar(11),GetDate(),108),
Convert(VarChar(12),GetDate(),109),
Convert(VarChar(12),GetDate(),110),
Convert(VarChar(12),GetDate(),111),
Convert(VarChar(12),GetDate(),112),
Convert(VarChar(12),GetDate(),113),
Convert(VarChar(12),GetDate(),114)
nivek
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:3F16F075-1C14-4CCD-A91D-13231E67A071@.microsoft.com...
> Im trying to convert a datetime value to a varchar
> Ive used cast( datetimevalue as varchar(10)) but am not geting the
> desired
> result. Im looking for a DD/MM/YYYY result
Ive used cast( datetimevalue as varchar(10)) but am not geting the desired
result. Im looking for a DD/MM/YYYY resultUse function CONVERT instead.
Example:
select convert(char(10), getdate(), 103)
AMB
"Peter Newman" wrote:
> Im trying to convert a datetime value to a varchar
> Ive used cast( datetimevalue as varchar(10)) but am not geting the desire
d
> result. Im looking for a DD/MM/YYYY result|||try this one
Select
Convert(Varchar(12),GetDate(),101),
Convert(VarChar(12),GetDate(),102),
Convert(VarChar(12),GetDate(),103),
Convert(VarChar(12),GetDate(),104),
Convert(VarChar(12),GetDate(),105),
Convert(VarChar(12),GetDate(),106),
Convert(VarChar(12),GetDate(),107),
Convert(Varchar(11),GetDate(),108),
Convert(VarChar(12),GetDate(),109),
Convert(VarChar(12),GetDate(),110),
Convert(VarChar(12),GetDate(),111),
Convert(VarChar(12),GetDate(),112),
Convert(VarChar(12),GetDate(),113),
Convert(VarChar(12),GetDate(),114)
nivek
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:3F16F075-1C14-4CCD-A91D-13231E67A071@.microsoft.com...
> Im trying to convert a datetime value to a varchar
> Ive used cast( datetimevalue as varchar(10)) but am not geting the
> desired
> result. Im looking for a DD/MM/YYYY result
Wednesday, March 21, 2012
Datetime string
Dear all,
I got a fieldA which is datetime , when I check the value
in query analyzer, the value returned is: 2003-10-27 10:55:00.000.
When I get this field to a ADODB.recordset named rs_A, rs_A
returns :
2006/4/26 =A4W=A4=C8 09:24:17 ,which is date time string format
with Chinese String.
When I try to insert '2006/4/26 =A4W=A4=C8 09:24:17' into a datetime
field, the query analyzer complains
Syntax error converting datetime from character string.
When I cast('2006/4/26 =A4W=A4=C8 09:24:17' as datetime), the query
analyzer also complains
Syntax error converting datetime from character string.
My quetions is how to keep the data time value as 2003-10-27
10:55:00.000 but not 2006/4/26 =A4W=A4=C8 09:24:17 .
Thanks.http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hon123456" <peterhon321@.yahoo.com.hk> wrote in message
news:1146016353.858447.49210@.i40g2000cwc.googlegroups.com...
Dear all,
I got a fieldA which is datetime , when I check the value
in query analyzer, the value returned is: 2003-10-27 10:55:00.000.
When I get this field to a ADODB.recordset named rs_A, rs_A
returns :
2006/4/26 W 09:24:17 ,which is date time string format
with Chinese String.
When I try to insert '2006/4/26 W 09:24:17' into a datetime
field, the query analyzer complains
Syntax error converting datetime from character string.
When I cast('2006/4/26 W 09:24:17' as datetime), the query
analyzer also complains
Syntax error converting datetime from character string.
My quetions is how to keep the data time value as 2003-10-27
10:55:00.000 but not 2006/4/26 W 09:24:17 .
Thanks.|||Hi
declare @.dt varchar(20)
set @.dt='2006/4/26 10:22:55'
create table #table (c datetime)
insert into #table (c)
select cast(rtrim(y*10000+m*100+d)+' '+ t as datetime)
from
(
select year(@.dt) as y,month(@.dt) as m ,day(@.dt)as d,
right(@.dt, CHARINDEX(' ', REVERSE(@.dt))-1) t
) as der
select * from #table
"hon123456" <peterhon321@.yahoo.com.hk> wrote in message
news:1146016353.858447.49210@.i40g2000cwc.googlegroups.com...
Dear all,
I got a fieldA which is datetime , when I check the value
in query analyzer, the value returned is: 2003-10-27 10:55:00.000.
When I get this field to a ADODB.recordset named rs_A, rs_A
returns :
2006/4/26 W 09:24:17 ,which is date time string format
with Chinese String.
When I try to insert '2006/4/26 W 09:24:17' into a datetime
field, the query analyzer complains
Syntax error converting datetime from character string.
When I cast('2006/4/26 W 09:24:17' as datetime), the query
analyzer also complains
Syntax error converting datetime from character string.
My quetions is how to keep the data time value as 2003-10-27
10:55:00.000 but not 2006/4/26 W 09:24:17 .
Thanks.|||hon123456 (peterhon321@.yahoo.com.hk) writes:
> I got a fieldA which is datetime , when I check the value
> in query analyzer, the value returned is: 2003-10-27 10:55:00.000.
> When I get this field to a ADODB.recordset named rs_A, rs_A
> returns :
> 2006/4/26 W 09:24:17 ,which is date time string format
> with Chinese String.
> When I try to insert '2006/4/26 W 09:24:17' into a datetime
> field, the query analyzer complains
> Syntax error converting datetime from character string.
> When I cast('2006/4/26 W 09:24:17' as datetime), the query
> analyzer also complains
> Syntax error converting datetime from character string.
> My quetions is how to keep the data time value as 2003-10-27
> 10:55:00.000 but not 2006/4/26 W 09:24:17 .
One answer to that particular question, is to change your regional settings
to Swedish, or at least change the datetime format in regional settings. I
would not recommend that though.
What I don't really understand is you need to take a value from an
ADO recordset and paste into Query Analyzer.
When you pass dates to and from SQL Server, you should do so in binary
format. The client API will then convert from/to string format according
to regional settings.
I suspect that you do something like this in your ADO code:
sql = "SELECT ... FROM tbl WHERE datetimecol = '" & rs("dt") & "'"
Don't do that. Run a parameterised query instead. Here is a quick sample
query:
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = " SELECT OrderID, OrderDate, CustomerID, ShipName " & _
" FROM dbo.Orders WHERE 1 = 1 "
If custid <> "" Then
cmd.CommandText = cmd.CommandText & " AND CustomerID LIKE ? "
cmd.Parameters.Append
cmd.CreateParameter("@.custid", adWChar, adParamInput, 5, custid)
End If
If shipname <> "" Then
cmd.CommandText = cmd.CommandText & " AND ShipName LIKE ? "
cmd.Parameters.Append cmd.CreateParameter("@.shipname", _
adVarWChar, adParamInput, 40, shipname)
End If
Set rs = cmd.Execute
This example does not includ a datetime parameter, but at least you get
to see the principle.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
I got a fieldA which is datetime , when I check the value
in query analyzer, the value returned is: 2003-10-27 10:55:00.000.
When I get this field to a ADODB.recordset named rs_A, rs_A
returns :
2006/4/26 =A4W=A4=C8 09:24:17 ,which is date time string format
with Chinese String.
When I try to insert '2006/4/26 =A4W=A4=C8 09:24:17' into a datetime
field, the query analyzer complains
Syntax error converting datetime from character string.
When I cast('2006/4/26 =A4W=A4=C8 09:24:17' as datetime), the query
analyzer also complains
Syntax error converting datetime from character string.
My quetions is how to keep the data time value as 2003-10-27
10:55:00.000 but not 2006/4/26 =A4W=A4=C8 09:24:17 .
Thanks.http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hon123456" <peterhon321@.yahoo.com.hk> wrote in message
news:1146016353.858447.49210@.i40g2000cwc.googlegroups.com...
Dear all,
I got a fieldA which is datetime , when I check the value
in query analyzer, the value returned is: 2003-10-27 10:55:00.000.
When I get this field to a ADODB.recordset named rs_A, rs_A
returns :
2006/4/26 W 09:24:17 ,which is date time string format
with Chinese String.
When I try to insert '2006/4/26 W 09:24:17' into a datetime
field, the query analyzer complains
Syntax error converting datetime from character string.
When I cast('2006/4/26 W 09:24:17' as datetime), the query
analyzer also complains
Syntax error converting datetime from character string.
My quetions is how to keep the data time value as 2003-10-27
10:55:00.000 but not 2006/4/26 W 09:24:17 .
Thanks.|||Hi
declare @.dt varchar(20)
set @.dt='2006/4/26 10:22:55'
create table #table (c datetime)
insert into #table (c)
select cast(rtrim(y*10000+m*100+d)+' '+ t as datetime)
from
(
select year(@.dt) as y,month(@.dt) as m ,day(@.dt)as d,
right(@.dt, CHARINDEX(' ', REVERSE(@.dt))-1) t
) as der
select * from #table
"hon123456" <peterhon321@.yahoo.com.hk> wrote in message
news:1146016353.858447.49210@.i40g2000cwc.googlegroups.com...
Dear all,
I got a fieldA which is datetime , when I check the value
in query analyzer, the value returned is: 2003-10-27 10:55:00.000.
When I get this field to a ADODB.recordset named rs_A, rs_A
returns :
2006/4/26 W 09:24:17 ,which is date time string format
with Chinese String.
When I try to insert '2006/4/26 W 09:24:17' into a datetime
field, the query analyzer complains
Syntax error converting datetime from character string.
When I cast('2006/4/26 W 09:24:17' as datetime), the query
analyzer also complains
Syntax error converting datetime from character string.
My quetions is how to keep the data time value as 2003-10-27
10:55:00.000 but not 2006/4/26 W 09:24:17 .
Thanks.|||hon123456 (peterhon321@.yahoo.com.hk) writes:
> I got a fieldA which is datetime , when I check the value
> in query analyzer, the value returned is: 2003-10-27 10:55:00.000.
> When I get this field to a ADODB.recordset named rs_A, rs_A
> returns :
> 2006/4/26 W 09:24:17 ,which is date time string format
> with Chinese String.
> When I try to insert '2006/4/26 W 09:24:17' into a datetime
> field, the query analyzer complains
> Syntax error converting datetime from character string.
> When I cast('2006/4/26 W 09:24:17' as datetime), the query
> analyzer also complains
> Syntax error converting datetime from character string.
> My quetions is how to keep the data time value as 2003-10-27
> 10:55:00.000 but not 2006/4/26 W 09:24:17 .
One answer to that particular question, is to change your regional settings
to Swedish, or at least change the datetime format in regional settings. I
would not recommend that though.
What I don't really understand is you need to take a value from an
ADO recordset and paste into Query Analyzer.
When you pass dates to and from SQL Server, you should do so in binary
format. The client API will then convert from/to string format according
to regional settings.
I suspect that you do something like this in your ADO code:
sql = "SELECT ... FROM tbl WHERE datetimecol = '" & rs("dt") & "'"
Don't do that. Run a parameterised query instead. Here is a quick sample
query:
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = " SELECT OrderID, OrderDate, CustomerID, ShipName " & _
" FROM dbo.Orders WHERE 1 = 1 "
If custid <> "" Then
cmd.CommandText = cmd.CommandText & " AND CustomerID LIKE ? "
cmd.Parameters.Append
cmd.CreateParameter("@.custid", adWChar, adParamInput, 5, custid)
End If
If shipname <> "" Then
cmd.CommandText = cmd.CommandText & " AND ShipName LIKE ? "
cmd.Parameters.Append cmd.CreateParameter("@.shipname", _
adVarWChar, adParamInput, 40, shipname)
End If
Set rs = cmd.Execute
This example does not includ a datetime parameter, but at least you get
to see the principle.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
datetime report parameter default value
Issue 1:
I have a report parameter StartDateTime. I set the default value to Now(). When I go to preview, the StartDateTime parameter is empty and its been locked. I am not even able to set it to different value in preview.
Can anyone help me how to set the datetime parameter to default value(Now).
Issue 2:
I have a stored procedure which takes StartDateTime parameter. Whenever the report refreshes using autorefresh interval, the startdatetime should default to Now. Right now the startdatetime defaults to whatever the value is there before i hit view report. how to do that using stored procedure.
Thanks fro your help.
I don't know about Issue 2, but are you using "=Now()" to populate the StartDateTime report parameter? Also, is the report parameter type set to "datetime"? I think fixing Issue 1 should automatically solve Issue 2 since it looks like the report isn't populating with the correct default date value.|||hey, Thanks for u'r reply. Now it started working. I mean using "=Now()" I could set the default value of StartDateTime.
Regarding Issue2, Whatever the value is there in the StartDateTime, its retaining for autorefresh. Its not taking the CurrentDateTime.
|||Suppose the user enters a date for the startdate, and then the report refreshes. I assume that in that case you do not want the start date to change when the report .
If my assumption is correct, I think the best way for you to accomplish this would be to set the parameter to allow a null value, and use null to indicate that the user wants to run the report using the current time. This way, when the report refreshes the startdate will still be null, and the report data will adjust.
Unfortunately, there is no way to have the parameter show the current time and return null, and still allow the user to enter a date manually.
|||
Thanks for your reply. There is more clarification from the customer on the requirements. Little bit of changes to the original question I posted..
We are trying to use a SSRS report as a dashboard on a TV screen. I am facing two issues that I need advice on
Issue 1 : We are using End Date Time and Duration as user selectable parameters. We autorefresh the report once every 15 minutes using report properties refresh. Essentially the customer wants a rolling time window report the auto refreshes.
If we use Now() function for End Date Time, at the end of the 15 minutes it still uses the the time from first time instead of the time at the refresh. How do I fix this?
Issue 2: We are using a custom ASP.NET appliction in which the SSRS Report Viewer displays the report. When the report refreshes, it does not retain the window scroll position. How can I fix it?
Thanks in advance.
|||Sorry it took so long for me to get back to you. hopefully you found a solution already, but if not here are my thoughts:
The first issue is actually two issues:
How to get a report to run using a 'rolling time period' on auto-refresh, and
how to get the parameters to change at refresh so that they match the reporting period.
The solution to the first is outlined above. Instead of using now(), use getdate() in your sql query.
The solution to the latter is more difficult, if not impossible. I would suggest not displaying the reporting period in the parameters (you can put it in the body of the report or something.)
I have had some luck in the past getting parameters to refresh if I put them in the available values instead of in the default values, but that doesn't seem to be working for me when I ran a quick test, so either I've forgotten how to do it, or I never actually got it to work the first time.
I'm not sure what the best way to approach the second problem is. Can you intercept the scroll events, or are they happening inside the ssrss viewer?
sqldatetime report parameter default value
Issue 1:
I have a report parameter StartDateTime. I set the default value to Now(). When I go to preview, the StartDateTime parameter is empty and its been locked. I am not even able to set it to different value in preview.
Can anyone help me how to set the datetime parameter to default value(Now).
Issue 2:
I have a stored procedure which takes StartDateTime parameter. Whenever the report refreshes using autorefresh interval, the startdatetime should default to Now. Right now the startdatetime defaults to whatever the value is there before i hit view report. how to do that using stored procedure.
Thanks fro your help.
I don't know about Issue 2, but are you using "=Now()" to populate the StartDateTime report parameter? Also, is the report parameter type set to "datetime"? I think fixing Issue 1 should automatically solve Issue 2 since it looks like the report isn't populating with the correct default date value.|||hey, Thanks for u'r reply. Now it started working. I mean using "=Now()" I could set the default value of StartDateTime.
Regarding Issue2, Whatever the value is there in the StartDateTime, its retaining for autorefresh. Its not taking the CurrentDateTime.
|||Suppose the user enters a date for the startdate, and then the report refreshes. I assume that in that case you do not want the start date to change when the report .
If my assumption is correct, I think the best way for you to accomplish this would be to set the parameter to allow a null value, and use null to indicate that the user wants to run the report using the current time. This way, when the report refreshes the startdate will still be null, and the report data will adjust.
Unfortunately, there is no way to have the parameter show the current time and return null, and still allow the user to enter a date manually.
|||
Thanks for your reply. There is more clarification from the customer on the requirements. Little bit of changes to the original question I posted..
We are trying to use a SSRS report as a dashboard on a TV screen. I am facing two issues that I need advice on
Issue 1 : We are using End Date Time and Duration as user selectable parameters. We autorefresh the report once every 15 minutes using report properties refresh. Essentially the customer wants a rolling time window report the auto refreshes.
If we use Now() function for End Date Time, at the end of the 15 minutes it still uses the the time from first time instead of the time at the refresh. How do I fix this?
Issue 2: We are using a custom ASP.NET appliction in which the SSRS Report Viewer displays the report. When the report refreshes, it does not retain the window scroll position. How can I fix it?
Thanks in advance.
|||Sorry it took so long for me to get back to you. hopefully you found a solution already, but if not here are my thoughts:
The first issue is actually two issues:
How to get a report to run using a 'rolling time period' on auto-refresh, and
how to get the parameters to change at refresh so that they match the reporting period.
The solution to the first is outlined above. Instead of using now(), use getdate() in your sql query.
The solution to the latter is more difficult, if not impossible. I would suggest not displaying the reporting period in the parameters (you can put it in the body of the report or something.)
I have had some luck in the past getting parameters to refresh if I put them in the available values instead of in the default values, but that doesn't seem to be working for me when I ran a quick test, so either I've forgotten how to do it, or I never actually got it to work the first time.
I'm not sure what the best way to approach the second problem is. Can you intercept the scroll events, or are they happening inside the ssrss viewer?
DATETIME question
What is the best way to encode a DATETIME value given three integer values:
Year, Month, Day (I only need precision up to a given day, no time values).
I know i can form a date string, but I am relunctant to use that since it is dependant on the Language settings of the current session.
for example:
SELECT CAST(CAST(2004 AS VARCHAR) + '/' + CAST(1 AS VARCHAR) + '/' + CAST(5 AS VARCHAR) AS DATETIME);
Is January 5, 2004 when the session's language is set toENGLISH/US_ENGLISH, but equals May 1, 2004 when the session's language is set to FRENCH;
Basically I'm looking for something like MakeDate(Year AS INT, Month AS TINYINT, Day AS TINYINT) that returns a DATETIME value.The string '2004-01-05' is always January 5, 2004 and the string '2004-05-01' is always May 1, 2004. The ISO standard date string format is a wonderful thing!
-PatP|||thanks Pat1...I had noticed the ASCII (YYYYMMDD) format always worked...but I didn't like the idea of manually padding the Months/days with a '0' for Months/days in the 1-9 range.....but the '-' gives me the proper separator for years/months/days....to think that any select statement that returns a date does so in the format you mentionned, and I didn't notice....I need some sleep.
thanks again.
Year, Month, Day (I only need precision up to a given day, no time values).
I know i can form a date string, but I am relunctant to use that since it is dependant on the Language settings of the current session.
for example:
SELECT CAST(CAST(2004 AS VARCHAR) + '/' + CAST(1 AS VARCHAR) + '/' + CAST(5 AS VARCHAR) AS DATETIME);
Is January 5, 2004 when the session's language is set toENGLISH/US_ENGLISH, but equals May 1, 2004 when the session's language is set to FRENCH;
Basically I'm looking for something like MakeDate(Year AS INT, Month AS TINYINT, Day AS TINYINT) that returns a DATETIME value.The string '2004-01-05' is always January 5, 2004 and the string '2004-05-01' is always May 1, 2004. The ISO standard date string format is a wonderful thing!
-PatP|||thanks Pat1...I had noticed the ASCII (YYYYMMDD) format always worked...but I didn't like the idea of manually padding the Months/days with a '0' for Months/days in the 1-9 range.....but the '-' gives me the proper separator for years/months/days....to think that any select statement that returns a date does so in the format you mentionned, and I didn't notice....I need some sleep.
thanks again.
datetime procedure argument wont accept datetime value?
I am using Microsoft SQL Server Management Studio to connect to MSSQL 2005.... There is a stored procedure which has the following value amongst its parameters:
@.created_after datetime = null,
When I right-click the procedure and select Execute Stored Procedure, and enter a date like "23/01/2007 8:54:59 AM" into that field the following error is produced:
Msg 8114, Level 16, State 5, Procedure search, Line 0
Error converting data type nvarchar to datetime.
(1 row(s) affected)
As well as showing the following:
USE [FileStore]
GO
DECLARE @.return_value int
EXEC @.return_value = [dbo].[search]
@.created_after = N'23/01/2007 8:54:59 AM'
SELECT 'Return Value' = @.return_value
GO
Any idea why it will not accept my date value?
Any help would be appreciated ... Thank you :)
try input the datetime in universal format YYYYMMDD HH:MM:SSYour system date format might not be DD/MM/YYYY|||You seem to be 100% correct! 
How can I change this?
Monday, March 19, 2012
Datetime Parameter Format
Hi,all
I have a datetime parameter,
I use calendar to select value,but I want to format it as "yyyy-MM"
Any suggest?Can I use expression,how to ?
Kevin ChuJust get the value in as datetime format and use
format(Fields!xxx.Value,"yyyy-MM")
--
Tom Stude
"Kevin" wrote:
> Hi,all
> I have a datetime parameter,
> I use calendar to select value,but I want to format it as "yyyy-MM"
> Any suggest?Can I use expression,how to ?
> Kevin Chu
>|||=?Utf-8?B?VG9t?= <membership@.stude.no> wrote in news:ADC758F7-F6A7-4F56-
8C5C-1BAD643ABF30@.microsoft.com:
I want to show "yyyy-MM" format in preview,not get the value
> Just get the value in as datetime format and use
> format(Fields!xxx.Value,"yyyy-MM")
>
I have a datetime parameter,
I use calendar to select value,but I want to format it as "yyyy-MM"
Any suggest?Can I use expression,how to ?
Kevin ChuJust get the value in as datetime format and use
format(Fields!xxx.Value,"yyyy-MM")
--
Tom Stude
"Kevin" wrote:
> Hi,all
> I have a datetime parameter,
> I use calendar to select value,but I want to format it as "yyyy-MM"
> Any suggest?Can I use expression,how to ?
> Kevin Chu
>|||=?Utf-8?B?VG9t?= <membership@.stude.no> wrote in news:ADC758F7-F6A7-4F56-
8C5C-1BAD643ABF30@.microsoft.com:
I want to show "yyyy-MM" format in preview,not get the value
> Just get the value in as datetime format and use
> format(Fields!xxx.Value,"yyyy-MM")
>
datetime JDBC date conversion
I am writing a datetime field value to MS SQL Server 7 in the following manner
via a stored procedure:
via a stored procedure:
// item to be written is originally a java.util.Date object
java.util.Date fromDate;
// I'm inserting it here into the database
cstmt.setTimestamp(8, new Timestamp(fromDate.getTime()));
// the record in the database appears as follows - as I wanted it to..
12/23/2004 4:30:43 AM
The problem is reading the date FROM the database back into ANY type of Java
Date-related object. No matter what I try, the hour/minutes/seconds are not
returned, and I desperately need the hour and minutes. I don't want to store
the hours and minutes in another field - it just causes more complications.
Does anyone out there know a way to get the ENTIRE date value out of the database?Hi
Are you saying that getTime() returns 00:00:00 ?
Posting your the code that tries to retrive this may help!
John
"billb" <kmilburn@.austin.rr.com> wrote in message
news:3561fddb.0410261050.6c8bafba@.posting.google.c om...
> I am writing a datetime field value to MS SQL Server 7 in the following
manner
> via a stored procedure:
> // item to be written is originally a java.util.Date object
> java.util.Date fromDate;
> // I'm inserting it here into the database
> cstmt.setTimestamp(8, new Timestamp(fromDate.getTime()));
> // the record in the database appears as follows - as I wanted it to..
> 12/23/2004 4:30:43 AM
> The problem is reading the date FROM the database back into ANY type of
Java
> Date-related object. No matter what I try, the hour/minutes/seconds are
not
> returned, and I desperately need the hour and minutes. I don't want to
store
> the hours and minutes in another field - it just causes more
complications.
> Does anyone out there know a way to get the ENTIRE date value out of the
database?|||
billb wrote:
> I am writing a datetime field value to MS SQL Server 7 in the following manner
> via a stored procedure:
> // item to be written is originally a java.util.Date object
> java.util.Date fromDate;
> // I'm inserting it here into the database
> cstmt.setTimestamp(8, new Timestamp(fromDate.getTime()));
> // the record in the database appears as follows - as I wanted it to..
> 12/23/2004 4:30:43 AM
> The problem is reading the date FROM the database back into ANY type of Java
> Date-related object. No matter what I try, the hour/minutes/seconds are not
> returned, and I desperately need the hour and minutes. I don't want to store
> the hours and minutes in another field - it just causes more complications.
> Does anyone out there know a way to get the ENTIRE date value out of the database?
Are you sure you tried getTimestamp()?
Joe Weinstein at BEA|||billb wrote:
> I am writing a datetime field value to MS SQL Server 7 in the following manner
> via a stored procedure:
> // item to be written is originally a java.util.Date object
> java.util.Date fromDate;
> // I'm inserting it here into the database
> cstmt.setTimestamp(8, new Timestamp(fromDate.getTime()));
> // the record in the database appears as follows - as I wanted it to..
> 12/23/2004 4:30:43 AM
> The problem is reading the date FROM the database back into ANY type of Java
> Date-related object. No matter what I try, the hour/minutes/seconds are not
> returned, and I desperately need the hour and minutes. I don't want to store
> the hours and minutes in another field - it just causes more complications.
> Does anyone out there know a way to get the ENTIRE date value out of the database?
Convert DATETIME into CHARATER using MS SQL finction is SELECT statement:
CONVERT ( VARCHAR( length ), some_datetime_colimn, <style> )
where <style> is magic number which defines format( 120 ~ yyyy-mm-dd HH:mi:ss);
get value from ResultSet as string and parse it using
java.text.DateFormat.parseDate(...)|||Hi,
Please use the following to construct a full date object from database date
field.
java.util.Date myDate = new
java.util.Date(myResultSet.getTimestamp("myDate").getTime());
I hope this will help ..
Regards,
Yasir
"billb" <kmilburn@.austin.rr.com> wrote in message
news:3561fddb.0410261050.6c8bafba@.posting.google.c om...
> I am writing a datetime field value to MS SQL Server 7 in the following
manner
> via a stored procedure:
> // item to be written is originally a java.util.Date object
> java.util.Date fromDate;
> // I'm inserting it here into the database
> cstmt.setTimestamp(8, new Timestamp(fromDate.getTime()));
> // the record in the database appears as follows - as I wanted it to..
> 12/23/2004 4:30:43 AM
> The problem is reading the date FROM the database back into ANY type of
Java
> Date-related object. No matter what I try, the hour/minutes/seconds are
not
> returned, and I desperately need the hour and minutes. I don't want to
store
> the hours and minutes in another field - it just causes more
complications.
> Does anyone out there know a way to get the ENTIRE date value out of the
database?
datetime in XP
I am passing in a datetime value into a extended stored procedure, and
thought I could read it into a double. its failing and I am not sure
why.
..
BYTE bType;
ULONG ulMaxLen;
ULONG ulActualLen;
BOOL isParamNull;
int inputParamIndex = 1;
srv_paraminfo(srvroc, inputParamIndex, &bType, &ulMaxLen,
&ulActualLen, NULL, &isParamNull );
BYTE* ldata = new BYTE[ulActualLen];
memset(ldata, '\0', ulActualLen);
srv_paraminfo(Proc, inputParamIndex, &bType, &ulMaxLen, &ulActualLen,
ldata, &isParamNull );
double xdate = (double)*ldata;
delete []ldata;
...
xdate should have the date, but doesn't.
ThanksI figured it out
thought I could read it into a double. its failing and I am not sure
why.
..
BYTE bType;
ULONG ulMaxLen;
ULONG ulActualLen;
BOOL isParamNull;
int inputParamIndex = 1;
srv_paraminfo(srvroc, inputParamIndex, &bType, &ulMaxLen,
&ulActualLen, NULL, &isParamNull );
BYTE* ldata = new BYTE[ulActualLen];
memset(ldata, '\0', ulActualLen);
srv_paraminfo(Proc, inputParamIndex, &bType, &ulMaxLen, &ulActualLen,
ldata, &isParamNull );
double xdate = (double)*ldata;
delete []ldata;
...
xdate should have the date, but doesn't.
ThanksI figured it out
DateTime in UTC
I have a DateTime column in a database table.
How can I get the equivalent UTC value for this column?
eg something like the DateTime.ToUniversalTime() in C#.
or select DATE_COL1, getutcdate(DATE_COL1) FROM TABLE
nb: The GETUTCDATE() function returns the current utc DATEIt depends what you want to do exactly - is the offset from UTC based
on where the server is physically, where the clients are physically, or
something else? I don't believe there's any easy way to do this in
MSSQL, because you need to know the server's location and current UTC
offset, so you would probably need an external program which gets this
information from the operating system. If you want to base the offset
on the clients' location, then things would be more complicated,
especially if you have clients in different time zones.
If the offset is constant, you could put it in a lookup table and
create your own scalar function to modify the date, but then you would
need to handle daylight savings and so on yourself as well. So if the
C# function you mentioned already does what you want, it might be
easiest just to use it in an external program (in SQL 2005 you could
write a C# stored procedure or function to do this).
Simon|||PromisedOyster (PromisedOyster@.hotmail.com) writes:
> I have a DateTime column in a database table.
> How can I get the equivalent UTC value for this column?
> eg something like the DateTime.ToUniversalTime() in C#.
> or select DATE_COL1, getutcdate(DATE_COL1) FROM TABLE
>
> nb: The GETUTCDATE() function returns the current utc DATE
Use the dateadd() function. You will have to handle the logic for
the offset to UTC yourself, as SQL Server does not have any time zone
information.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||hi
hope this trick would work:
select DATE_COL1, dateadd("mi", datediff("mi",GETUTCDATE() ,getdate())
,DATE_COL1)
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
------------
*** Sent via Developersdex http://www.developersdex.com ***|||We do this all the time:
--test method
DECLARE @.Date smalldatetime
SET @.Date = GETDATE()
SELECT @.date, DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()),
@.Date), GETUTCDATE()
You could deal with minutes, but since UTC time is a change in hours,
simply your life :)
Stu|||Hi Stu
I think UTC time deals with 1/2 Hrs also. and because of this minute
should be correct.
For eg, India is +5.30 Hrs GMT
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
------------
*** Sent via Developersdex http://www.developersdex.com ***|||Thanks Stu
I worked this out myself and the proc I developed is about the same as
yours. I had to use minutes though to handle Central Australian Time.
Stu wrote:
> We do this all the time:
> --test method
> DECLARE @.Date smalldatetime
> SET @.Date = GETDATE()
> SELECT @.date, DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()),
> @.Date), GETUTCDATE()
> You could deal with minutes, but since UTC time is a change in hours,
> simply your life :)
> Stu|||Really? I never knew that. I always thought that the timezones were
shifts in hours; didnot realize they shifted in half hors as well.
That's gotta be a pain for mking long distance calls.
:)|||Stu wrote:
> Really? I never knew that. I always thought that the timezones were
> shifts in hours; didnot realize they shifted in half hors as well.
> That's gotta be a pain for mking long distance calls.
> :)
Yes, its true.
And to further complicate things, Nepal is GMT+5:45. They just had to
be different from India.
datetime HOUR function format
I am using reporting services to make a matrix. The row value is the date portion of DateIn. The value is a count of transactions. The column type is the problem. It is the hour part of the timein value.
I got it from the database like this:
{fn HOUR(dbo.[Transaction].[TimeIn])} AS Hour
This works, but gives 24 hour time (and only the hour part, so it looks like 10, 11, 12, 13, 14, etc.)
I want it to look like 10:00 AM, 11:00 AM, 12:00 PM, 1:00, PM, etc.
I have read several books, checked online books, tried format functions... and I'm going nuts. This should be so simple- how do I format this so a human can read it? Thanks
If you need the database to do the conversion, then you can set the format code of the textbox to "t" and use the following expression.=CDate(Fields!Hour.Value & ":00")
If you can use the raw date value from the database, then you can just set the format code of the textbox to "t". If you are grouping on only the hour, then you can still just get the raw date value from the database and use =Fields!TimeIn.Value.Hour as the group expression.
Sunday, March 11, 2012
DateTime Function
Dear all,
I am writing SQL statement to manipulate datetime value.
Say to add 1 day, 1 minute & 1 hour to datetime '2004-03-10 14:00:00.000'.
I use the following SQL statement;
fac_date_cnt -> integer value 1
book_date -> datetime value '2004-03-10 14:00:00.000'.
UPDATE SCH_SET
SET fac_send_date = DateAdd(day,fac_date_cnt, f.book_date) + DateAdd(hour,fac_hour, f.book_date) + DateAdd(minute,fac_minute, f.book_date)
FROM SCH_SET t, FAC_BOOK f
WHERE upper(set_id) = 'A000001' and upper(pcode) = 'A';
And then the result is very strange:
'2108-06-20 04:00:00.000'
What wrong with this statement?? Should I not use '+' between DateAdd()?? And then what should I do?
Thanks you for suggestion!!!Try:
DATEADD(minute,fac_minute,DATEADD(hour,fac_hour,DATEADD(day, fac_date_cnt, f.book_date)))|||Thanks you for reply!!!
It works well.
I just want to ask why I need to embed all DATEADD() into one??
Thanks!!!|||With your original query, you were actually adding three different dates together, after performing a DATEADD on each date. What you want to do instead is perform three distinct DATEADD operations on the same date.
An even easier way to perform this operation is to simply transalte everything into the least common denominator and then add that. For example:
DATEADD(minute, 1 + (25 * 60), @.originalDate)
Datetime format for dimension Please help me
Dear All,
How can I format a dimesion datetime column . for example while browsing a cube, the value of the dimension column 'DateOrder' shows like that 2002-11-01 00:00:00. I wan to get 01/11/2002 dd/mm/yyyy.
What I have to do. When I changed their cell value property as dd/mm/yyyy it doesnpt working ..Please to crrect my problem
with regards
Polachah
You could create a named calculation in the DSV which formats the Date column however you like and the use this as the name of the attribute.|||Thank for replying my requirement
I did the same way but the format is not changed.. Also I tried to use that cube in a pivot grid table and tried to change the format there. Still the format is shown as yyyy/mm/dd like that..
|||You can either break the date into pieces and join it back together however you want
Code Snippet
datename(dd,DateOrder) + '/' + convert(varchar,month(DateOrder)) + '/' + datename(yyyy,DateOrder)
But you would have to do a bit more work on the above code to get it producing a leading "0" on the day and month.
Or you can use the third parameter of the convert function that is used when converting from a datetime to a string (which I prefer to use if I can)
Code Snippet
convert(varchar,DateOrder,103)
Format 103 is dd/mm/yyyy - Books Online has a list of all the format numbers in the help for the CONVERT() function|||
Dear sir
Thank you very mcuh ... for your help .. I got from your advice what I need thank u verymuch again
Datetime format
Hi all,
Getdate() fuction always returns value in 'yyyy-mm-dd hh:mi:ss.mmm' format
How do i customize this format?
For example i want the value like 'ddmonyyyy hh:mm'
Help required.
Thanx in anticipation.
'yyyy-mm-dd hh:mi:ss.mmm' is the way it is displayed in Query Analyzer. If
you want to have your datetime displayed differently, you have to use
CONVERT. CONVERT supports a number of formats, although it doesn't support
the one you want directly, but you can use REPLACE to remove spaces and LEFT
to remove any characters at the end you don't want.
Jacco Schalkwijk
SQL Server MVP
"Senthil" <anonymous@.discussions.microsoft.com> wrote in message
news:3CAFB558-6D38-4B09-BCA3-646F911D2C44@.microsoft.com...
> Hi all,
> Getdate() fuction always returns value in 'yyyy-mm-dd hh:mi:ss.mmm'
format
> How do i customize this format?
> For example i want the value like 'ddmonyyyy hh:mm'
> Help required.
> Thanx in anticipation.
|||You can also use function DATEPART() to retrieve parts of
date, and append them to get the format you require.
Shrikant Patil
MCDBA
>--Original Message--
> Hi all,
> Getdate() fuction always returns value in 'yyyy-mm-dd
hh:mi:ss.mmm' format
> How do i customize this format?
> For example i want the value like 'ddmonyyyy hh:mm'
> Help required.
> Thanx in anticipation.
>.
>
Getdate() fuction always returns value in 'yyyy-mm-dd hh:mi:ss.mmm' format
How do i customize this format?
For example i want the value like 'ddmonyyyy hh:mm'
Help required.
Thanx in anticipation.
'yyyy-mm-dd hh:mi:ss.mmm' is the way it is displayed in Query Analyzer. If
you want to have your datetime displayed differently, you have to use
CONVERT. CONVERT supports a number of formats, although it doesn't support
the one you want directly, but you can use REPLACE to remove spaces and LEFT
to remove any characters at the end you don't want.
Jacco Schalkwijk
SQL Server MVP
"Senthil" <anonymous@.discussions.microsoft.com> wrote in message
news:3CAFB558-6D38-4B09-BCA3-646F911D2C44@.microsoft.com...
> Hi all,
> Getdate() fuction always returns value in 'yyyy-mm-dd hh:mi:ss.mmm'
format
> How do i customize this format?
> For example i want the value like 'ddmonyyyy hh:mm'
> Help required.
> Thanx in anticipation.
|||You can also use function DATEPART() to retrieve parts of
date, and append them to get the format you require.
Shrikant Patil
MCDBA
>--Original Message--
> Hi all,
> Getdate() fuction always returns value in 'yyyy-mm-dd
hh:mi:ss.mmm' format
> How do i customize this format?
> For example i want the value like 'ddmonyyyy hh:mm'
> Help required.
> Thanx in anticipation.
>.
>
Datetime format
Hi all,
Getdate() fuction always returns value in 'yyyy-mm-dd hh:mi:ss.mmm' format
How do i customize this format?
For example i want the value like 'ddmonyyyy hh:mm'
Help required.
Thanx in anticipation.'yyyy-mm-dd hh:mi:ss.mmm' is the way it is displayed in Query Analyzer. If
you want to have your datetime displayed differently, you have to use
CONVERT. CONVERT supports a number of formats, although it doesn't support
the one you want directly, but you can use REPLACE to remove spaces and LEFT
to remove any characters at the end you don't want.
Jacco Schalkwijk
SQL Server MVP
"Senthil" <anonymous@.discussions.microsoft.com> wrote in message
news:3CAFB558-6D38-4B09-BCA3-646F911D2C44@.microsoft.com...
> Hi all,
> Getdate() fuction always returns value in 'yyyy-mm-dd hh:mi:ss.mmm'
format
> How do i customize this format?
> For example i want the value like 'ddmonyyyy hh:mm'
> Help required.
> Thanx in anticipation.|||You can also use function DATEPART() to retrieve parts of
date, and append them to get the format you require.
Shrikant Patil
MCDBA
>--Original Message--
> Hi all,
> Getdate() fuction always returns value in 'yyyy-mm-dd
hh:mi:ss.mmm' format
> How do i customize this format?
> For example i want the value like 'ddmonyyyy hh:mm'
> Help required.
> Thanx in anticipation.
>.
>
Getdate() fuction always returns value in 'yyyy-mm-dd hh:mi:ss.mmm' format
How do i customize this format?
For example i want the value like 'ddmonyyyy hh:mm'
Help required.
Thanx in anticipation.'yyyy-mm-dd hh:mi:ss.mmm' is the way it is displayed in Query Analyzer. If
you want to have your datetime displayed differently, you have to use
CONVERT. CONVERT supports a number of formats, although it doesn't support
the one you want directly, but you can use REPLACE to remove spaces and LEFT
to remove any characters at the end you don't want.
Jacco Schalkwijk
SQL Server MVP
"Senthil" <anonymous@.discussions.microsoft.com> wrote in message
news:3CAFB558-6D38-4B09-BCA3-646F911D2C44@.microsoft.com...
> Hi all,
> Getdate() fuction always returns value in 'yyyy-mm-dd hh:mi:ss.mmm'
format
> How do i customize this format?
> For example i want the value like 'ddmonyyyy hh:mm'
> Help required.
> Thanx in anticipation.|||You can also use function DATEPART() to retrieve parts of
date, and append them to get the format you require.
Shrikant Patil
MCDBA
>--Original Message--
> Hi all,
> Getdate() fuction always returns value in 'yyyy-mm-dd
hh:mi:ss.mmm' format
> How do i customize this format?
> For example i want the value like 'ddmonyyyy hh:mm'
> Help required.
> Thanx in anticipation.
>.
>
Datetime format
Hi all
Getdate() fuction always returns value in 'yyyy-mm-dd hh:mi:ss.mmm' forma
How do i customize this format
For example i want the value like 'ddmonyyyy hh:mm
Help required
Thanx in anticipation.'yyyy-mm-dd hh:mi:ss.mmm' is the way it is displayed in Query Analyzer. If
you want to have your datetime displayed differently, you have to use
CONVERT. CONVERT supports a number of formats, although it doesn't support
the one you want directly, but you can use REPLACE to remove spaces and LEFT
to remove any characters at the end you don't want.
--
Jacco Schalkwijk
SQL Server MVP
"Senthil" <anonymous@.discussions.microsoft.com> wrote in message
news:3CAFB558-6D38-4B09-BCA3-646F911D2C44@.microsoft.com...
> Hi all,
> Getdate() fuction always returns value in 'yyyy-mm-dd hh:mi:ss.mmm'
format
> How do i customize this format?
> For example i want the value like 'ddmonyyyy hh:mm'
> Help required.
> Thanx in anticipation.|||You can also use function DATEPART() to retrieve parts of
date, and append them to get the format you require.
Shrikant Patil
MCDBA
>--Original Message--
> Hi all,
> Getdate() fuction always returns value in 'yyyy-mm-dd
hh:mi:ss.mmm' format
> How do i customize this format?
> For example i want the value like 'ddmonyyyy hh:mm'
> Help required.
> Thanx in anticipation.
>.
>
Getdate() fuction always returns value in 'yyyy-mm-dd hh:mi:ss.mmm' forma
How do i customize this format
For example i want the value like 'ddmonyyyy hh:mm
Help required
Thanx in anticipation.'yyyy-mm-dd hh:mi:ss.mmm' is the way it is displayed in Query Analyzer. If
you want to have your datetime displayed differently, you have to use
CONVERT. CONVERT supports a number of formats, although it doesn't support
the one you want directly, but you can use REPLACE to remove spaces and LEFT
to remove any characters at the end you don't want.
--
Jacco Schalkwijk
SQL Server MVP
"Senthil" <anonymous@.discussions.microsoft.com> wrote in message
news:3CAFB558-6D38-4B09-BCA3-646F911D2C44@.microsoft.com...
> Hi all,
> Getdate() fuction always returns value in 'yyyy-mm-dd hh:mi:ss.mmm'
format
> How do i customize this format?
> For example i want the value like 'ddmonyyyy hh:mm'
> Help required.
> Thanx in anticipation.|||You can also use function DATEPART() to retrieve parts of
date, and append them to get the format you require.
Shrikant Patil
MCDBA
>--Original Message--
> Hi all,
> Getdate() fuction always returns value in 'yyyy-mm-dd
hh:mi:ss.mmm' format
> How do i customize this format?
> For example i want the value like 'ddmonyyyy hh:mm'
> Help required.
> Thanx in anticipation.
>.
>
Datetime Filter
For one of the tables on my report i need to allow only the records where a
certain datetime field has Null value. I have not been able to figure out the
expression in the filter that would do this.
Any ideas?
I cannot do this in the select statement as other data regions require
records with values in the same field.You can do a filter at the table level (the Table report object, that is) or
at the list level by high-lighting the Table or List (for instance) and
selecting the filters from the Properties menu and placing a filter on the
data in the object. Hope this helps.
"Nice_Out" wrote:
> For one of the tables on my report i need to allow only the records where a
> certain datetime field has Null value. I have not been able to figure out the
> expression in the filter that would do this.
> Any ideas?
> I cannot do this in the select statement as other data regions require
> records with values in the same field.|||Thanks. I have figured out that I can do a filter.
What I am having trouble with is the expression to use in the filter. I get
the feeling that it does not like to handle Nulls.
"Rand" <Rand@.discussions.microsoft.com> wrote in message
news:DD85D2AC-7562-426A-815B-0B7936D95D9F@.microsoft.com...
> You can do a filter at the table level (the Table report object, that is)
or
> at the list level by high-lighting the Table or List (for instance) and
> selecting the filters from the Properties menu and placing a filter on the
> data in the object. Hope this helps.
> "Nice_Out" wrote:
> > For one of the tables on my report i need to allow only the records
where a
> > certain datetime field has Null value. I have not been able to figure
out the
> > expression in the filter that would do this.
> >
> > Any ideas?
> >
> > I cannot do this in the select statement as other data regions require
> > records with values in the same field.|||Sorry about that. If I had read more closely I would have seen that. Have
you tried the following in your filter?
set Expression to your date/time field
set Operator to "="
set Value to "= Nothing"
This is assuming you do not also need to include a date spread for when the
field does have a value. If so, let me know. I have that figured out as
well.
"Nice_Out" wrote:
> Thanks. I have figured out that I can do a filter.
> What I am having trouble with is the expression to use in the filter. I get
> the feeling that it does not like to handle Nulls.
> "Rand" <Rand@.discussions.microsoft.com> wrote in message
> news:DD85D2AC-7562-426A-815B-0B7936D95D9F@.microsoft.com...
> > You can do a filter at the table level (the Table report object, that is)
> or
> > at the list level by high-lighting the Table or List (for instance) and
> > selecting the filters from the Properties menu and placing a filter on the
> > data in the object. Hope this helps.
> >
> > "Nice_Out" wrote:
> >
> > > For one of the tables on my report i need to allow only the records
> where a
> > > certain datetime field has Null value. I have not been able to figure
> out the
> > > expression in the filter that would do this.
> > >
> > > Any ideas?
> > >
> > > I cannot do this in the select statement as other data regions require
> > > records with values in the same field.
>
>|||Hey thanks!
That worked groovy.
I swear I tried everything so close to that. Don't know how I missed it.
"Rand" <Rand@.discussions.microsoft.com> wrote in message
news:DCE9E96F-CD79-4D43-B03A-88204095C6C0@.microsoft.com...
> Sorry about that. If I had read more closely I would have seen that.
Have
> you tried the following in your filter?
> set Expression to your date/time field
> set Operator to "="
> set Value to "= Nothing"
> This is assuming you do not also need to include a date spread for when
the
> field does have a value. If so, let me know. I have that figured out as
> well.
>
> "Nice_Out" wrote:
> > Thanks. I have figured out that I can do a filter.
> > What I am having trouble with is the expression to use in the filter. I
get
> > the feeling that it does not like to handle Nulls.
> >
> > "Rand" <Rand@.discussions.microsoft.com> wrote in message
> > news:DD85D2AC-7562-426A-815B-0B7936D95D9F@.microsoft.com...
> > > You can do a filter at the table level (the Table report object, that
is)
> > or
> > > at the list level by high-lighting the Table or List (for instance)
and
> > > selecting the filters from the Properties menu and placing a filter on
the
> > > data in the object. Hope this helps.
> > >
> > > "Nice_Out" wrote:
> > >
> > > > For one of the tables on my report i need to allow only the records
> > where a
> > > > certain datetime field has Null value. I have not been able to
figure
> > out the
> > > > expression in the filter that would do this.
> > > >
> > > > Any ideas?
> > > >
> > > > I cannot do this in the select statement as other data regions
require
> > > > records with values in the same field.
> >
> >
> >
certain datetime field has Null value. I have not been able to figure out the
expression in the filter that would do this.
Any ideas?
I cannot do this in the select statement as other data regions require
records with values in the same field.You can do a filter at the table level (the Table report object, that is) or
at the list level by high-lighting the Table or List (for instance) and
selecting the filters from the Properties menu and placing a filter on the
data in the object. Hope this helps.
"Nice_Out" wrote:
> For one of the tables on my report i need to allow only the records where a
> certain datetime field has Null value. I have not been able to figure out the
> expression in the filter that would do this.
> Any ideas?
> I cannot do this in the select statement as other data regions require
> records with values in the same field.|||Thanks. I have figured out that I can do a filter.
What I am having trouble with is the expression to use in the filter. I get
the feeling that it does not like to handle Nulls.
"Rand" <Rand@.discussions.microsoft.com> wrote in message
news:DD85D2AC-7562-426A-815B-0B7936D95D9F@.microsoft.com...
> You can do a filter at the table level (the Table report object, that is)
or
> at the list level by high-lighting the Table or List (for instance) and
> selecting the filters from the Properties menu and placing a filter on the
> data in the object. Hope this helps.
> "Nice_Out" wrote:
> > For one of the tables on my report i need to allow only the records
where a
> > certain datetime field has Null value. I have not been able to figure
out the
> > expression in the filter that would do this.
> >
> > Any ideas?
> >
> > I cannot do this in the select statement as other data regions require
> > records with values in the same field.|||Sorry about that. If I had read more closely I would have seen that. Have
you tried the following in your filter?
set Expression to your date/time field
set Operator to "="
set Value to "= Nothing"
This is assuming you do not also need to include a date spread for when the
field does have a value. If so, let me know. I have that figured out as
well.
"Nice_Out" wrote:
> Thanks. I have figured out that I can do a filter.
> What I am having trouble with is the expression to use in the filter. I get
> the feeling that it does not like to handle Nulls.
> "Rand" <Rand@.discussions.microsoft.com> wrote in message
> news:DD85D2AC-7562-426A-815B-0B7936D95D9F@.microsoft.com...
> > You can do a filter at the table level (the Table report object, that is)
> or
> > at the list level by high-lighting the Table or List (for instance) and
> > selecting the filters from the Properties menu and placing a filter on the
> > data in the object. Hope this helps.
> >
> > "Nice_Out" wrote:
> >
> > > For one of the tables on my report i need to allow only the records
> where a
> > > certain datetime field has Null value. I have not been able to figure
> out the
> > > expression in the filter that would do this.
> > >
> > > Any ideas?
> > >
> > > I cannot do this in the select statement as other data regions require
> > > records with values in the same field.
>
>|||Hey thanks!
That worked groovy.
I swear I tried everything so close to that. Don't know how I missed it.
"Rand" <Rand@.discussions.microsoft.com> wrote in message
news:DCE9E96F-CD79-4D43-B03A-88204095C6C0@.microsoft.com...
> Sorry about that. If I had read more closely I would have seen that.
Have
> you tried the following in your filter?
> set Expression to your date/time field
> set Operator to "="
> set Value to "= Nothing"
> This is assuming you do not also need to include a date spread for when
the
> field does have a value. If so, let me know. I have that figured out as
> well.
>
> "Nice_Out" wrote:
> > Thanks. I have figured out that I can do a filter.
> > What I am having trouble with is the expression to use in the filter. I
get
> > the feeling that it does not like to handle Nulls.
> >
> > "Rand" <Rand@.discussions.microsoft.com> wrote in message
> > news:DD85D2AC-7562-426A-815B-0B7936D95D9F@.microsoft.com...
> > > You can do a filter at the table level (the Table report object, that
is)
> > or
> > > at the list level by high-lighting the Table or List (for instance)
and
> > > selecting the filters from the Properties menu and placing a filter on
the
> > > data in the object. Hope this helps.
> > >
> > > "Nice_Out" wrote:
> > >
> > > > For one of the tables on my report i need to allow only the records
> > where a
> > > > certain datetime field has Null value. I have not been able to
figure
> > out the
> > > > expression in the filter that would do this.
> > > >
> > > > Any ideas?
> > > >
> > > > I cannot do this in the select statement as other data regions
require
> > > > records with values in the same field.
> >
> >
> >
Subscribe to:
Posts (Atom)