Solved

Auto Update a Date Field In Sql

Posted on 2013-06-27
13
535 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 48

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 48

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 48

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 48

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 48

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 48

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 48

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 48

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

710 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