Add 0 if day is lesser than 9 in sql

Question

I have the following value:

Jun **6** 2012 12:00:00:000AM

What I want is:

Jun **06** 2012 12:00:00:000AM

0 should only be prepended if the value of day is less than 9.

How do I do this?

You need to supply a lot more context than that: what's the RDBMS, what's the table definition, what's the query that returns the string, and so on. — Sergey Kalinichenko, Jun 22 '12 at 15:00
You should just use approaite date format, eg. in MySQL use `%d` in [`DATE_FORMAT()`](http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format) — Crozin, Jun 22 '12 at 15:03
Date types aren't formatted in any specific way. A format is used only when you convert them to strings. Where are you doing this conversion? On the server, the client? — Panagiotis Kanavos, Jun 22 '12 at 15:04
Your SQL should return a date value and the user interface of your app should format appropriately. — Bill, Jun 22 '12 at 15:05
A better solution then a `Case` statement would be to format DateTime.ToString to match what you need exactly. — Security Hound, Jun 22 '12 at 15:12

Curtis · Answer 1 · 2012-06-22T15:12:00.313

4

If you're using SQL Server you can do:

DECLARE @dt datetime = '1/1/2012'
SELECT CONVERT(VARCHAR(26), @dt, 107) + ' ' + CONVERT(VARCHAR(12), @dt, 114)

edited Jun 22 '12 at 15:12

answered Jun 22 '12 at 15:02

Curtis

101,612
66
270
352

score 0 · Answer 2 · answered Jun 22 '12 at 15:35

In any database, you can do something like:

(case when substring(val, 5, 1) = ' '
      then substring(val, 1, 4)||'0'+substring(val, 5, 1000)
      else val
 end)

This checks for the 5th character to see if it is a space. If so, then a zero needs to be added. I'm assuming the "**" are for emphasis an not in the string.

The problem is that the various string operators -- substring and concatenation -- tend to vary by database.

Add 0 if day is lesser than 9 in sql

2 Answers2