TSQL - Grouping data by year and month

-
A useful way to group data in result sets using only one date field is often to employ a YYYY-MM or YYYY-MM-DD format.

In the past I'd opted for a slightly painful way of taking the dateparts of the year and month, converting them to varchars and then concatinating them together with the hyphens. The following are examples use getdate(), but obviously you'd use your own field.

SELECT CAST(DATEPART(YYYY, GETDATE()) as CHAR(4)) + '-' + REPLICATE('0', 2-LEN(CAST(DATEPART(M, GETDATE()) as VARCHAR(2)))) + CAST(DATEPART(M, GETDATE()) as VARCHAR(2))

Not very pretty I think you'll agree. Especially if you want to immediately use it in a group by. The following is much more elegant and returns the same result!
SELECT CONVERT(CHAR(7),GETDATE(),23)

If you want to include the day part as well then use all 10 characters returned:
SELECT CONVERT(CHAR(10),GETDATE(),23)

For more formats have a look at http://msdn.microsoft.com/en-us/library/ms187928.aspx











































Comments











Post a Comment






























Popular posts from this blog









Enabling SNMP on an ASUS RT-N66U






-















Recently I wanted to monitor an ASUS RT-N66U, a brilliant router with many feature, one of which did NOT appear to be SNMP.   After some rooting around on the Internet (pun intended?), I discovered that it is actually quite easy to set up.   For this you will need a small USB stick, I used an old tiny 4GB thumb drive.   Ensure it's partitioned and formatted to ext2. If you're using Windows then EaseUS Partition Master  can help, there's a free version for home use.  Otherwise the Gnome Partition Editor , otherwise known as GParted, will work.   Just pop the USB drive into the back of the router and go to the "USB application" page in the UI, it's one of the options under "General" on the left hand index.   At the bottom of that page is the "Download Master" which you can then choose to install onto your USB.   What this does is into install a package manager, along with various packages for the Download Master, but this gives you the abilit








Read more










Making Microsoft  Dataverse OData queries using Postman






-















Once you have registered your application and have your ClientId and ClientSecret you can use these to authenticate using oauth2 client credentials workflow. Environment Create an appropriate Postman environment with the following variables: url - https://<yourcompanyname>.crm4.dynamics.com clientid - <your clientid> clientsecret - <your clientsecret> version - 9.1 webapiurl - {{url}}/api/data/v{{version}} tenantid - <guid> authurl - https://login.microsoftonline.com/{{tenantid}}/oauth2/v2.0/token Collection Authorization Setup Authorization for your Postman Collection so all the calls within will inherit it and use the token. Set the Type to "OAuth 2.0" Add auth data to "Request Headers" Configure New Token Token Name - "MyToken" Grant Type - "Client Credentials" Access Token URL -  "{{authurl}}" Client ID - "{{clientid}}" Client Secret - "{{clientsecret}}" Scope - "{{url}}/.default"








Read more










Debugging multiple PHP projects with VS Code and Laragon






-















Laragon is a great solution for developing PHP, and especially Laravel, projects on Windows.  It bundles together Apache, PHP, MySql and automatic host resolution based on folder names.  It's great...  Until...  You try and debug in VS Code using XDebug when multiple sites on your local machine are hit in quick succession resulting in multiple connections from apache being opened against port 9000 on your VS Code instance at the same time.   This manifests itself in VS Code by not hitting any breakpoints and just showing "Request 1" and "Request 2", or similar, in the debug call stack.   In my environment, I have a main project containing middleware that does a security check against a second project before hitting my main action in my controller. Both PHP instances fire off their XDebug connections on port 9000, as xdebug.remote_autostart=1  in the PHP ini file.   Normally this is fine as only one project is practically running at once, but the middleware cause








Read more