Custom Date Time formatting in SQL Server

Question

I am trying to write a stored procedure which selects columns from a table and adds 2 extra columns to the ResultSet  These 2 extra columns are the result of conversions on a field in the table which is a Datetime field   The Datetime format field has the following format  YYYY-MM-DD HH MM SS S   The 2 additional fields which should be in the following format    DDMMM HHMMT  where T is  A  for a m  and  P  for p m    Example  If the data in the field was  2008-10-12 13 19 12 0  then the extracted fields should contain    12OCT 0119P   I have tried using CONVERT string formats  but none of the formats match the output I want to get  I am thinking along the lines of extracting the field data via CONVERT and then using REPLACE  but I surely need some help here  as I am no sure   Could anyone well versed in stored procedures help me out here  Thanks

User · Answer

The Datetime format field has the following format 'YYYY-MM-DD HH:MM:SS.S'

That statement is false. That's just how Enterprise Manager or SQL Server chooses to show the date. Internally it's a 8-byte binary value, which is why some of the functions posted by Andrew will work so well.

Kibbee makes a valid point as well, and in a perfect world I would agree with him. However, sometimes you want to bind query results directly to display control or widgets and there's really not a chance to do any formatting. And sometimes the presentation layer lives on a web server that's even busier than the database. With those in mind, it's not necessarily a bad thing to know how to do this in SQL.

User · Answer

You can use the following command in SQL server to make it   select FORMAT getdate    N yyyy-MM-ddThh mm ss

User · Answer

Use DATENAME and wrap the logic in a Function   not a Stored Proc  declare  myTime as DateTime  set  myTime   GETDATE    select  myTime  select DATENAME day   myTime    SUBSTRING UPPER DATENAME month   myTime    0 4    Returns  14OCT   Try not to use any Character   String based operations if possible when working with dates  They are numerical  a float  and performance will suffer from those data type conversions    Dig these handy conversions I have compiled over the years        Common date functions    --  This contains common date functions for MSSQL server    Getting Parts of a DateTime       --  gets the date only  20x faster than using Convert Cast to varchar     --  this has been especially useful for JOINS     SELECT  CAST FLOOR CAST GETDATE   as FLOAT   AS DateTime        --  gets the time only  date portion is  1900-01-01  and is considered the  0 time  of dates in MSSQL  even with the datatype min value of 01 01 1753       SELECT  GETDATE   -  CAST FLOOR CAST GETDATE   as FLOAT   AS DateTime        Relative Dates   --  These are all functions that will calculate a date relative to the current date and time       Current Day       --  now     SELECT  GETDATE         --  midnight of today     SELECT  DATEADD ms -4  DATEADD dd DATEDIFF dd 0 GETDATE      1 0          --  Current Hour     SELECT DATEADD hh DATEPART hh GETDATE    CAST FLOOR CAST GETDATE   AS FLOAT   as DateTime        --  Current Half-Hour - if its 9 36  this will show 9 30     SELECT DATEADD mi   DATEDIFF mi  CAST FLOOR CAST GETDATE   as FLOAT   as DateTime    GETDATE       30    30  CAST FLOOR CAST GETDATE   as FLOAT   as DateTime           Yearly       --  first datetime of the current year     SELECT  DATEADD yy DATEDIFF yy 0 GETDATE    0        --  last datetime of the current year     SELECT  DATEADD ms -4  DATEADD yy DATEDIFF yy 0 GETDATE      1 0            Monthly       --  first datetime of current month     SELECT  DATEADD mm DATEDIFF mm 0 GETDATE    0        --  last datetime of the current month     SELECT  DATEADD ms -4 DATEADD mm 1 DATEADD mm DATEDIFF mm 0 GETDATE    0          --  first datetime of the previous month     SELECT  DATEADD mm DATEDIFF mm 0 GETDATE    -1 0        --  last datetime of the previous month     SELECT  DATEADD ms  -4 DATEADD mm DATEDIFF mm 0 GETDATE    0           Weekly       --  previous monday at 12AM     SELECT  DATEADD wk DATEDIFF wk 0 GETDATE    -1  0        --  previous friday at 11 59 59 PM     SELECT  DATEADD ms -4 DATEADD dd 5 DATEADD wk DATEDIFF wk 0 GETDATE    -1  0            Quarterly       --  first datetime of current quarter     SELECT  DATEADD qq DATEDIFF qq 0 GETDATE    0        --  last datetime of current quarter     SELECT  DATEADD ms -4 DATEADD qq DATEDIFF qq 0 GETDATE      1 0

User · Answer

in MS SQL Server you can do   SET DATEFORMAT ymd  year  month  day

User · Answer

Not answering your question specifically  but isn t that something that should be handled by the presentation layer of your application   Doing it the way you describe creates extra processing on the database end as well as adding extra network traffic  assuming the database exists on a  different machine than the application   for something that could be easily computed on the application side  with more rich date processing libraries  as well as being more language agnostic  especially in the case of your first example which contains the abbreviated month name   Anyway the answers others give you should point you in the right direction if you still decide to go this route

User · Answer

If it s something more specific like DateKey  yyyymmdd  that you need for dimensional models  I suggest something without any casts converts   DECLARE  DateKeyToday int    SELECT 10000   DATEPART yy GETDATE      100   DATEPART mm GETDATE      DATEPART dd GETDATE      PRINT  DateKeyToday

User · Answer

You re going to need DATEPART here   You can concatenate the results of the DATEPART calls together   To get the month abbreviations  you might be able to use DATENAME  if that doesn t work for you  you can use a CASE statement on the DATEPART   DATEPART also works for the time field   I can think of a couple of ways of getting the AM PM indicator  including comparing new dates built via DATEPART or calculating the total seconds elapsed in the day and comparing that to known AM PM thresholds

User · Answer

You re going to need DATEPART here   You can concatenate the results of the DATEPART calls together   To get the month abbreviations  you might be able to use DATENAME  if that doesn t work for you  you can use a CASE statement on the DATEPART   DATEPART also works for the time field   I can think of a couple of ways of getting the AM PM indicator  including comparing new dates built via DATEPART or calculating the total seconds elapsed in the day and comparing that to known AM PM thresholds

User · Answer

I m adding this answer  for myself  as relevant to custom formatting    For underscore yyyy MM dd   REPLACE SUBSTRING CONVERT VARCHAR   dt  120   1  10   -

User · Answer

Use DATENAME and wrap the logic in a Function   not a Stored Proc  declare  myTime as DateTime  set  myTime   GETDATE    select  myTime  select DATENAME day   myTime    SUBSTRING UPPER DATENAME month   myTime    0 4    Returns  14OCT   Try not to use any Character   String based operations if possible when working with dates  They are numerical  a float  and performance will suffer from those data type conversions    Dig these handy conversions I have compiled over the years        Common date functions    --  This contains common date functions for MSSQL server    Getting Parts of a DateTime       --  gets the date only  20x faster than using Convert Cast to varchar     --  this has been especially useful for JOINS     SELECT  CAST FLOOR CAST GETDATE   as FLOAT   AS DateTime        --  gets the time only  date portion is  1900-01-01  and is considered the  0 time  of dates in MSSQL  even with the datatype min value of 01 01 1753       SELECT  GETDATE   -  CAST FLOOR CAST GETDATE   as FLOAT   AS DateTime        Relative Dates   --  These are all functions that will calculate a date relative to the current date and time       Current Day       --  now     SELECT  GETDATE         --  midnight of today     SELECT  DATEADD ms -4  DATEADD dd DATEDIFF dd 0 GETDATE      1 0          --  Current Hour     SELECT DATEADD hh DATEPART hh GETDATE    CAST FLOOR CAST GETDATE   AS FLOAT   as DateTime        --  Current Half-Hour - if its 9 36  this will show 9 30     SELECT DATEADD mi   DATEDIFF mi  CAST FLOOR CAST GETDATE   as FLOAT   as DateTime    GETDATE       30    30  CAST FLOOR CAST GETDATE   as FLOAT   as DateTime           Yearly       --  first datetime of the current year     SELECT  DATEADD yy DATEDIFF yy 0 GETDATE    0        --  last datetime of the current year     SELECT  DATEADD ms -4  DATEADD yy DATEDIFF yy 0 GETDATE      1 0            Monthly       --  first datetime of current month     SELECT  DATEADD mm DATEDIFF mm 0 GETDATE    0        --  last datetime of the current month     SELECT  DATEADD ms -4 DATEADD mm 1 DATEADD mm DATEDIFF mm 0 GETDATE    0          --  first datetime of the previous month     SELECT  DATEADD mm DATEDIFF mm 0 GETDATE    -1 0        --  last datetime of the previous month     SELECT  DATEADD ms  -4 DATEADD mm DATEDIFF mm 0 GETDATE    0           Weekly       --  previous monday at 12AM     SELECT  DATEADD wk DATEDIFF wk 0 GETDATE    -1  0        --  previous friday at 11 59 59 PM     SELECT  DATEADD ms -4 DATEADD dd 5 DATEADD wk DATEDIFF wk 0 GETDATE    -1  0            Quarterly       --  first datetime of current quarter     SELECT  DATEADD qq DATEDIFF qq 0 GETDATE    0        --  last datetime of current quarter     SELECT  DATEADD ms -4 DATEADD qq DATEDIFF qq 0 GETDATE      1 0

User · Answer

You re going to need DATEPART here   You can concatenate the results of the DATEPART calls together   To get the month abbreviations  you might be able to use DATENAME  if that doesn t work for you  you can use a CASE statement on the DATEPART   DATEPART also works for the time field   I can think of a couple of ways of getting the AM PM indicator  including comparing new dates built via DATEPART or calculating the total seconds elapsed in the day and comparing that to known AM PM thresholds

User · Answer

If dt is your datetime column  then   For 1   SUBSTRING CONVERT varchar  dt  13   1  2        UPPER SUBSTRING CONVERT varchar  dt  13   4  3     For 2   SUBSTRING CONVERT varchar  dt  100   13  2        SUBSTRING CONVERT varchar  dt  100   16  3

User · Answer

If dt is your datetime column  then   For 1   SUBSTRING CONVERT varchar  dt  13   1  2        UPPER SUBSTRING CONVERT varchar  dt  13   4  3     For 2   SUBSTRING CONVERT varchar  dt  100   13  2        SUBSTRING CONVERT varchar  dt  100   16  3

User · Answer

Not answering your question specifically  but isn t that something that should be handled by the presentation layer of your application   Doing it the way you describe creates extra processing on the database end as well as adding extra network traffic  assuming the database exists on a  different machine than the application   for something that could be easily computed on the application side  with more rich date processing libraries  as well as being more language agnostic  especially in the case of your first example which contains the abbreviated month name   Anyway the answers others give you should point you in the right direction if you still decide to go this route

User · Answer

Use DATENAME and wrap the logic in a Function   not a Stored Proc  declare  myTime as DateTime  set  myTime   GETDATE    select  myTime  select DATENAME day   myTime    SUBSTRING UPPER DATENAME month   myTime    0 4    Returns  14OCT   Try not to use any Character   String based operations if possible when working with dates  They are numerical  a float  and performance will suffer from those data type conversions    Dig these handy conversions I have compiled over the years        Common date functions    --  This contains common date functions for MSSQL server    Getting Parts of a DateTime       --  gets the date only  20x faster than using Convert Cast to varchar     --  this has been especially useful for JOINS     SELECT  CAST FLOOR CAST GETDATE   as FLOAT   AS DateTime        --  gets the time only  date portion is  1900-01-01  and is considered the  0 time  of dates in MSSQL  even with the datatype min value of 01 01 1753       SELECT  GETDATE   -  CAST FLOOR CAST GETDATE   as FLOAT   AS DateTime        Relative Dates   --  These are all functions that will calculate a date relative to the current date and time       Current Day       --  now     SELECT  GETDATE         --  midnight of today     SELECT  DATEADD ms -4  DATEADD dd DATEDIFF dd 0 GETDATE      1 0          --  Current Hour     SELECT DATEADD hh DATEPART hh GETDATE    CAST FLOOR CAST GETDATE   AS FLOAT   as DateTime        --  Current Half-Hour - if its 9 36  this will show 9 30     SELECT DATEADD mi   DATEDIFF mi  CAST FLOOR CAST GETDATE   as FLOAT   as DateTime    GETDATE       30    30  CAST FLOOR CAST GETDATE   as FLOAT   as DateTime           Yearly       --  first datetime of the current year     SELECT  DATEADD yy DATEDIFF yy 0 GETDATE    0        --  last datetime of the current year     SELECT  DATEADD ms -4  DATEADD yy DATEDIFF yy 0 GETDATE      1 0            Monthly       --  first datetime of current month     SELECT  DATEADD mm DATEDIFF mm 0 GETDATE    0        --  last datetime of the current month     SELECT  DATEADD ms -4 DATEADD mm 1 DATEADD mm DATEDIFF mm 0 GETDATE    0          --  first datetime of the previous month     SELECT  DATEADD mm DATEDIFF mm 0 GETDATE    -1 0        --  last datetime of the previous month     SELECT  DATEADD ms  -4 DATEADD mm DATEDIFF mm 0 GETDATE    0           Weekly       --  previous monday at 12AM     SELECT  DATEADD wk DATEDIFF wk 0 GETDATE    -1  0        --  previous friday at 11 59 59 PM     SELECT  DATEADD ms -4 DATEADD dd 5 DATEADD wk DATEDIFF wk 0 GETDATE    -1  0            Quarterly       --  first datetime of current quarter     SELECT  DATEADD qq DATEDIFF qq 0 GETDATE    0        --  last datetime of current quarter     SELECT  DATEADD ms -4 DATEADD qq DATEDIFF qq 0 GETDATE      1 0

User · Answer

If it s something more specific like DateKey  yyyymmdd  that you need for dimensional models  I suggest something without any casts converts   DECLARE  DateKeyToday int    SELECT 10000   DATEPART yy GETDATE      100   DATEPART mm GETDATE      DATEPART dd GETDATE      PRINT  DateKeyToday

User · Answer

in MS SQL Server you can do   SET DATEFORMAT ymd  year  month  day

User · Answer

Yes Depart is a solution for that but I think this kind of methods are long trips   SQL SERVER   SELECT CAST DATEPART DD GETDATE    AS VARCHAR       CAST DATEPART MM GETDATE    AS VARCHAR       CAST DATEPART YYYY GETDATE    AS VARCHAR       CAST DATEPART HH GETDATE    AS VARCHAR       CAST DATEPART MI GETDATE    AS VARCHAR    Oracle   Select to char sysdate  DD MM YYYY HH24 MI   from dual   You may write your own function by this way you can get rid of this mess   http   sql dzone com news custom-date-formatting-sql-ser  select myshortfun getdate   myformat  GO

User · Answer

The Datetime format field has the following format 'YYYY-MM-DD HH:MM:SS.S'

That statement is false. That's just how Enterprise Manager or SQL Server chooses to show the date. Internally it's a 8-byte binary value, which is why some of the functions posted by Andrew will work so well.

Kibbee makes a valid point as well, and in a perfect world I would agree with him. However, sometimes you want to bind query results directly to display control or widgets and there's really not a chance to do any formatting. And sometimes the presentation layer lives on a web server that's even busier than the database. With those in mind, it's not necessarily a bad thing to know how to do this in SQL.

User · Answer

Use DATENAME and wrap the logic in a Function   not a Stored Proc  declare  myTime as DateTime  set  myTime   GETDATE    select  myTime  select DATENAME day   myTime    SUBSTRING UPPER DATENAME month   myTime    0 4    Returns  14OCT   Try not to use any Character   String based operations if possible when working with dates  They are numerical  a float  and performance will suffer from those data type conversions    Dig these handy conversions I have compiled over the years        Common date functions    --  This contains common date functions for MSSQL server    Getting Parts of a DateTime       --  gets the date only  20x faster than using Convert Cast to varchar     --  this has been especially useful for JOINS     SELECT  CAST FLOOR CAST GETDATE   as FLOAT   AS DateTime        --  gets the time only  date portion is  1900-01-01  and is considered the  0 time  of dates in MSSQL  even with the datatype min value of 01 01 1753       SELECT  GETDATE   -  CAST FLOOR CAST GETDATE   as FLOAT   AS DateTime        Relative Dates   --  These are all functions that will calculate a date relative to the current date and time       Current Day       --  now     SELECT  GETDATE         --  midnight of today     SELECT  DATEADD ms -4  DATEADD dd DATEDIFF dd 0 GETDATE      1 0          --  Current Hour     SELECT DATEADD hh DATEPART hh GETDATE    CAST FLOOR CAST GETDATE   AS FLOAT   as DateTime        --  Current Half-Hour - if its 9 36  this will show 9 30     SELECT DATEADD mi   DATEDIFF mi  CAST FLOOR CAST GETDATE   as FLOAT   as DateTime    GETDATE       30    30  CAST FLOOR CAST GETDATE   as FLOAT   as DateTime           Yearly       --  first datetime of the current year     SELECT  DATEADD yy DATEDIFF yy 0 GETDATE    0        --  last datetime of the current year     SELECT  DATEADD ms -4  DATEADD yy DATEDIFF yy 0 GETDATE      1 0            Monthly       --  first datetime of current month     SELECT  DATEADD mm DATEDIFF mm 0 GETDATE    0        --  last datetime of the current month     SELECT  DATEADD ms -4 DATEADD mm 1 DATEADD mm DATEDIFF mm 0 GETDATE    0          --  first datetime of the previous month     SELECT  DATEADD mm DATEDIFF mm 0 GETDATE    -1 0        --  last datetime of the previous month     SELECT  DATEADD ms  -4 DATEADD mm DATEDIFF mm 0 GETDATE    0           Weekly       --  previous monday at 12AM     SELECT  DATEADD wk DATEDIFF wk 0 GETDATE    -1  0        --  previous friday at 11 59 59 PM     SELECT  DATEADD ms -4 DATEADD dd 5 DATEADD wk DATEDIFF wk 0 GETDATE    -1  0            Quarterly       --  first datetime of current quarter     SELECT  DATEADD qq DATEDIFF qq 0 GETDATE    0        --  last datetime of current quarter     SELECT  DATEADD ms -4 DATEADD qq DATEDIFF qq 0 GETDATE      1 0

User · Answer

Not answering your question specifically  but isn t that something that should be handled by the presentation layer of your application   Doing it the way you describe creates extra processing on the database end as well as adding extra network traffic  assuming the database exists on a  different machine than the application   for something that could be easily computed on the application side  with more rich date processing libraries  as well as being more language agnostic  especially in the case of your first example which contains the abbreviated month name   Anyway the answers others give you should point you in the right direction if you still decide to go this route

User · Answer

The Datetime format field has the following format 'YYYY-MM-DD HH:MM:SS.S'

That statement is false. That's just how Enterprise Manager or SQL Server chooses to show the date. Internally it's a 8-byte binary value, which is why some of the functions posted by Andrew will work so well.

Kibbee makes a valid point as well, and in a perfect world I would agree with him. However, sometimes you want to bind query results directly to display control or widgets and there's really not a chance to do any formatting. And sometimes the presentation layer lives on a web server that's even busier than the database. With those in mind, it's not necessarily a bad thing to know how to do this in SQL.

User · Answer

Not answering your question specifically  but isn t that something that should be handled by the presentation layer of your application   Doing it the way you describe creates extra processing on the database end as well as adding extra network traffic  assuming the database exists on a  different machine than the application   for something that could be easily computed on the application side  with more rich date processing libraries  as well as being more language agnostic  especially in the case of your first example which contains the abbreviated month name   Anyway the answers others give you should point you in the right direction if you still decide to go this route

User · Answer

I m adding this answer  for myself  as relevant to custom formatting    For underscore yyyy MM dd   REPLACE SUBSTRING CONVERT VARCHAR   dt  120   1  10   -

User · Answer

The Datetime format field has the following format 'YYYY-MM-DD HH:MM:SS.S'

That statement is false. That's just how Enterprise Manager or SQL Server chooses to show the date. Internally it's a 8-byte binary value, which is why some of the functions posted by Andrew will work so well.

Kibbee makes a valid point as well, and in a perfect world I would agree with him. However, sometimes you want to bind query results directly to display control or widgets and there's really not a chance to do any formatting. And sometimes the presentation layer lives on a web server that's even busier than the database. With those in mind, it's not necessarily a bad thing to know how to do this in SQL.

User · Answer

If dt is your datetime column  then   For 1   SUBSTRING CONVERT varchar  dt  13   1  2        UPPER SUBSTRING CONVERT varchar  dt  13   4  3     For 2   SUBSTRING CONVERT varchar  dt  100   13  2        SUBSTRING CONVERT varchar  dt  100   16  3

User · Answer

You re going to need DATEPART here   You can concatenate the results of the DATEPART calls together   To get the month abbreviations  you might be able to use DATENAME  if that doesn t work for you  you can use a CASE statement on the DATEPART   DATEPART also works for the time field   I can think of a couple of ways of getting the AM PM indicator  including comparing new dates built via DATEPART or calculating the total seconds elapsed in the day and comparing that to known AM PM thresholds

User · Answer

If dt is your datetime column  then   For 1   SUBSTRING CONVERT varchar  dt  13   1  2        UPPER SUBSTRING CONVERT varchar  dt  13   4  3     For 2   SUBSTRING CONVERT varchar  dt  100   13  2        SUBSTRING CONVERT varchar  dt  100   16  3

User · Answer

You can use the following command in SQL server to make it   select FORMAT getdate    N yyyy-MM-ddThh mm ss

User · Answer

Yes Depart is a solution for that but I think this kind of methods are long trips   SQL SERVER   SELECT CAST DATEPART DD GETDATE    AS VARCHAR       CAST DATEPART MM GETDATE    AS VARCHAR       CAST DATEPART YYYY GETDATE    AS VARCHAR       CAST DATEPART HH GETDATE    AS VARCHAR       CAST DATEPART MI GETDATE    AS VARCHAR    Oracle   Select to char sysdate  DD MM YYYY HH24 MI   from dual   You may write your own function by this way you can get rid of this mess   http   sql dzone com news custom-date-formatting-sql-ser  select myshortfun getdate   myformat  GO

[sql] Custom Date/Time formatting in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to datetime

Examples related to stored-procedures

Examples related to sql-convert