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









Windows 7 - Cisco VPN Client






-















I recently started using Windows 7 and so decided to push it to the limit and run all the software I was previously running under Vista.  The only serious problem I had with incompatibility was a known issue with Daemon Tools in that it simply isn't compatible with Windows 7, so I used the free version of Virtual CloneDrive by ElaborateBytes instead. My next stumbling block was to get the Cisco VPN client working. Oddly enough I had no problems getting this to work on build 6956, but on moving to 7000 I kept getting a dreaded blue screen everytime I tried to start Windows. Luckily the system repair managed to get round this and take me back to the system point just before I'd installed the VPN client. After hunting round the web for a while I found a simple combination of things that seemed to result in a reliable way of getting the VPN client to install and work properly. The VPN client version is v5.0.04.0300. First I changed the properties of the Setup application so that it...








Read more










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