April 21, 2014 at 1:10 pm
I have a SQL query for SCCM that is pulling from an Active Directory System Discovery (v_R_System table). This conversion was copied from another's blog. I am not SQL savvy, but can get by enough on my own. This one, however, is beyond my scope of skills and I could not find a solution in my research.
I don't know how or if I can change the date format from what I've copied. Below is the snippet of the query:
case
when pwdLastSet0 is null then 'Never'
when pwdLastSet0 = '0' then 'Must change at next logon'
ELSE cast (DATEADD(mi,(cast(pwdLastSet0 as bigint) / 600000000) - 157258080
+ DATEDIFF(Minute,GetUTCDate(),GetDate()),0) as varchar(32)
end as 'Password Last Set Date'
The result is Jan 01 2014 1:30PM
The preferred format is m/dd/yyyy HH:MM or 1/1/2014 13:30
Do I have to do a CONVERT somewhere or is there a better way to do this? I am using a SQL 2008 server and the report is being built in SSRS (Report Builder 3.0).
I appreciate the help!
Jen
April 21, 2014 at 1:22 pm
Yes you probably need to use a convert in your date portion of that case expression.
http://msdn.microsoft.com/en-us/library/ms187928.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 21, 2014 at 2:41 pm
Maybe this:
case
when pwdLastSet0 is null then 'Never'
when pwdLastSet0 = '0' then 'Must change at next logon'
ELSE convert(varchar(10), cast (DATEADD(mi,(cast(pwdLastSet0 as bigint) / 600000000) - 157258080
+ DATEDIFF(Minute,GetUTCDate(),GetDate()),0) as varchar(32)), 101) + ' ' +
convert(varchar(5), cast (DATEADD(mi,(cast(pwdLastSet0 as bigint) / 600000000) - 157258080
+ DATEDIFF(Minute,GetUTCDate(),GetDate()),0) as varchar(32)), 108)
end as 'Password Last Set Date'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 21, 2014 at 2:48 pm
Thanks for the help. With your new code, the result looks like:
Jan 1 201 Jan 1
So it did change, but not for the better sadly. :-/
April 21, 2014 at 2:52 pm
case
when pwdLastSet0 is null then 'Never'
when pwdLastSet0 = '0' then 'Must change at next logon'
ELSE convert(varchar(10), DATEADD(mi,(cast(pwdLastSet0 as bigint) / 600000000) - 157258080
+ DATEDIFF(Minute,GetUTCDate(),GetDate()),0) , 101) + ' ' +
convert(varchar(5), DATEADD(mi,(cast(pwdLastSet0 as bigint) / 600000000) - 157258080
+ DATEDIFF(Minute,GetUTCDate(),GetDate()),0) , 108)
end as 'Password Last Set Date'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 21, 2014 at 2:57 pm
Perfect! Thanks so much for the help!
Jen
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply