Solved

Auto Update a Date Field In Sql

Posted on 2013-06-27
13
532 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
  • 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
LTrim & Double Space Correction 5 40
table joins in qry 17 61
t-sql need help on t-sql 10 25
Get Next number from Stored Procedure 8 21
I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

809 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