Date Format Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date Format Help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-10-21 : 08:42:06

Hey Guys
I hope your well

Aim – Convert the following field ”[INSTALLATION_DATE]” date format from “20090709” Into this “2009-07-09” ,

Also create a new column called “BegMonth” which selects first day of the given month of the converted date column

The table is ;

SELECT
[FDMSAccountNo],
[INSTALLATION_DATE]
FROM [FDMS].[dbo].[stg_LMPAB501]

Results
FDMSAccountNo INSTALLATION_DATE
878000000884 20030521

Required Results
FDMSAccountNo INSTALLATION_DATE BegMonth
878000000884 2003-05-21 2003-05-01

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-21 : 08:52:33

This is actually a presentation requirement. You should not worried about date formats in sql queries. Keep dates in native format (datetime datatype) and do formatting at front end using formatting functions. Only if you've no front end so it at SQL query using CONVERT function.
so far as [INSTALLATION_DATE] is of type datetime the default format in which its displayed will be 2009-07-09 so far as language settings are default (US english)
FOr getting beginning date of month use



SELECT 
[FDMSAccountNo],
[INSTALLATION_DATE],
DATEADD(mm,DATEDIFF(mm,0,[INSTALLATION_DATE]),0) AS BegMonthDate
FROM [FDMS].[dbo].[stg_LMPAB501]

Also see
http://visakhm.blogspot.com/2013/06/t-sql-tips-beginning-of-month.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-10-21 : 09:21:29

HI Visakh16

Only reason i asked to convert is, is because i am linking these tables into other tables and the date in the other tables are dosplayed as "2009-07-09"

Thank you for your help :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-21 : 09:37:15

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources