Concatenate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Concatenate

Author

Topic

WebKill
Starting Member

32 Posts

Posted - 2011-09-12 : 09:40:45

I am trying to create a variable to format a date so that I have a leading space, using dateadd -60 to get a previous month, with any day, then dateadd again to get the year. Here is what I have, and the error I am getting:

declare @date nvarchar
set @date = ' ' + datepart(month, DATEADD(DAY, -60, GETDATE())) + '/%/' + datepart(year, DATEADD(DAY, -60, GETDATE()))

From here I do a select * from table where the date field like @date, but it returns this message:

Conversion failed when converting the varchar value '/%/' to data type int.

Kristen
Test

22859 Posts

Posted - 2011-09-12 : 09:47:12

datepart returns INT so you will have to convert that to VARCHAR before you can concatenate it to other strings.

however, this is most probably a terrible idea. What's the underlying problem you are trying to solve - all records with dates in a particular Year and Month?

WebKill
Starting Member

32 Posts

Posted - 2011-09-12 : 10:18:00

This one was for my own testing purposes, but eventually I want to set up a spreadsheet that will grab the last two months of data.

Kristen
Test

22859 Posts

Posted - 2011-09-12 : 10:36:34

Last two months:


SELECT ...
FROM MyTable
WHERE MyDateColumn >= DATEADD(Month, DATEDIFF(Month, 0, GetDate())-2, 0)

will be much more efficient than twiddling & concatenating strings.

By "last two months" I assume you mean "From the 1st of the month, two months ago" rather than "From this day, two months ago".

WebKill
Starting Member

32 Posts

Posted - 2011-09-12 : 10:56:38

Well, the date field has a leading space in every entry, so that's where my problem lies.

Kristen
Test

22859 Posts

Posted - 2011-09-12 : 11:02:37

Don't store it in a String column, use a column with a datatype of DATE or DATETIME - that's what its for, to provide full chronological capabilities.

But ... comparing a string column against a DATETIME object should work just fine. Even with a leading space.


SELECT 1 WHERE ' 12Sep2011' >= DATEADD(Month, DATEDIFF(Month, 0, GetDate())-2, 0) -- Success
SELECT 1 WHERE ' 12Sep2000' >= DATEADD(Month, DATEDIFF(Month, 0, GetDate())-2, 0) -- Failure

(use your locale date format as a test if you like). if the leading space is a problem [which I don't think it will be] then just use

LTrim(MyStringDateColumn)

But get all your Date / DateTime column values into a column with datatype DATE / DATETIME at the earliest opportunity.

WebKill
Starting Member

32 Posts

Posted - 2011-09-12 : 11:15:49

That's much easier, your right the space isn't a problem. Thanks!

Kristen
Test

22859 Posts

Posted - 2011-09-12 : 11:35:09

One thing that will be a problem is if your MyStringDateColumn has any invalid dates in it

Might be wise to put

SET DATEFORMAT MDY

ahead of the SELECT statement to force SQL to parse the string dates assuming MDY ordering (use a different D M Y sequence to suit your data / locale if necessary)

That's another reason for using a DATE / DATETIME datatype column ... the dates are unambiguous, and won't be wrongly parsed, etc. With "string dates", unless they are YYYYMMDD format with no punctuation, the parsing of them is down to all sorts of fragile rules. Server setting might change ... a user might run this having connected to SQL with a different language ... highly risky I'm afraid.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources