I'm extracting dateTime columns using SQL studio and I only want the date portion in a specific format. For example, I would like this format YYYY-MM-DD and I would like to retain the leading zeros. According to the SQL Studio SDK documentation, I can concatenate the date parts by using functions Date(), Month(), and Date(). See example below.
+ '-' + ToString(Month(N.Node.AssetInventory.ServerInformation.WarrantyDate))
+ '-' + ToString(Day(N.Node.AssetInventory.ServerInformation.WarrantyDate))
This will produce a value like this -> 2015-7-1
Notice the day and month will NOT include the leading zeroes though and that creates a problem for me in what I want to do with the data. I would think there would be a date function like DateFormat(Date, '%YYYY-%MM-%DD) or something like that. Does anyone know if a function exists that will do this for me using SQL for SolarWinds? Thank you.
Check out this post to see examples of datetime usage and manipulation. Especially how the CONVERT function is used with a style value of 112 which changes the datetime directly to a string result.
Also check out W3 schools article on how the different style values can be used.
This may be what you are looking for.
SELECT CONVERT(char(10), N.Node.AssetInventory.ServerInformation.WarrantyDate,
126) As warranty_expiration
What version of sql server do you have? What is the full query that you are running so i can check it for errors.
Here is a working demo that shows the result of the different datetime types in action. SQL Fiddle
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.