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:
Example: If the data in the field was '2008-10-12 13:19:12.0' then the extracted fields should contain:
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!
This question is related to
sql
sql-server
datetime
stored-procedures
sql-convert
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)
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.
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),'-','_')
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.
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
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.
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)))
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.
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.
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
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.
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)))
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),'-','_')
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.
You can use the following command in SQL server to make it:
select FORMAT(getdate(), N'yyyy-MM-ddThh:mm:ss')
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.
You can use the following command in SQL server to make it:
select FORMAT(getdate(), N'yyyy-MM-ddThh:mm:ss')
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.
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.
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)))
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
in MS SQL Server you can do:
SET DATEFORMAT ymd
year, month, day,
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
Source: Stackoverflow.com