Solved

Auto Update a Date Field In Sql

Posted on 2013-06-27
13
537 Views
Last Modified: 2013-06-27
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
Comment
Question by:psmittyy14
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
13 Comments
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
ID: 39281739
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
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
ID: 39281744
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
 

Author Comment

by:psmittyy14
ID: 39281749
It needs to be able to do it for every date possible. no matter what year or month.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39281763
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
 

Author Closing Comment

by:psmittyy14
ID: 39281774
thank you i got it now
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39281786
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39281789
wow!

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

whooo hooo
0
 

Author Comment

by:psmittyy14
ID: 39281805
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
 

Author Comment

by:psmittyy14
ID: 39281823
i really am good with sql.. just this one was a bit over my head
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39281833
select
  field20
, dateadd(day,-1,dateadd(month, 1, convert (date, right (field20, 4) + left(field20, 2) + '01')))
from ssfields
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39281836
don't forget, if it complains about conversion to date, use datetime instead
0
 

Author Comment

by:psmittyy14
ID: 39281861
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39281891
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question