Knowledge is power. We love to share it.

News related to Mono products, services and latest developments in our community.

Goran

SQL Server 2008 – Date&Time tips and tricks

03/15/2012

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!

Rated 2.46, 13 vote(s).