Solved

Auto Update a Date Field In Sql

Posted on 2013-06-27
13
531 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

770 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