I’ve written about how you can manipulate dates and times in .NET before. Here, I’m going to highlight a few avoidable mistakes when manipulating them in .NET and in SQL for database queries.
Case matters
I’m not going to list down all the format strings you can use. Refer to this list instead. You might find this article by Microsoft on best practices useful. I am going to highlight these two letters, h and m.
The small “h” gives you the hour, in 12-hour notation without a leading zero (for less than 10 values). “hh” gives you the 12-hour representation with a leading zero.
The capital “H” gives you the hour in 24-hour notation (or military time) without a leading zero, and “HH” gives you the 24-hour representation with a leading zero.
It’s good user-friendly practice to include the “t” or “tt” notation if you’re using the small “h” to represent the hour (for “A” / “P” or “AM” / “PM” respectively). This way, you know if it’s in the morning or night.
The letter H isn’t so bad. At least you’re still referring to the hour. When you get to M, oh you better watch out.
The small “m” and “mm” gives you the minute (of the time) without and with a leading zero respectively.
The capital “M” and “MM” gives you the month without and with a leading zero respectively. For example, September would be 9 and 09 respectively. See, totally different thing from its small lettered counterpart. There’s the “MMM” and the “MMMM” format string, and I’ll leave it to you to experiment with it.
Now to a common mistake I see: “dd/mm/yyyy“. See any problems?
I’m going to give you a starter custom format string: “dd/MM/yyyy HH:mm:ss“. Burn that into your brain. You can swap “dd” and “MM” if you want. Use “-” instead of “/” as the separator if you want.
The TO_CHAR() function in Oracle PL/SQL
For the equivalent SQL statement to format dates and times in Oracle, here it is:
TO_CHAR(sysdate, 'DD/MM/YYYY HH24:MI:SS')
Note that the function parameter is case insensitive.
Note the difference between “MM” for month and “MI” for minute. For a 12-hour representation, use “HH” or “HH12″.
Tricky formats in Sybase and SQL Server
I don’t know why Sybase and SQL Server don’t just give the ability to easily customise date and time formats. Instead of the string formats in .NET and PL/SQL, they use number codes. Number codes! *urgh*
I actually have a table of the codes and the resulting formats printed out and placed beside my desk, somewhere buried in the chaotic mess of papers. I really just remember 3 number codes: 103, 112 and 120.
select convert(char(10), getdate(), 103) -- gives something like 17/09/2008 in dd/MM/yyyy format select convert(char(8), getdate(), 112) -- gives something like 20080917 in yyyyMMdd format select convert(char(19), getdate(), 120) -- gives something like 2008-09-17 05:04:03 in yyyy-MM-dd HH:mm:ss format
I’m using the .NET format string notation in the comments. Note that number code 120 is only available in SQL Server. The other 2 codes are available in both Sybase and SQL Server. There are other codes (which you can explore here). I just frequently use those 3, particularly 112.
If you’re used to the American date display, then this might be useful:
select convert(char(10), getdate(), 101) -- gives something like 09/17/2008 in MM/dd/yyyy format
Note that you can do something like
select convert(char(6), getdate(), 112) -- gives something like 200809 in yyyyMM format
Note the char(6) part (versus char(8) originally). I do this quite often too.
Now for the mistake in T-SQL. What do you think is wrong with this?
select convert(char(10), EFF_DATE, 103) EFF_DATE from Customers order by EFF_DATE desc
Yes, I’m being deliberately vague. Use your powers of deduction to fill in the blanks.
I write about maths, programming, entrepreneurship and business stuff. I also make videos.
