DateTime to varchar conversion
During the last 6 years I have been working with SQL, I had a lot of tasks that required some form of converting DateTime values to character values, removing Date or Time portion from DateTime values and so on. In the following section you will see how the output changes depending on the format you need. Let's first define a variable that will be used in all conversion examples:
DECLARE
@DateTime DateTime
SET
@DateTime = GETDATE()
SELECT
@DateTime
Returns 2012-03-09 10:41:04.293
Now, using this DateTime stamp, let's take a look at couple of examples of conversion of DateTime to varchar. Using the following expression and replacing LL with the length of the varchar and XXX with the "format code", we will get different results, from Date in different formats to only Time portion of DateTime.
SELECT
CONVERT
(
varchar
(LL), @DateTime, XXX)
Format
|
Output
|
Varchar Length
|
100
|
Mar 9 2012 10:41AM
|
19
|
101
|
03/09/2012a
|
10
|
102
|
2012.03.09
|
10
|
103
|
09/03/2012
|
10
|
104
|
09.03.2012
|
10
|
105
|
09-03-2012
|
10
|
106
|
09 Mar 2012
|
11
|
107
|
Mar 09, 2012
|
12
|
108
|
10:41:04
|
08
|
109
|
Mar 9 2012 10:41:04:293AM
|
26
|
110
|
03-09-2012
|
10
|
111
|
2012/03/09
|
10
|
112
|
20120309
|
08
|
113
|
09 Mar 2012 10:41:04:293
|
24
|
114
|
10:41:04:293
|
12
|
As you see from the example above, in addition to various Date and DateTime formats, there are two Time formats. So if you only want to keep the time in HH:MM:SS format, you will use the format 108, and if you need milliseconds too, you will be using format 114.
Alternatively, for keeping only the date portion, there is more than one conversion format and, depending which Date format you will need, you will choose between 101, 102, 103, 104, 105, 106, 107, 110, 111 and 114. Alternatively, you can cast DateTime to Date format.
Which date from the two is more recent?
This is a very simple function that accepts two DateTime values and returns the more recent one:
CREATE
FUNCTION
[dbo].[GetMaxDate](
@Date1 DateTime,
@Date2 DateTime
)
RETURNS
DateTime
AS
BEGIN
IF @Date1
IS
NULL
RETURN
@Date2
IF @Date2
IS
NULL
RETURN
@Date1
IF @Date1 > @Date2
RETURN
@Date1
ELSE
RETURN
@Date2
RETURN
null
END
Usage:
DECLARE
@DateTime1 DateTime
SET
@DateTime1 =
CAST
(
'2012-01-01'
AS
DateTime)
DECLARE
@DateTime2 DateTime
SET
@DateTime2 =
CAST
(
'2012-02-02'
AS
DateTime)
SELECT
CAST
(dbo.GetMaxDate(@DateTime1, @DateTime2) AS Date)
Result: 2012-02-02
First day of the week
I have often had a need to have the date of the first day of the week, based on any input date. This function does exactly that. I also added a parameter that lets you choose the first day of the week (Sunday, Monday, Tuesday etc).
CREATE
FUNCTION
[dbo].[GetWeekStartDate]
(
@
Date
DATETIME,
@FirstDayIndex
int
-- First day of the week(Sunday = 1, Monday = 2, etc.)
)
RETURNS
DATETIME
AS
BEGIN
DECLARE
@StartOfWeekDate DATETIME
DECLARE
@SqlServerMinDate DATETIME
SELECT
@SqlServerMinDate =
convert
(DATETIME, -53690 + ((@FirstDayIndex + 5) % 7))
IF @
Date
>= @SqlServerMinDate
BEGIN
SELECT
@StartOfWeekDate = DATEADD(dd, (DATEDIFF(dd, @SqlServerMinDate, @
Date
) / 7) * 7, @SqlServerMinDate)
END
RETURN
@StartOfWeekDate
END
Usage:
DECLARE
@DateTime
Date
SET
@DateTime = GETDATE()
SELECT
@DateTime
AS
InputDate, dbo.GetWeekStartDate(@DateTime, 2)
AS
WeekFirstDate
Result:
InputDate WeekFirstDate
2012-03-09 2012-03-05 00:00:00.000
That's it for now. In the next article, I'll show you a couple additional SQL Server tricks. Stay tuned!