Prev: How write this sql query
Next: sunday is my week ending day
From: DavidC on 31 May 2010 19:40
I need to have an INSERT statement put the begain and end dates of each
quarter of a year into date columns, e.g. 1-1-2010, 3-31-2010, etc. I have
the following but is giving an error. Can someone help? Thanks.

INSERT INTO BranchDates .....
SELECT CAST('1/1/' + YEAR(GETDATE()) AS Date)
,CAST('3/31/' + YEAR(GETDATE()) AS Date)
,CAST('4/1/' + YEAR(GETDATE()) AS Date)
,CAST('6/30/' + YEAR(GETDATE()) AS Date)
,CAST('7/1/' + YEAR(GETDATE()) AS Date)
,CAST('9/30/' + YEAR(GETDATE()) AS Date)
,CAST('10/1/' + YEAR(GETDATE()) AS Date)
,CAST('12/31/' + YEAR(GETDATE()) AS Date)

It gives error "Explicit conversion from data type int to date is not
allowed."

--
David
From: Bob Barrows on 31 May 2010 20:40
DavidC wrote:
> I need to have an INSERT statement put the begain and end dates of
> each quarter of a year into date columns, e.g. 1-1-2010, 3-31-2010,
> etc. I have the following but is giving an error. Can someone help?
> Thanks.
>
> INSERT INTO BranchDates .....
> SELECT CAST('1/1/' + YEAR(GETDATE()) AS Date)

> It gives error "Explicit conversion from data type int to date is not
> allowed."

The YEAR function returns an int. You need to explicitly cast it as a char
before concatenating it with the string in the left side of the +
expression.

You should be aware that this (mm/dd/yyyy)) is not a "safe" way to express a
date, i.e., there is a danger that dates expressed in the format you are
using can be misinterpreted. The safest way to express a date is yyyymmdd,
so you should at least modify your expressions as follows:

CAST(CAST(YEAR(GETDATE()) as char(4)) + '0101' as Date)

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


From: --CELKO-- on 31 May 2010 23:44
On May 31, 6:40 pm, DavidC <dlch...(a)lifetimeinc.com> wrote:
> I need to have an INSERT statement put the begain and end dates of each
> quarter of a year into date columns, e.g. 1-1-2010, 3-31-2010, etc.  I have
> the following but is giving an error.  Can someone help?  Thanks.
>
> INSERT INTO BranchDates .....
> SELECT CAST('1/1/' + YEAR(GETDATE()) AS Date)
>            ,CAST('3/31/' + YEAR(GETDATE()) AS Date)
>            ,CAST('4/1/' + YEAR(GETDATE()) AS Date)
>            ,CAST('6/30/' + YEAR(GETDATE()) AS Date)
>            ,CAST('7/1/' + YEAR(GETDATE()) AS Date)
>            ,CAST('9/30/' + YEAR(GETDATE()) AS Date)
>            ,CAST('10/1/' + YEAR(GETDATE()) AS Date)
>            ,CAST('12/31/' + YEAR(GETDATE()) AS Date)
>
> It gives error "Explicit conversion from data type int to date is not
> allowed."
>
> --
> David

From: Peso on 1 Jun 2010 03:10
SELECT DATEADD(QUARTER, d.q, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0))
AS FromDate,
DATEADD(QUARTER, d.q + 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), -1))
AS ToDate
FROM (
SELECT 0 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3
) AS d(q)

//Peso




"DavidC" <dlchase(a)lifetimeinc.com> wrote in message
news:C25227BA-C9D8-4237-8B55-F2EDB47EBBDB(a)microsoft.com...
> I need to have an INSERT statement put the begain and end dates of each
> quarter of a year into date columns, e.g. 1-1-2010, 3-31-2010, etc. I
> have
> the following but is giving an error. Can someone help? Thanks.
>
> INSERT INTO BranchDates .....
> SELECT CAST('1/1/' + YEAR(GETDATE()) AS Date)
> ,CAST('3/31/' + YEAR(GETDATE()) AS Date)
> ,CAST('4/1/' + YEAR(GETDATE()) AS Date)
> ,CAST('6/30/' + YEAR(GETDATE()) AS Date)
> ,CAST('7/1/' + YEAR(GETDATE()) AS Date)
> ,CAST('9/30/' + YEAR(GETDATE()) AS Date)
> ,CAST('10/1/' + YEAR(GETDATE()) AS Date)
> ,CAST('12/31/' + YEAR(GETDATE()) AS Date)
>
> It gives error "Explicit conversion from data type int to date is not
> allowed."
>
> --
> David

 | 
Pages: 1
Prev: How write this sql query
Next: sunday is my week ending day