Question

Locked

SQL GETDATE WITH STRING HANDLING ON TEXT FIELD

By robmack1 ·
I have a SQL query that I wish to select a specific field entry based on the previous month.

The purpouse of which is to run the table on a schedule without re-creating it each month.

The field I wish to interrogate contains a text string made up as follows:-

"yymm" = for example "0712" for the month of December 2007.

I can see that using a select statement of the kind ... DatePart("m", <InvoiceDate>) = DatePart("m", DateAdd("m", -1, getdate())) AND DatePart("yyyy", <InvoiceDate>) = DatePart("yyyy", DateAdd("m", -1, getdate()))

...Would achieve a prior month result if the Invoicedate field is actually a date formatted field.

How would I achieve the equivalent on my table field (using string conversion)to obtain the same result.

Alas there is no actual date field in the table I am querying.

Any assistance would be much appreciated.

Many thanks,

Rob.

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

'LEFT' &/or 'RIGHT' string functions

by Absolutely In reply to SQL GETDATE WITH STRING H ...

... or intermediate SELECT to a date-formatted field. Whichever blows your skirt up.

Collapse -

new table or select criteia

by gedwards In reply to SQL GETDATE WITH STRING H ...

2 year date field - thought we got rid of those with Y2K.
First and probably easiest - can you add a table with Dates and mmyy columns? Then use this in the query? You might also want to add Fiscal Month / Year if you need them.

or try this in select statement:
Replace 'myDate' your date column name

where
datepart(yy,dateadd(m,-1,getdate()))-2000 = cast(left(myDate,2) as int)
and datepart(m,dateadd(m,-1,getdate())) = cast(right(myDate,2) as int)

Note the conversion so Jan (01) would be 1 for both sides
Greg E

Collapse -

Concatenation

by msi77 In reply to SQL GETDATE WITH STRING H ...

You can use concatenation, for example:

declare @InvoiceDate as varchar(4)
set @InvoiceDate='0712'

select DatePart("m", @InvoiceDate+'01')
select DatePart("m", cast(@InvoiceDate+'01' AS datetime))

Back to Web Development Forum
4 total posts (Page 1 of 1)  

Software Forums