cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

Is there a SQL Studio Date Time Format function that keeps leading zeroes?

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(Year(N.Node.AssetInventory.ServerInformation.WarrantyDate))

+ '-' + ToString(Month(N.Node.AssetInventory.ServerInformation.WarrantyDate))

+ '-' + ToString(Day(N.Node.AssetInventory.ServerInformation.WarrantyDate))

As warranty_expiration

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.

Todd

0 Kudos
5 Replies
Level 10

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

not in my use

0 Kudos

What would you like the format to output as?  What format did the command above result in?

0 Kudos

When I executed the above command, SWQL studio errors with message Function chart not found. Any other suggestions? Thank yoiu.

0 Kudos

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

0 Kudos