Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 543
  • Last Modified:

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.
0
psmittyy14
Asked:
psmittyy14
  • 8
  • 5
3 Solutions
 
PortletPaulCommented:
so it arrives as a varchar I assume
declare @thatField as varchar(6)
set @thatField = '022013'

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

Open in new window

0
 
PortletPaulCommented:
the logic is this:
get the year + get the month + '01' = '20130201'
convert that to date
add 1 month = 20130301
deduct 1 day = 20130228
0
 
psmittyy14Author Commented:
It needs to be able to do it for every date possible. no matter what year or month.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
PortletPaulCommented:
as long as that string is always MMYYYY
it will always work as described e.g.

122012
get the year + get the month + '01' = '20121201'
convert that to date
add 1 month = 20130101
deduct 1 day = 20121231

try it yourself
0
 
psmittyy14Author Commented:
thank you i got it now
0
 
PortletPaulCommented:
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
0
 
PortletPaulCommented:
wow!

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

whooo hooo
0
 
psmittyy14Author Commented:
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')))
0
 
psmittyy14Author Commented:
i really am good with sql.. just this one was a bit over my head
0
 
PortletPaulCommented:
select
  field20
, dateadd(day,-1,dateadd(month, 1, convert (date, right (field20, 4) + left(field20, 2) + '01')))
from ssfields
0
 
PortletPaulCommented:
don't forget, if it complains about conversion to date, use datetime instead
0
 
psmittyy14Author Commented:
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... -_-)
0
 
PortletPaulCommented:
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
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now