Date Format in SQL

I have a question and any help would be greatly appreciated. I'm failry new to MS SQL, though i have been doing Access for sometime and am familiar with its SQL. I'm updating a database to MSSQL and am creating some views off of some old queries but the syntax has changed. Here is the Access SQL:

PARAMETERS Month Short;
SELECT tblrec.HCNumber, tblrec.LocationID, Sum(tblrec.Recvalue) AS RecValue, tblrec.use, tblrec.Unit, Format([Date],"m") AS Month
FROM tblrec
GROUP BY tblrec.HCNumber, tblrec.LocationID, tblrec.use, tblrec.Unit, Format([Date],"m")
HAVING (((Format([Date],"m"))=Month([month])));

The portion that I am having a problem with is the FORMAT statement to change the date column so it only displays the month (1-12) in the result pane. Any help would be greatly appreciated.

THanks!!format([date], "m") = datepart(month, current_timestamp)|||datepart(month, [date])

Be very careful when using keywords as names for objects in sql - I strongly suggest not using keywords.

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
>