asked on

Auto Update a Date Field In Sql

Hi there,

I have a date field in sql, but when we import the date it is formatted as month and year.
I need to automatically add the last day of the month to the date for example:

if the date is imported as 022013 I need it to automatically change to 02282013
then concatenate the field as a date time field.

I will have to set this up for every month.

Any thoughts???

Thank you so much.

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

psmittyy14

ASKER

It needs to be able to do it for every date possible. no matter what year or month.

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

psmittyy14

ASKER

thank you i got it now

PortletPaul

btw: you can use
convert(datetime,

dateadd(day,-1,dateadd(month,1,convert(datetime,right(@thatField,4) + left(@thatField,2) + '01')))

Open in new window

instead of

convert(date

if needed

PortletPaul

wow!

THANK YOU! just jumped over 1,000,000 :)

whooo hooo

psmittyy14

ASKER

it says day is not a recognized table hints option.

I would have to do this for every month correct?

so:

declare field20 as varchar
set field20 = '022013'

select field20 from ssfields
dateadd(day,-1,dateadd(month, 1, convert (date, right (field20, 4) + left(field20, 2) + '01')))

psmittyy14

ASKER

i really am good with sql.. just this one was a bit over my head

PortletPaul

select
field20
, dateadd(day,-1,dateadd(month, 1, convert (date, right (field20, 4) + left(field20, 2) + '01')))
from ssfields

PortletPaul

don't forget, if it complains about conversion to date, use datetime instead

psmittyy14

ASKER

SELECT DATEADD(mm,1,CAST(LEFT(FIELD20,2) + '/01/' + RIGHT(FIELD20,4) as datetime)) -1
FROM SSFIELDS WHERE Field20 is not null
AND ISDATE(CAST(LEFT(FIELD20,2) + '/01/' + RIGHT(FIELD20,4) as datetime)) = 1

(my boss helped... -_-)

PortletPaul

mmm

not one to disagree with boss

but YYYYMMDD is a far more reliable method ( & so is using dateadd)

try this:
"Bad habits to kick : mis-handling date / range queries"

&
cast(..... as datetime)

is the same as

convert(datetime,....)

in fact, some swear that convert is faster