Date problem

Hi,

I enter a date in my parameter in the browser like '30/01/2001'. When I press on 'view report' then I get an error. 'An error occurred during local report processing. The value provided for the report parameter 'datum' is not valid for its type.

I think he sees the '30' as a month, so it gives an error. BUT in my SQL-statement i write this : CONVERT(DateTime, @.datum, 103). What's the problem?

Thx

We do validation on parameter values. If you specify the type of this parameter is datetime, it needs to be valid datetime value based on the current culture. You can try changing the parameter type to be string if you don't want the validation.|||

Hi

Does any one know how we can validate the input criteria in reporting server 2005? By saying this I mean to say; let say we have to dates “Start date” and “End date” as criteria to one report. How can I check that the Start date must be lower or equal to the End date?

Regards;
Rakesh

sql

date order of Tables Stored Procedures etc in Enterprise Manager

When I view the list of stored procedures, tables, etc. in Enterprise Manager
console, I cannot click the Create Date header and have the list sort in any
meaningful order. This only occurs with registered SQL servers that are
external to my LAN. SQL servers within the LAN work fine. Any ideas on what
might be causing this?
I stopped trying to figure this out long ago (though it is one of the peeves
I mention in http://www.aspfaq.com/2455).
Instead, why don't you create procedures like these, and run them in Query
Analyzer:
CREATE PROCEDURE dbo.ListTables
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'u'
ORDER BY o.crdate DESC
END
GO
CREATE PROCEDURE dbo.ListProcedures
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'p'
ORDER BY o.crdate DESC
END
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:ED0B754B-AF90-4BF9-8725-46ED1CAB3811@.microsoft.com...
> When I view the list of stored procedures, tables, etc. in Enterprise
Manager
> console, I cannot click the Create Date header and have the list sort in
any
> meaningful order. This only occurs with registered SQL servers that are
> external to my LAN. SQL servers within the LAN work fine. Any ideas on
what
> might be causing this?

date order of Tables Stored Procedures etc in Enterprise Manager

When I view the list of stored procedures, tables, etc. in Enterprise Manager
console, I cannot click the Create Date header and have the list sort in any
meaningful order. This only occurs with registered SQL servers that are
external to my LAN. SQL servers within the LAN work fine. Any ideas on what
might be causing this?I stopped trying to figure this out long ago (though it is one of the peeves
I mention in http://www.aspfaq.com/2455).
Instead, why don't you create procedures like these, and run them in Query
Analyzer:
CREATE PROCEDURE dbo.ListTables
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'u'
ORDER BY o.crdate DESC
END
GO
CREATE PROCEDURE dbo.ListProcedures
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'p'
ORDER BY o.crdate DESC
END
GO
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:ED0B754B-AF90-4BF9-8725-46ED1CAB3811@.microsoft.com...
> When I view the list of stored procedures, tables, etc. in Enterprise
Manager
> console, I cannot click the Create Date header and have the list sort in
any
> meaningful order. This only occurs with registered SQL servers that are
> external to my LAN. SQL servers within the LAN work fine. Any ideas on
what
> might be causing this?|||Thanks! A lot of good information on your hyperlink.

date order of Tables Stored Procedures etc in Enterprise Manager

When I view the list of stored procedures, tables, etc. in Enterprise Manage
r
console, I cannot click the Create Date header and have the list sort in any
meaningful order. This only occurs with registered SQL servers that are
external to my LAN. SQL servers within the LAN work fine. Any ideas on wha
t
might be causing this?I stopped trying to figure this out long ago (though it is one of the peeves
I mention in http://www.aspfaq.com/2455).
Instead, why don't you create procedures like these, and run them in Query
Analyzer:
CREATE PROCEDURE dbo.ListTables
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'u'
ORDER BY o.crdate DESC
END
GO
CREATE PROCEDURE dbo.ListProcedures
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'p'
ORDER BY o.crdate DESC
END
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:ED0B754B-AF90-4BF9-8725-46ED1CAB3811@.microsoft.com...
> When I view the list of stored procedures, tables, etc. in Enterprise
Manager
> console, I cannot click the Create Date header and have the list sort in
any
> meaningful order. This only occurs with registered SQL servers that are
> external to my LAN. SQL servers within the LAN work fine. Any ideas on
what
> might be causing this?

Date format problem

I am using a view which brings purchase related data from my OLTP.

I am using the same view for the source of my production system (which has a mm/dd/yy format) and for my test machine (which has a dd-Mmm-yy format).

By formats I mean theformatsdefinedvia theRegional settings.

The problem is that on the test machine, the dates are giving problems and a date such as today's (i.e. 10, March, 2005) is coming in as 03-October, 2005) - notice that this is due to the data format difference i.e. 03/10/05 versus 10-Mar-05 (or 10/03/05).

Any ideas how I can use the same view but for my test machine I do some sort of conversion to correctly change the format.

Many TIAHi all

I've faced the same issue here, our company uses DD/MM/YYYY date format and one of the computers we had uses MM/DD/YYYY.
to come around this problem we had to think like MS-SQL server. our application must use YYYY-MM-DD so we wrote a function in VB to read the current user date format and change the dates to SQL server format.

to use this function just pass the date and the result will be YYYY-MM-DD.
you can use this function before inserting, updateting or deleting data in VB.


please have fun.:D



Public Function fDate(ByVal xDateIn As String) As String
Dim xReg As New Registry
Dim xRegDateFormat As String
Dim xRegDateSeperator As String
Dim xDay, xMonth, xYear As String
Dim xTmp As Integer
Dim xTmpStr As String


If xDateIn = "" Then Exit Function

xReg.GetKeyValue HKEY_CURRENT_USER, "Control Panel\INTERNATIONAL", "SSHORTDATE", xRegDateFormat
xReg.GetKeyValue HKEY_CURRENT_USER, "Control Panel\INTERNATIONAL", "SDATE", xRegDateSeperator

xDateIn = Trim(Str(CDate(xDateIn)))

xRegDateFormat = UCase(xRegDateFormat)

If InStr(1, xRegDateFormat, "DD") <> 0 Then
Else
xTmp = InStr(1, xRegDateFormat, "D")
xRegDateFormat = Left(xRegDateFormat, xTmp) + "D" + Right(xRegDateFormat, Len(xRegDateFormat) - xTmp)
End If

If InStr(1, xRegDateFormat, "MM") <> 0 Then
Else
xTmp = InStr(1, xRegDateFormat, "M")
xRegDateFormat = Left(xRegDateFormat, xTmp) + "M" + Right(xRegDateFormat, Len(xRegDateFormat) - xTmp)
End If

If InStr(1, xRegDateFormat, "YYYY") <> 0 Then
Else
xTmp = InStr(1, xRegDateFormat, "YY")
xRegDateFormat = Left(xRegDateFormat, xTmp) + "YY" + Right(xRegDateFormat, Len(xRegDateFormat) - xTmp)
End If

'step 01
Select Case Left(xRegDateFormat, 1)
Case "D"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xDay = Left(xDateIn, xTmp - 1)
If xDay < 10 And Len(xDay) = 1 Then
xDay = "0" & xDay
xDateIn = "0" & xDateIn
xTmp = xTmp + 1
End If
Case "M"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xMonth = Left(xDateIn, xTmp - 1)
If xMonth < 10 And Len(xMonth) = 1 Then
xMonth = "0" & xMonth
xDateIn = "0" & xDateIn
xTmp = xTmp + 1
End If
Case "Y"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xYear = Left(xDateIn, xTmp - 1)
If xYear < 100 Then
xYear = "20" & xYear
xDateIn = "20" & xDateIn
xTmp = xTmp + 1
End If
End Select
xDateIn = Mid(xDateIn, xTmp + 1)
xRegDateFormat = Mid(xRegDateFormat, xTmp + 1)

'step 02
Select Case Left(xRegDateFormat, 1)
Case "D"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xDay = Left(xDateIn, xTmp - 1)
If xDay < 10 And Len(xDay) = 1 Then
xDay = "0" & xDay
xDateIn = "0" & xDateIn
xTmp = xTmp + 1
End If
Case "M"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xMonth = Left(xDateIn, xTmp - 1)
If xMonth < 10 And Len(xMonth) = 1 Then
xMonth = "0" & xMonth
xDateIn = "0" & xDateIn
xTmp = xTmp + 1
End If
Case "Y"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xYear = Left(xDateIn, xTmp - 1)
If xYear < 100 Then
xYear = "20" & xYear
xDateIn = "20" & xDateIn
xTmp = xTmp + 1
End If
End Select
xDateIn = Mid(xDateIn, xTmp + 1)
xRegDateFormat = Mid(xRegDateFormat, xTmp + 1)

'step 03
Select Case UCase(Left(xRegDateFormat, 1))
Case "D"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xDay = xDateIn
If xDay < "10" And Len(xDay) = 1 Then xDay = "0" & xDay
Case "M"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xMonth = xDateIn
If xMonth < "10" And Len(xMonth) = 1 Then xMonth = "0" & xMonth
Case "Y"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xYear = xDateIn
If xYear < "100" Then xYear = "20" & xYear
End Select

'fDate = xDay + "/" + xMonth + "/" + xYear
fDate = xYear + "/" + xMonth + "/" + xDay

End Function|||Thanks but there's got to be a simpler solution that this :(|||yes, the simple solution is to use the CONVERT function|||The other simple solution is just to format the date when it is sent through initially.|||yes, the simple solution is to use the CONVERT function

Wish I got paid by the line...

Date Format for a String

Using SQL Server 2000 in a view, if the data is Char and six
characters like: 081564
How can you convert it to a date that looks like: 08/15/1964 and make
it a date value?
Thanks for any suggestions!
RBollingAssuming mdy dateformat and english language settings...
DECLARE @.foo VARCHAR(32);
SET @.foo = '081564';
SET @.foo = CONVERT(SMALLDATETIME, LEFT(@.foo, 2) + '/' + SUBSTRING(@.foo, 3,2)
+ '/' + RIGHT(@.foo, 2));
SELECT @.foo;
Otherwise, you may need more converts in there...
"robboll" <robboll@.hotmail.com> wrote in message
news:1188925487.090517.43360@.d55g2000hsg.googlegroups.com...
> Using SQL Server 2000 in a view, if the data is Char and six
> characters like: 081564
> How can you convert it to a date that looks like: 08/15/1964 and make
> it a date value?
> Thanks for any suggestions!
> RBolling
>|||> How can you convert it to a date that looks like: 08/15/1964 and make
> it a date value?
Above is a contradiction in terms. First, there's no "date" datatype in SQL Server.
Perhaps you mean datetime? If so, datetime doesn't have any format attached to it. It is stored
internally as a number of 0's and 1's. The format that *you* see when you look at it is determined
by the client application. You can use the CONVERT function to convert it to a string with a certain
format and look at that string, but now it isn't datetime anymore.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"robboll" <robboll@.hotmail.com> wrote in message
news:1188925487.090517.43360@.d55g2000hsg.googlegroups.com...
> Using SQL Server 2000 in a view, if the data is Char and six
> characters like: 081564
> How can you convert it to a date that looks like: 08/15/1964 and make
> it a date value?
> Thanks for any suggestions!
> RBolling
>

Date Format

How to format a date that looks like this yyyy-mm-dd in a table to mm-dd-yyy
y
in a view? What is the syntax?
Thanks
--
TSCONVERT(CHAR(10), ColumnName, 110)
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"TS" <TS@.discussions.microsoft.com> wrote in message
news:8B5B0627-00E9-4EFF-A23A-1EE1C03A85A9@.microsoft.com...
> How to format a date that looks like this yyyy-mm-dd in a table to
> mm-dd-yyyy
> in a view? What is the syntax?
> Thanks
> --
> TS|||Hi,
Use CONVERT funtion.
select convert(char(20),getdate(),101)
For a view use:-
Create view V1 as Select convert(char(20),column_name,101) AS Some_name from
Table_name
Thanks
Hari
SQL Server MVP
"TS" <TS@.discussions.microsoft.com> wrote in message
news:8B5B0627-00E9-4EFF-A23A-1EE1C03A85A9@.microsoft.com...
> How to format a date that looks like this yyyy-mm-dd in a table to
> mm-dd-yyyy
> in a view? What is the syntax?
> Thanks
> --
> TS|||Just to add some to the correct answers posted. SQL Server does not store
datetimes in any format that you would see as a datetime. It is two
Integers. Format is the result of the gui that you are displaying it in
given no other formatting such as CONVERT. See these for more details:
http://www.karaszi.com/SQLServer/info_datetime.asp Guide to Datetimes
http://www.sqlservercentral.com/col...sqldatetime.asp
Datetimes
http://www.murach.com/books/sqls/article.htm Datetime Searching
Andrew J. Kelly SQL MVP
"TS" <TS@.discussions.microsoft.com> wrote in message
news:8B5B0627-00E9-4EFF-A23A-1EE1C03A85A9@.microsoft.com...
> How to format a date that looks like this yyyy-mm-dd in a table to
> mm-dd-yyyy
> in a view? What is the syntax?
> Thanks
> --
> TS|||To prevent a 'next time'... ;)
http://msdn.microsoft.com/library/d...br />
2f3o.asp
ML|||Right, THIS will prevent a next time :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"ML" <ML@.discussions.microsoft.com> wrote in message
news:BCCDC6A4-FB90-46DD-9AD4-00D973CC59B6@.microsoft.com...
> To prevent a 'next time'... ;)
> http://msdn.microsoft.com/library/d... />
o_2f3o.asp
>
> ML

Date Format

hello

I'm having problem with date format.I have CRYSTAL 9 and using CRYSTAL ENTERPRISE 9 to view reports.
default system date format is dd/mm/yyyy.when the report is viewed with CR, the date format is system date format and this is what i want.but when viewed by networked users using CE some are veiwing date as 'mm/dd/yyyy'.even if user date setting is 'dd/mm/yyyy' and server setting is 'dd/mm/yyyy'.

Please advice
RegardsHi,

Could you use a fixed format for your date fields? This would be the simplest solution. Personally I'm working an environment with several regional settings and I have defined date layout parameters in my date base. I fetch the data in a subreport into shared variables in the report header and then I use the variables to define the date format. This works fine.

- Jukka

date comparison

Hi there!
I'm having some problems with a query considering it's performance. I
want to make a view of saleslines (table 1) joined with a purchline
(table 2). The purchline should show the last purchamount of an item,
having the latest date before the salesdate.
When running the query I can go get a cup of coffee on the other side
of the world... Any suggestions how to make this query run faster?
At this moment I have made something like this:
SELECT
dbo.SALESLINE.SALESID,
dbo.SALESLINE.ITEMID,
dbo.SALESLINE.QTYORDERED,
dbo.SALESLINE.LINEAMOUNT,
dbo.SALESLINE.CREATEDDATE AS datumVerkoop,
dbo.purchLINE.CREATEDDATE AS datumInkoop,
FROM
dbo.PURCHLINE INNER JOIN
dbo.SALESLINE ON dbo.PURCHLINE.ITEMID = dbo.SALESLINE.ITEMID
WHERE
dbo.PURCHLINE.RECID =
(SELECT
MAX(dbo.purchline.recid)
FROM
dbo.purchline WITH (nolock)
WHERE
(dbo.PURCHLINE.QTYORDERED <> 0) AND
dbo.purchline.createddate <= dbo.salesline.createddate AND
dbo.purchline.itemid = dbo.salesline.itemid)Can you POST DDL and insert scripts for sample data.
"Q" wrote:

> Hi there!
> I'm having some problems with a query considering it's performance. I
> want to make a view of saleslines (table 1) joined with a purchline
> (table 2). The purchline should show the last purchamount of an item,
> having the latest date before the salesdate.
> When running the query I can go get a cup of coffee on the other side
> of the world... Any suggestions how to make this query run faster?
> At this moment I have made something like this:
> SELECT
> dbo.SALESLINE.SALESID,
> dbo.SALESLINE.ITEMID,
> dbo.SALESLINE.QTYORDERED,
> dbo.SALESLINE.LINEAMOUNT,
> dbo.SALESLINE.CREATEDDATE AS datumVerkoop,
> dbo.purchLINE.CREATEDDATE AS datumInkoop,
> FROM
> dbo.PURCHLINE INNER JOIN
> dbo.SALESLINE ON dbo.PURCHLINE.ITEMID = dbo.SALESLINE.ITEMID
> WHERE
> dbo.PURCHLINE.RECID =
> (SELECT
> MAX(dbo.purchline.recid)
> FROM
> dbo.purchline WITH (nolock)
> WHERE
> (dbo.PURCHLINE.QTYORDERED <> 0) AND
> dbo.purchline.createddate <= dbo.salesline.createddate AND
> dbo.purchline.itemid = dbo.salesline.itemid)
>|||I did not create the tables myself. You probably want to know something
about datatypes and indexes I suppose...
salesid => varchar 20
itemid => varchar 30
qtyordered => numeric 13(28,12)
lineamount => numeric 13(28,12)
createddate => datetime 8
The length of the datatypes are mostly larger as needed, but are not
allowed to be changed.
All used columns is the purchline are indexed, and the columns salesid,
itemid are indexed.|||Can you try this... Not sure how much improvement this will give !!.
SELECT
dbo.SALESLINE.SALESID,
dbo.SALESLINE.ITEMID,
dbo.SALESLINE.QTYORDERED,
dbo.SALESLINE.LINEAMOUNT,
dbo.SALESLINE.CREATEDDATE AS datumVerkoop,
dbo.ph.CREATEDDATE AS datumInkoop,
FROM
dbo.SALESLINE
INNER JOIN
(
select
sl.salesid ,MAX(pl.recid) as LatestPurchLineId
from
salesline sl
inner join purchline pl on
sl.itemid = pl.itemid and
sl.createddate > pl.createdon and
pl.qtyordered <> 0
group by
sl.SalesId
) plat ON
dbo.salesline.salesid = plat.salesid
INNER JOIN PurchLine pl ON
pl.recid = plat.LatestPurchLineId
- Sha Anand
"Q" wrote:

> I did not create the tables myself. You probably want to know something
> about datatypes and indexes I suppose...
> salesid => varchar 20
> itemid => varchar 30
> qtyordered => numeric 13(28,12)
> lineamount => numeric 13(28,12)
> createddate => datetime 8
> The length of the datatypes are mostly larger as needed, but are not
> allowed to be changed.
> All used columns is the purchline are indexed, and the columns salesid,
> itemid are indexed.
>

Date Compare

How do you compare date in sql, it is giving me errors,

I want to view all that logged onto my system Today
(*Yes I want to use dyn query for this *)

LastLogin is of type DateTime

SELECT @.myStatement = ' SELECT LastLogin, surname
FROM #TEMP WHERE ((LastLogin ='+CONVERT(datetime, @.LastLogin)+')

exec(@.myStatement)

Thanks in advanceLooks to me like you're trying to add a date type to a string, you can't do that. You need to make it a string.|||

SELECT LastLogin, surname
FROM #TEMP
WHERE LastLogin >= cast(cast(getdate() as char(12)) as datetime)

|||Ehorn where did the parameter go ?|||ehorns showing you how to cast the date conversion into a string. You need to apply that to your code, although its back to front for your example. Basically you need...

cast(@.LastLogin as varchar)

Date & Time Formatting

Hi Everyone,
I am failry new to SQL & am trying to write a view over a table that has a
filed called TIME. the field holds the date & time in the usual format i.e.
dd/mm/yyyy:hh:mm:ss
I want to be able to have the field formatted in the view just to show the
hour portion of the field as we can in excel but how? I know I can set the
field to DATE but how to HOUR?
Regards
TIA> I am failry new to SQL & am trying to write a view over a table that has a
> filed called TIME. the field holds the date & time in the usual format
> i.e.
> dd/mm/yyyy:hh:mm:ss
No it doesn't. There is no "usual format" since the information is stored
as numeric information.
> I want to be able to have the field formatted in the view just to show the
> hour portion of the field as we can in excel but how? I know I can set
> the
> field to DATE but how to HOUR?
Presentation of data is best left to the client application. Best you read
the following link (and the entire site when you have time).
http://www.karaszi.com/sqlserver/info_datetime.asp|||You can use the datepart function in the view. i.e. datepart(hh,getdate())
This example returns the hour for the supplied date.
"Scott Morris" wrote:
> > I am failry new to SQL & am trying to write a view over a table that has a
> > filed called TIME. the field holds the date & time in the usual format
> > i.e.
> > dd/mm/yyyy:hh:mm:ss
> No it doesn't. There is no "usual format" since the information is stored
> as numeric information.
> > I want to be able to have the field formatted in the view just to show the
> > hour portion of the field as we can in excel but how? I know I can set
> > the
> > field to DATE but how to HOUR?
> Presentation of data is best left to the client application. Best you read
> the following link (and the entire site when you have time).
> http://www.karaszi.com/sqlserver/info_datetime.asp
>
>|||Hi Jonathan,
try this:
SELECT DATEPART(hour, fieldname) AS 'Hour'
from tablename
GO
Hope this helps,
Isobel
"Jonathan" wrote:
> Hi Everyone,
> I am failry new to SQL & am trying to write a view over a table that has a
> filed called TIME. the field holds the date & time in the usual format i.e.
> dd/mm/yyyy:hh:mm:ss
> I want to be able to have the field formatted in the view just to show the
> hour portion of the field as we can in excel but how? I know I can set the
> field to DATE but how to HOUR?
> Regards
> TIA
>

Date & Time Formatting

Hi Everyone,
I am failry new to SQL & am trying to write a view over a table that has a
filed called TIME. the field holds the date & time in the usual format i.e.
dd/mm/yyyy:hh:mm:ss
I want to be able to have the field formatted in the view just to show the
hour portion of the field as we can in excel but how? I know I can set the
field to DATE but how to HOUR?
Regards
TIA> I am failry new to SQL & am trying to write a view over a table that has a
> filed called TIME. the field holds the date & time in the usual format
> i.e.
> dd/mm/yyyy:hh:mm:ss
No it doesn't. There is no "usual format" since the information is stored
as numeric information.

> I want to be able to have the field formatted in the view just to show the
> hour portion of the field as we can in excel but how? I know I can set
> the
> field to DATE but how to HOUR?
Presentation of data is best left to the client application. Best you read
the following link (and the entire site when you have time).
http://www.karaszi.com/sqlserver/info_datetime.asp|||You can use the datepart function in the view. i.e. datepart(hh,getdate())
This example returns the hour for the supplied date.
"Scott Morris" wrote:

> No it doesn't. There is no "usual format" since the information is stored
> as numeric information.
>
> Presentation of data is best left to the client application. Best you rea
d
> the following link (and the entire site when you have time).
> http://www.karaszi.com/sqlserver/info_datetime.asp
>
>|||Hi Jonathan,
try this:
SELECT DATEPART(hour, fieldname) AS 'Hour'
from tablename
GO
Hope this helps,
Isobel
"Jonathan" wrote:

> Hi Everyone,
> I am failry new to SQL & am trying to write a view over a table that has a
> filed called TIME. the field holds the date & time in the usual format i.
e.
> dd/mm/yyyy:hh:mm:ss
> I want to be able to have the field formatted in the view just to show the
> hour portion of the field as we can in excel but how? I know I can set th
e
> field to DATE but how to HOUR?
> Regards
> TIA
>