Last Month Date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Last Month Date

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-12-06 : 04:30:57

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

52326 Posts

Posted - 2013-12-06 : 04:48:38

[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-12-06 : 04:52:15

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-06 : 05:11:12

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

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-12-06 : 05:18:39

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-06 : 08:04:26

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources