browse by category or date

MS MSQL

Contents

In MS SQL, converting DateTime to String is using FORMAT function. To convert from String to DateTime, we can use either CONVERT or PARSE.

Unfortunately, we can’t really use custom format to parse a string.To make things easy to remember, I simplified it to two ways:

  1. The German way.
    • Culture code: de-DE
    • Culture mode (with millenia): 104
    • Culture mode (without millenia): 4
    • DateTime format: dd-MM-yyyy (date before month)
  2. The US way.
    • Culture code: en-US
    • Culture mode: 101
    • Culture mode (without millenia): 1
    • DateTime format: MM-dd-yyyy (month before date)
select FORMAT(CURRENT_TIMESTAMP,'dd-MMM-yyyy');
--Output: 21-Mar-2015

-- The US Way, with millenia
select convert(datetime, '03-06-2012 10:11', 101);
--Output: 2012-03-06 10:11:00.000

-- The US Way, without millenia
select convert(datetime, '03-06-12 10:11', 1);
--Output: 2012-03-06 10:11:00.000

-- The German way
select PARSE('03.06.2012 19:00' as datetime using 'de-DE');
select PARSE('03/06/2012 19:00' as datetime using 'de-DE');
select PARSE('03-06-2012 19:00' as datetime using 'de-DE');
-- All three has the same output: 2012-06-03 19:00:00.000

Other symbols that can be used in formatting a datetime are shown below (prettified, original source can be find HERE):

Format specifier

Description

Examples

d

The day of the month, from 1 through 31.

2009-06-01T13:45:30 -> 1

2009-06-15T13:45:30 -> 15

dd

The day of the month, from 01 through 31.

2009-06-01T13:45:30 -> 01

2009-06-15T13:45:30 -> 15

ddd

The abbreviated name of the day of the week.

2009-06-15T13:45:30 -> Mon (en-US)

2009-06-15T13:45:30 -> ?? (ru-RU)

2009-06-15T13:45:30 -> lun. (fr-FR)

dddd

The full name of the day of the week.

2009-06-15T13:45:30 -> Monday (en-US)

2009-06-15T13:45:30 -> ??????????? (ru-RU)

2009-06-15T13:45:30 -> lundi (fr-FR)

f

The tenths of a second in a date and time value.

2009-06-15T13:45:30.6170000 -> 6

2009-06-15T13:45:30.05 -> 0

ff

The hundredths of a second in a date and time value.

2009-06-15T13:45:30.6170000 -> 61

2009-06-15T13:45:30.0500000 -> 00

fff

The milliseconds in a date and time value.

6/15/2009 13:45:30.617 -> 617

6/15/2009 13:45:30.0005 -> 000

ffff

The ten thousandths of a second in a date and time value.

2009-06-15T13:45:30.6175000 -> 6175

2009-06-15T13:45:30.0000500 -> 0000

fffff

The hundred thousandths of a second in a date and time value.

2009-06-15T13:45:30.6175400 -> 61754

6/15/2009 13:45:30.000005 -> 00000

ffffff

The millionths of a second in a date and time value.

2009-06-15T13:45:30.6175420 -> 617542

2009-06-15T13:45:30.0000005 -> 000000

fffffff

The ten millionths of a second in a date and time value.

2009-06-15T13:45:30.6175425 -> 6175425

2009-06-15T13:45:30.0001150 -> 0001150

F

If non-zero, the tenths of a second in a date and time value.

2009-06-15T13:45:30.6170000 -> 6

2009-06-15T13:45:30.0500000 -> (no output)

FF

If non-zero, the hundredths of a second in a date and time value.

2009-06-15T13:45:30.6170000 -> 61

2009-06-15T13:45:30.0050000 -> (no output)

FFF

If non-zero, the milliseconds in a date and time value.

2009-06-15T13:45:30.6170000 -> 617

2009-06-15T13:45:30.0005000 -> (no output)

FFFF

If non-zero, the ten thousandths of a second in a date and time value.

2009-06-15T13:45:30.5275000 -> 5275

2009-06-15T13:45:30.0000500 -> (no output)

FFFFF

If non-zero, the hundred thousandths of a second in a date and time value.

2009-06-15T13:45:30.6175400 -> 61754

2009-06-15T13:45:30.0000050 -> (no output)

FFFFFF

If non-zero, the millionths of a second in a date and time value.

2009-06-15T13:45:30.6175420 -> 617542

2009-06-15T13:45:30.0000005 -> (no output)

FFFFFFF

If non-zero, the ten millionths of a second in a date and time value.

2009-06-15T13:45:30.6175425 -> 6175425

2009-06-15T13:45:30.0001150 -> 000115

g
gg

The period or era.

2009-06-15T13:45:30.6170000 -> A.D.

h

The hour, using a 12-hour clock from 1 to 12.

2009-06-15T01:45:30 -> 1

2009-06-15T13:45:30 -> 1

hh

The hour, using a 12-hour clock from 01 to 12.

2009-06-15T01:45:30 -> 01

2009-06-15T13:45:30 -> 01

H

The hour, using a 24-hour clock from 0 to 23.

2009-06-15T01:45:30 -> 1

2009-06-15T13:45:30 -> 13

HH

The hour, using a 24-hour clock from 00 to 23.

2009-06-15T01:45:30 -> 01

2009-06-15T13:45:30 -> 13

K

Time zone information.

With DateTime values:

2009-06-15T13:45:30, Kind Unspecified ->

2009-06-15T13:45:30, Kind Utc -> Z

2009-06-15T13:45:30, Kind Local -> -07:00 (depends on local computer settings)

With DateTimeOffset values:

2009-06-15T01:45:30-07:00 –> -07:00

2009-06-15T08:45:30+00:00 –> +00:00

m

The minute, from 0 through 59.

2009-06-15T01:09:30 -> 9

2009-06-15T13:29:30 -> 29

mm

The minute, from 00 through 59.

2009-06-15T01:09:30 -> 09

2009-06-15T01:45:30 -> 45

M

The month, from 1 through 12.

2009-06-15T13:45:30 -> 6

MM

The month, from 01 through 12.

2009-06-15T13:45:30 -> 06

MMM

The abbreviated name of the month.

2009-06-15T13:45:30 -> Jun (en-US)

2009-06-15T13:45:30 -> juin (fr-FR)

2009-06-15T13:45:30 -> Jun (zu-ZA)

MMMM

The full name of the month.

2009-06-15T13:45:30 -> June (en-US)

2009-06-15T13:45:30 -> juni (da-DK)

2009-06-15T13:45:30 -> uJuni (zu-ZA)

s

The second, from 0 through 59.

2009-06-15T13:45:09 -> 9

ss

The second, from 00 through 59.

2009-06-15T13:45:09 -> 09

t

The first character of the AM/PM designator.

2009-06-15T13:45:30 -> P (en-US)

2009-06-15T13:45:30 -> ? (ja-JP)

2009-06-15T13:45:30 -> (fr-FR)

tt

The AM/PM designator.

2009-06-15T13:45:30 -> PM (en-US)

2009-06-15T13:45:30 -> ?? (ja-JP)

2009-06-15T13:45:30 -> (fr-FR)

y

The year, from 0 to 99.

0001-01-01T00:00:00 -> 1

0900-01-01T00:00:00 -> 0

1900-01-01T00:00:00 -> 0

2009-06-15T13:45:30 -> 9

2019-06-15T13:45:30 -> 19

yy

The year, from 00 to 99.

0001-01-01T00:00:00 -> 01

0900-01-01T00:00:00 -> 00

1900-01-01T00:00:00 -> 00

2019-06-15T13:45:30 -> 19

yyy

The year, with a minimum of three digits.

0001-01-01T00:00:00 -> 001

0900-01-01T00:00:00 -> 900

1900-01-01T00:00:00 -> 1900

2009-06-15T13:45:30 -> 2009

yyyy

The year as a four-digit number.

0001-01-01T00:00:00 -> 0001

0900-01-01T00:00:00 -> 0900

1900-01-01T00:00:00 -> 1900

2009-06-15T13:45:30 -> 2009

yyyyy

The year as a five-digit number.

0001-01-01T00:00:00 -> 00001

2009-06-15T13:45:30 -> 02009

z

Hours offset from UTC, with no leading zeros.

2009-06-15T13:45:30-07:00 -> -7

zz

Hours offset from UTC, with a leading zero for a single-digit value.

2009-06-15T13:45:30-07:00 -> -07

zzz

Hours and minutes offset from UTC.

2009-06-15T13:45:30-07:00 -> -07:00

:

The time separator.

2009-06-15T13:45:30 -> : (en-US)

2009-06-15T13:45:30 -> . (it-IT)

2009-06-15T13:45:30 -> : (ja-JP)

/

The date separator.

2009-06-15T13:45:30 -> / (en-US)

2009-06-15T13:45:30 -> – (ar-DZ)

2009-06-15T13:45:30 -> . (tr-TR)

“string”
‘string’

Literal string delimiter.

2009-06-15T13:45:30 (“arr:” h:m t) -> arr: 1:45 P

2009-06-15T13:45:30 (‘arr:’ h:m t) -> arr: 1:45 P

%

Defines the following character as a custom format specifier.

2009-06-15T13:45:30 (%h) -> 1

\

The escape character.

2009-06-15T13:45:30 (h \h) -> 1 h

Any other character

The character is copied to the result string unchanged.

2009-06-15T01:45:30 (arr hh:mm t) -> arr 01:45 A

GD Star Rating
a WordPress rating system
Working With Custom DateTime Format In MySQL, Oracle, MS SQL and .NET, 5.0 out of 5 based on 1 rating

About Hardono

Howdy! I'm Hardono. I am working as a Software Developer. I am working mostly in Windows, dealing with .NET, conversing in C#. But I know a bit of Linux, mainly because I need to keep this blog operational. I've been working in Logistics/Transport industry for more than 11 years.

Incoming Search

.net, c#, mssql, mysql, oracle

No Comment

Add Your Comment