SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Last Month Date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 12/06/2013 :  04:30:57  Show Profile  Reply with Quote
HI all

I need some further help
Aim – With the new created column called “[NEW-LAST-STATUS-CHG]” i would like to find the latest date of this column and go back one month

Query so far
SELECT
FDMSAccountNo,
[ACCOUNT-STATUS],
[LAST-STATUS-CHG],
[NEW-LAST-STATUS-CHG] = SUBSTRING([LAST-STATUS-CHG], 3, 2) + '-' + LEFT([LAST-STATUS-CHG], 2) + '-' + RIGHT([LAST-STATUS-CHG], 2),
[NEW-FIRST-POST-DATE] = SUBSTRING([FIRST-POST-DATE], 3, 2) + '-' + LEFT([FIRST-POST-DATE], 2) + '-' + RIGHT([FIRST-POST-DATE], 2)
FROM [FDMS].[dbo].[stg_LMPAB501]

Looking forward to your help

Regards
D

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/06/2013 :  04:48:38  Show Profile  Reply with Quote

SELECT MAX([NEW-LAST-STATUS-CHG])
FROM
(
SELECT 
FDMSAccountNo,
[ACCOUNT-STATUS],
[LAST-STATUS-CHG],
[NEW-LAST-STATUS-CHG] = SUBSTRING([LAST-STATUS-CHG], 3, 2) + '-' + LEFT([LAST-STATUS-CHG], 2) + '-' + RIGHT([LAST-STATUS-CHG], 2),
[NEW-FIRST-POST-DATE] = SUBSTRING([FIRST-POST-DATE], 3, 2) + '-' + LEFT([FIRST-POST-DATE], 2) + '-' + RIGHT([FIRST-POST-DATE], 2)
FROM [FDMS].[dbo].[stg_LMPAB501]
)t
WHERE [NEW-LAST-STATUS-CHG] >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)
AND [NEW-LAST-STATUS-CHG] < GETDATE() + 1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 12/06/2013 :  04:52:15  Show Profile  Reply with Quote
HI Visakh16

I changed the query to
SELECT MAX([NEW-LAST-STATUS-CHG])
FROM
(
SELECT
FDMSAccountNo,
[ACCOUNT-STATUS],
[LAST-STATUS-CHG],
[NEW-LAST-STATUS-CHG] = SUBSTRING([LAST-STATUS-CHG], 3, 2) + '-' + LEFT([LAST-STATUS-CHG], 2) + '-' + RIGHT([LAST-STATUS-CHG], 2),
[NEW-FIRST-POST-DATE] = SUBSTRING([FIRST-POST-DATE], 3, 2) + '-' + LEFT([FIRST-POST-DATE], 2) + '-' + RIGHT([FIRST-POST-DATE], 2)
FROM [FDMS].[dbo].[stg_LMPAB501]
)t
WHERE [NEW-LAST-STATUS-CHG] >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)
AND [NEW-LAST-STATUS-CHG] < GETDATE() + 1
and [ACCOUNT-STATUS] in ('01','16')


and i get the following error message
Msg 242, Level 16, State 3, Line 1
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/06/2013 :  05:11:12  Show Profile  Reply with Quote
i think issue is with this part
SUBSTRING([LAST-STATUS-CHG], 3, 2) + '-' + LEFT([LAST-STATUS-CHG], 2) + '-' + RIGHT([LAST-STATUS-CHG], 2)

can you post few values from this column ([LAST-STATUS-CHG])?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 12/06/2013 :  05:18:39  Show Profile  Reply with Quote
Column name : [LAST-STATUS-CHG]
Data type :nvarchar(6)

[LAST-STATUS-CHG] examples
042312
080813
080813
080813

Want the date in the following format
23-04-12
08-08-13
08-08-13
08-08-13
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/06/2013 :  08:04:26  Show Profile  Reply with Quote
quote:
Originally posted by masond

Column name : [LAST-STATUS-CHG]
Data type :nvarchar(6)

[LAST-STATUS-CHG] examples
042312
080813
080813
080813

Want the date in the following format
23-04-12
08-08-13
08-08-13
08-08-13



Ok..thats where your issue is.
The date values are interpreted based on your servers language and regional settings unless you send them in unambiguos formats. so depending on whether its a US/UK dseetings for example 23-04-12 will get interpreted as 23rd month 04th day of 2012 or 23rd day 4th month of 2012 . so if its us settings the date is invalid (no 23rd mohth ) and so it breaks
see the article below
http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

you should always try to bringit in unambiguos format
ie in your case make it like

[NEW-LAST-STATUS-CHG] = CASE WHEN RIGHT([LAST-STATUS-CHG], 2) >=50 THEN '19' ELSE '20' END + RIGHT([LAST-STATUS-CHG], 2) + LEFT([LAST-STATUS-CHG], 2) + SUBSTRING([LAST-STATUS-CHG], 3, 2)

and then use it in where condition as shown before.
Beware that if you've any spurious values (non valid dates) or any dates values in different format (other than MMDDYY) then it will again break.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000