Converting the nvarchar value into date/time value

When you are importing the data from CSV file or other data source, you might require to convert the nvarchar(14) into date/time value. We assume that the column value is in a predefined format of 'yyyyMMddhhmmss' (14 characters).

Below function will be very handy in converting those values into date/time type:

create function fn_from_sys_date
(
 @dt_str as nvarchar(14)
)
returns datetime
begin

/*
9-may-12,lhw
-parse the date+time string and returns datetime type.

select 
 dbo.fn_from_sys_date('20120509174210'),
 dbo.fn_from_sys_date('20120509'),
 dbo.fn_from_sys_date('')
 

*/
 declare @result datetime,
  @dd nchar(2),
  @mm nchar(2),
  @yyyy nchar(4),
  @hh nchar(2),
  @minute nchar(2),
  @ss nchar(2)

 -- the value format is yyyymmddhhMMss
 if len(@dt_str) >= 8
 begin
  select 
   @yyyy = left(@dt_str, 4),
   @mm = substring(@dt_str, 5, 2),
   @dd = substring(@dt_str, 7, 2)
 end
 else
 begin
  -- min date.
  select 
   @yyyy = '1753',
   @mm = '01',
   @dd = '01'
 end

 if len(@dt_str) = 14
 begin 
  select
   @hh = substring(@dt_str, 9, 2),
   @minute = substring(@dt_str, 11, 2),
   @ss = substring(@dt_str, 13, 2)
 end
 else
 begin
  select 
   @hh = '00',
   @minute = '00',
   @ss = '00'
 end

 select @result = cast(@yyyy 
    + '-' + @mm 
    + '-' + @dd 
    + ' ' + @hh
    + ':' + @minute
    + ':' + @ss
   as datetime)

 return @result
end

Converting the date/time value into nvarchar

Sometimes, you have to store the data/time value into nvarchar format so that it can be understand by other programs that easier. You need this function to convert the date/time value into nvarchar:

create function fn_to_sys_date
(
 @dt as datetime
)
returns nvarchar(50)
begin

/*
9-may-12,lhw
-format the date value to human readable format. 

select dbo.fn_to_sys_date(getdate())

*/
  declare @result nvarchar(50)
  select @result = replace(convert(nvarchar,@dt,102), 
                           '.', 
                           '')
           + replace(left(convert(nvarchar, @dt, 114), 8), 
                            ':', 
                            '')

 return @result
end

Getting the records within the given range

For example, you have a voucher table which stores the vouchers old to the customer. Each voucher record store the voucher_no_start and voucher_no_end in INT data type (i.e., this means a voucher record is a booklet but not one voucher).

In order for you to check within the voucher booklet has been sold or not (if sold, there will be a record in the tb_voucher), must be able to tell that the start/end number key in by the user falls within any existing records or not.

To do this, you will have to pass in the @start/@end value(of course, you must modify the following statement into stored procedure).

declare @start int,
 @end int

select @start = 300,
 @end = 450

select 
  voucher_no_start, voucher_no_end
from 
  tb_voucher
where 
  (voucher_no_start <= @end or voucher_no_end < @start)
  and @start <= voucher_no_end

If the above query returns some rows, it means that the values between @start and @end already exist in the database.