Prev: time out
Next: Upgrade from SQL Server 2005 to 2008 r2
From: A on 10 Jun 2010 16:47
I have a query like

.............
............
col11 = 'THIS' OR
col12 = 'THAT' AND
col13 = '201003' AND
col14 = 'THE OTHER'
..............
..............

col 13 is a char (6) NOT NULL column with dates that have only year and
month in it with also some space. Around 765,000 rows have date and 668 rows
1 empty space. Since empty space is a character, SQL Doesn't complain.

To run the above query, I have to change the value of col13 every month to
get the results for last month. If I run it today, I should make the col13 =
'201005' then run it.

I am trying to automate this process so that I don't have to modify the
query every month.

I don't know what is the best way to do it. I am trying to convert the col13
to date time with (placing 01 for the day part)

SELECT CONVERT(datetime, LEFT(col13, 4)+ RIGHT(col13, 2)+ '01') from TABLE1
where col13 <> ''

then I have problem adding the (Thanks to Plamen Ratchev who gave me this
piece in my previous port)

where col13 > DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) - 1, 0)
and col13 < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0)

to get the last month's data. Below query would give me "The conversion of a
char data type to a datetime data type resulted in an out-of-range datetime
value." error

SELECT CONVERT(datetime, LEFT(col13, 4)+ RIGHT(col13, 2)+ '01') from TABLE1
where col13 <> '' and fiscalno > DATEADD(MONTH, DATEDIFF(MONTH, 0,
CURRENT_TIMESTAMP) - 1, 0) and fiscalno < DATEADD(MONTH, DATEDIFF(MONTH, 0,
CURRENT_TIMESTAMP), 0)


Thanks for any help.

Kevin.
From: Erland Sommarskog on 10 Jun 2010 18:06
A (A(a)discussions.microsoft.com) writes:
> To run the above query, I have to change the value of col13 every month
> to get the results for last month. If I run it today, I should make the
> col13 = '201005' then run it.

convert(char(6), dateadd(-1, MONTH, getdate(), 112)

Format 112 is YYYYMMDD.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: A on 11 Jun 2010 09:19
Server: Msg 174, Level 15, State 1, Line 1
The dateadd function requires 3 arguments.



"Erland Sommarskog" wrote:

> A (A(a)discussions.microsoft.com) writes:
> > To run the above query, I have to change the value of col13 every month
> > to get the results for last month. If I run it today, I should make the
> > col13 = '201005' then run it.
>
> convert(char(6), dateadd(-1, MONTH, getdate(), 112)
>
> Format 112 is YYYYMMDD.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
> .
>
From: Erland Sommarskog on 11 Jun 2010 18:01
A (A(a)discussions.microsoft.com) writes:
> Server: Msg 174, Level 15, State 1, Line 1
> The dateadd function requires 3 arguments.

Try this:

convert(char(6), dateadd(-1, MONTH, getdate()), 112)

But you could also have looked up the convert and dateadd functions
in Books Online and helped yourself a little faster.




--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: A on 14 Jun 2010 08:56

Don't worry about it !!!


"Erland Sommarskog" wrote:

> A (A(a)discussions.microsoft.com) writes:
> > Server: Msg 174, Level 15, State 1, Line 1
> > The dateadd function requires 3 arguments.
>
> Try this:
>
> convert(char(6), dateadd(-1, MONTH, getdate()), 112)
>
> But you could also have looked up the convert and dateadd functions
> in Books Online and helped yourself a little faster.
>
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
> .
>
 | 
Pages: 1
Prev: time out
Next: Upgrade from SQL Server 2005 to 2008 r2