[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Date format help SQL 2005

jpBless

7/20/2007 2:13:00 PM

I have a DateTime Field that stores Date as "07/20/2007 10:20:0 AM" in
Access MDB. I am trying to upsize the mdb file to SQL 2005 express. The SQL
below works OK in Access to retrieve the record's year and week. In SQL 2005
express I get the error "format is not a built in function"

ssql = "SELECT Format([DateOpen],'yyyy') AS YearPart,
Format([DateOpen],'ww') AS WeekNum"

My question... How should I change the above SQL to retrive both year and
week on a given longdatetime field in SQL Express 2005. I am using ADO
connection VB6SP5. I have googled but haven't found a clue. Thanks in
advance


10 Answers

Aaron [SQL Server MVP]

7/20/2007 2:25:00 PM

0

Take a look at the DATEPART function in Books Online. Fomrat() is not
understood by SQL Server.

This article might be useful as well:
http://www.aspfa...

--
Aaron Bertrand
SQL Server MVP





"JP Bless" <jp3BlessNoSpam@hotmail.com> wrote in message
news:%23oc7PgtyHHA.5980@TK2MSFTNGP04.phx.gbl...
>I have a DateTime Field that stores Date as "07/20/2007 10:20:0 AM" in
>Access MDB. I am trying to upsize the mdb file to SQL 2005 express. The SQL
>below works OK in Access to retrieve the record's year and week. In SQL
>2005 express I get the error "format is not a built in function"
>
> ssql = "SELECT Format([DateOpen],'yyyy') AS YearPart,
> Format([DateOpen],'ww') AS WeekNum"
>
> My question... How should I change the above SQL to retrive both year and
> week on a given longdatetime field in SQL Express 2005. I am using ADO
> connection VB6SP5. I have googled but haven't found a clue. Thanks in
> advance
>


Aaron [SQL Server MVP]

7/20/2007 2:27:00 PM

0

> Fomrat() is not understood by SQL Server.

Neither is Format(). Sigh. Where's my coffee...

--
Aaron Bertrand
SQL Server MVP

jpBless

7/20/2007 3:24:00 PM

0

Thanks Aron... Before posting this I actually tried
DatePart('yyyy',[DateOpen]) and DatePart('ww',[DateOpen])... but that did
not work. Eventually I tried DatePart(Year,[DateOpen]) and
DatePart(Week,DateOpen) and that worked... Thanks for your help

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:%23KyglmtyHHA.600@TK2MSFTNGP05.phx.gbl...
> Take a look at the DATEPART function in Books Online. Fomrat() is not
> understood by SQL Server.
>
> This article might be useful as well:
> http://www.aspfa...
>
> --
> Aaron Bertrand
> SQL Server MVP
>
>
>
>
>
> "JP Bless" <jp3BlessNoSpam@hotmail.com> wrote in message
> news:%23oc7PgtyHHA.5980@TK2MSFTNGP04.phx.gbl...
>>I have a DateTime Field that stores Date as "07/20/2007 10:20:0 AM" in
>>Access MDB. I am trying to upsize the mdb file to SQL 2005 express. The
>>SQL below works OK in Access to retrieve the record's year and week. In
>>SQL 2005 express I get the error "format is not a built in function"
>>
>> ssql = "SELECT Format([DateOpen],'yyyy') AS YearPart,
>> Format([DateOpen],'ww') AS WeekNum"
>>
>> My question... How should I change the above SQL to retrive both year and
>> week on a given longdatetime field in SQL Express 2005. I am using ADO
>> connection VB6SP5. I have googled but haven't found a clue. Thanks in
>> advance
>>
>
>


Aaron [SQL Server MVP]

7/20/2007 3:26:00 PM

0

> Thanks Aron... Before posting this I actually tried
> DatePart('yyyy',[DateOpen]) and DatePart('ww',[DateOpen])... but that did
> not work.

Right, you need to look up function syntax in the documentation, rather than
blindly guessing how they might work. :-)

--
Aaron Bertrand
SQL Server MVP


Ralph

7/20/2007 3:53:00 PM

0


"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:%23rNpGJuyHHA.1204@TK2MSFTNGP03.phx.gbl...
> > Thanks Aron... Before posting this I actually tried
> > DatePart('yyyy',[DateOpen]) and DatePart('ww',[DateOpen])... but that
did
> > not work.
>
> Right, you need to look up function syntax in the documentation, rather
than
> blindly guessing how they might work. :-)
>

I congratuate you on your polite expansion of "RTFM".

<g>
-ralph

Jeremy

7/20/2007 5:32:00 PM

0

SELECT
DATEPART(year, [DateOpen]) AS YearPart ,
DATEPART(week, [DateOpen]) AS WeekNum
FROM ...

"JP Bless" wrote:

> I have a DateTime Field that stores Date as "07/20/2007 10:20:0 AM" in
> Access MDB. I am trying to upsize the mdb file to SQL 2005 express. The SQL
> below works OK in Access to retrieve the record's year and week. In SQL 2005
> express I get the error "format is not a built in function"
>
> ssql = "SELECT Format([DateOpen],'yyyy') AS YearPart,
> Format([DateOpen],'ww') AS WeekNum"
>
> My question... How should I change the above SQL to retrive both year and
> week on a given longdatetime field in SQL Express 2005. I am using ADO
> connection VB6SP5. I have googled but haven't found a clue. Thanks in
> advance
>
>
>

Douglas J. Steele

7/20/2007 6:12:00 PM

0

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:eQmhAotyHHA.5408@TK2MSFTNGP02.phx.gbl...
>> Fomrat() is not understood by SQL Server.
>
> Neither is Format(). Sigh. Where's my coffee...

Told you you shouldn't have installed that Beta spellchecker! <g>

--
Doug Steele, Microsoft Access MVP
http://I.Am/...
(no private e-mails, please)

jpBless

7/20/2007 8:34:00 PM

0

Thank Jeremy. Got it

"Jeremy" <jeremy@nospam.nospam> wrote in message
news:01099559-45FD-4B51-A3B1-5C2B33BED775@microsoft.com...
> SELECT
> DATEPART(year, [DateOpen]) AS YearPart ,
> DATEPART(week, [DateOpen]) AS WeekNum
> FROM ...
>
> "JP Bless" wrote:
>
>> I have a DateTime Field that stores Date as "07/20/2007 10:20:0 AM" in
>> Access MDB. I am trying to upsize the mdb file to SQL 2005 express. The
>> SQL
>> below works OK in Access to retrieve the record's year and week. In SQL
>> 2005
>> express I get the error "format is not a built in function"
>>
>> ssql = "SELECT Format([DateOpen],'yyyy') AS YearPart,
>> Format([DateOpen],'ww') AS WeekNum"
>>
>> My question... How should I change the above SQL to retrive both year and
>> week on a given longdatetime field in SQL Express 2005. I am using ADO
>> connection VB6SP5. I have googled but haven't found a clue. Thanks in
>> advance
>>
>>
>>


jpBless

7/20/2007 8:36:00 PM

0

Actually the 2005 SQL Express documentation on datepart has
datepart('yyyy',Date) example... didn't work
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:%23rNpGJuyHHA.1204@TK2MSFTNGP03.phx.gbl...
>> Thanks Aron... Before posting this I actually tried
>> DatePart('yyyy',[DateOpen]) and DatePart('ww',[DateOpen])... but that did
>> not work.
>
> Right, you need to look up function syntax in the documentation, rather
> than blindly guessing how they might work. :-)
>
> --
> Aaron Bertrand
> SQL Server MVP
>


Aaron [SQL Server MVP]

7/21/2007 3:36:00 AM

0

> Actually the 2005 SQL Express documentation on datepart has
> datepart('yyyy',Date) example... didn't work

Can you show the URL or the local file path to that document? Is it
possible it's for SQL Server compact edition?

If that is the case, see Tibor's blog entry
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/07/13/want-to-get-rid-of-compact-edition-from-books-o...
and also Andrew Kelly's at
http://sqlblog.com/blogs/andrew_kelly/archive/2007/07/13/customizing-booksonline-help-colle...

--
Aaron Bertrand
SQL Server MVP